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

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 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.