Does running the SQL Server Index Wizard or the Database Engine Tuning Advisor find all of the tables in my database that need indexing?

Question

Does running the SQL Server Index Wizard in SQL Server 7.0 or 2000, or the Database Engine Tuning Advisor in 2005, find all of the tables in my database that need indexing?

Answer

No. While these are handy tools to help identify some of the more obvious missing indexes, they can still miss a lot of useful indexes. Here are some of the reasons why:

• While the Index Wizard is very sophisticated, it still is not smart enough to identify all potential indexes. The 2005 Database Engine Tuning Wizard does a better job than the older tools.
• The number of indexes that are identified is partially based on the settings you choose when running these tools. Some settings do a more thorough, and time-consuming analysis, resulting in fewer or more indexes being identified.
• The trace file you "feed" to these tools for analysis also plays a large part in identifying indexes. Ideally, the trace should be of a representative sample of data. If not, then these tools won't be able to identify all the poorly running queries and be able to suggest appropriate indexes.

I recommend the use of these tools be used to identify the easy to find indexes, and then your next step is to use Profiler to identify any other long running queries, then you must manually review each of these to see if changes in indexes will help their performance. This can be a slow process, but this is just one of the many jobs of the DBA.