Tools for Identifying Needed Indexes

Question

I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?

Answer 1

Yes, there are tools, like the database engine tuning advisor. It ships with SQL Server and does some pretty nasty things:

• It allows you to consider only a very small workload (even a single query). Which will then suggest indexes that may help that small workload only, with no regard whatsoever to the rest of the workload (indexes help speed up some queries, but they can slow down others, especially insert/update/delete), never mind the rest of the business cycle.
• It will often recommend redundant indexes that only differ by a trailing column or by an included column.
• It really, really, really likes included columns - I think it may be a fetish.

The proper approach to index tuning is to consider your entire workload over a complete business cycle. Sometimes it is better to turn to 3rd party tools than reinvent the wheel. DBSophic makes a free product called Qure Workload Analyzer that I think does a much better job than DTA - but their licensed tool (Qure Workload Optimizer) is absolutely phenomenal at what it does. Last year I blogged about how the tool works when integrated with our solution (SQL Sentry Performance Advisor for SQL Server) (disclaimer: I work for SQL Sentry).

There are some other options, too, for example missing indexes are reported in the DMVs, and you can also discover which indexes are being written to (so they are taking up space and affecting insert/update/delete operations) but never (or rarely) used by user queries:

• Find missing indexes using the DMVs
• Find unused indexes using Policy-Based Management
• Discovering unused indexes
• Deeper insight into unused indexes

But these also need the caveat that the DMVs are only valid since the last SQL Server restart, and that they won't necessarily cover a proper business cycle (and hence may be missing vital information

Answer 2

The tool you are talking about is called the Database Engine Tuning Advisor (DTA for short). It can take a number of inputs and give recommendations on things like missing indexes. It is worth noting, though, that these are just suggestions and careful consideration should be taken instead of blindly following DTA's recommendations.

Link for DTA Tutorial

It is also worth noting that there are built-in DMVs for gathering that type of information:

• sys.dm_db_missing_index_columns
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups