Is there any reason not to take the advice of a consultant on tuning the database kernel?

Question

I am on a team supporting a .NET web application with SQL Server 2005. The system has been running a bit slower lately, so after you have done all the settings we might think (adding indexes, clearing really badly written stored procedures etc.), I performed a typical workload through the Tuning Advisor - and it spills out a huge list of additional indexes and statistics to create. My initial reaction was to say, “Of course you got it, SQL Server,” but is there ever some reason NOT to just do what the adviser says?

Answer 1

Sql Server does a great job of managing statistics if you enable automatic creation and automatic updating of statistics (you should), so ignore the recommendations on statistics. Take indexes and analyze them to make sure you can handle the extra space requirements, and also make sure they don't duplicate any other index that has similar columns. You can often consolidate indices by simply adding a column or two (paying attention to the order of the columns) or adding an included column (coverage index).

If the index is in a table with heavy use of OLAP, you want to limit your indexes to maybe 5-10. For tables that rarely receive inserts or updates (less than a few times per second), space limitation should be the only problem.

The recommendations of the setup wizard can be a great learning tool. Take the indexes, go back to the query plan and try to find out why the recommendation was made.

Answer 2

There are two problems with indexes.

• Indexes take up space. Space is cheap, so this is usually not a strong argument against indexes. However, it is worth considering.

• Indexes will slow down some queries (for example, insert, update, and delete).

Creating the right indexes is a balancing act. If this is not enough for you, your system will be slow. If you have too much, your system will be slow. For systems that perform more reads than writes, you can get away with adding additional indexes.

Answer 3

I recommend this SQL script; it uses SQL 2005, built into performance counters, to suggest indexes:

SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC 

Answer 4

He suggested indexes in calculated columns.

Which then prohibits users from inserting rows into the table .

So watch out for such things.

Answer 5

I think the advice is useful, but, in my opinion, it only gives you the opportunity to try. You should really benchmark and see what helps and what doesn't. This can be very time-consuming, but probably worth what M. Mastros pointed out.

Database optimization is not a direct science, but rather a question of the right balance for a specific situation.

Answer 6

Be careful with the recommendations of DROP INDEX. If your trace capture misses some scheduled or rare requests, they may suffer next time.

Answer 7

Also note that setting up the database will largely depend on your usage patterns, which can vary greatly between prototyping, development, and production. Therefore, my best recommendation is now to tune your heart while you have time, and find out what consequences your changes may have. This will definitely serve you later.

Answer 8

Like all tips, take it with salt and use it to reach your own conclusion.