Index Tuning Wizard Doesn't See All

Performance tuning is near and dear to me. As a consultant, I enjoy helping customers solve complex (and sometimes simple) performance problems and get the most out of SQL Server. Plus, it's thrilling to make a 2- or 3-hour query run in just a few minutes or seconds through the judicious addition of indexes. One way you can help find the right performance-enhancing indexes to add to your tables is by using SQL Server's Index Tuning Wizard. But beginners often rely solely on the Index Tuning Wizard for their indexing, which can be a dangerous strategy.

The Index Tuning Wizard quickly analyzes a given T-SQL workload, then recommends an index or indexes that the query optimizer believes would be beneficial for efficiently processing that workload. The problem is that the Index Tuning Wizard doesn't reliably find all indexes (including some obvious ones) that could improve the workload's performance. Selecting indexes for a large database is a complex task, and I don't expect the Index Tuning Wizard to be perfect. So instead of relying solely on the wizard, I instead often use it as a quick test when I'm evaluating whether a query could benefit from a new index. In my experience, the Index Tuning Wizard rarely suggests a bad index. However, I never assume that the query is perfectly indexed if the wizard doesn't offer any new suggestions.

I'm not against fixing a problem in 10 seconds, rather than minutes or hours, and you might wonder why I'm issuing this cautionary advice if the Index Tuning Wizard genuinely helps in some situations. The reason is that too many customers, especially beginners, rely on the wizard as the authoritative review of their indexing strategy. I've been told (or read on the newsgroups) many times that "my indexes are fine because the wizard said so."

Normally, I'd stop at urging DBAs to beware of relying on the Index Tuning Wizard. However, I also think that Microsoft should, inside SQL Server, post a "caution sign" as well. The problem is that most (if not all) wizards do a thorough job of performing their specific tasks. For example, I don't need to worry about my backups not working if I've used the Database Maintenance Wizard. In addition, novices tend to heavily use and rely on wizards and might not know that although most other wizards work perfectly, the Index Tuning Wizard is unreliable for finding missing indexes.

The Index Tuning Wizard is a helpful piece of code that you can use to simplify the complex activity of selecting indexes. And I'm sure Yukon's version of the wizard will be even more accurate. But SQL Server needs a clear warning sign that lets you know that you need more than the Index Tuning Wizard to find the most efficient indexing strategy for your system.