Is there ever any reason not to take the advice of the Database Engine Tuning Advisor?

Question

I'm on a team maintaining a .Net web app with a SQL Server 2005 back end. The system's been running a little slow in places lately, so after doing all the tuning kind of stuff we could think of (adding indexes, cleaning up really badly written stored procedures, etc.) I ran a typical workload through the Tuning Advisor - and it spit out a huge list of additional Indexes and Statistics to create. My initial reaction was to say "sure, you got it, SQL Server," but is there ever any reason NOT to just do what the Advisor says?

Answer 1

Like all advice take it with a grain of salt and use it to reach your own conclusion.

Answer 2

There are 2 problems with indexes.

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

2. Indexes will slow down certain queries (like insert, update, and delete).

Creating proper indexes is a balancing act. If you don't have enough, your system will be slow. If you have too many, your system will be slow. For systems that perform more reads than writes, you can get away with adding more indexes.

Answer 3

I think the advice is helpful, but in my opinion it only gives you things to try. You have to actually do some benchmarking and see what helps and what doesn't. This can be very time consuming but is probably worth it for the reasons G Mastros pointed out.

Database optimization is not a straight forward science but rather a matter of striking the right balance for you exact situation.

Answer 4

Sql Server does a good job of managing statistics if you have enabled auto-create and auto-update of statistics (you should), so ignore the statistics recommendations. Take the indexes and analyze them to make sure you can handle the extra space requirements, and also make sure they aren't duplicating some other index that has similar columns. You can often consolidate indexes by just adding a column or two (paying attention to the order of columns) or by adding an included column (covering index).

If the index is on a table with heavy OLAP use, you want to limit your indexes to maybe 5-10. For tables that rarely get inserts or updates (less than several per second), space limitations should be the only concern.

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

Answer 5

Be careful of it's DROP INDEX recommendations - If your trace capture missed some scheduled or rare queries they could suffer next time it's run.

Answer 6

I recommend this SQL script; it uses SQL 2005's built in perfomance 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 7

Also note that Database tweaking will depend heavily on your usage patterns, which might change a lot between prototyping, development and production. So my best recommendation is to tweak your heart away now, while you have the time, and learn what effects your changes may have. It'll definitely serve you later.