SQL Server- Improve SQL Server performance with a simple query

This is probably embarrassingly simple to a SQL professional, and it’s been around since SQL 2005, but I only discovered it today and it made a noticeable difference to my production databases so I’m going to share it just in case it’s new to anyone else.

The query

Run this query on SQL Server 2005 or later and it will list indexes that you could create to improve the query performance of your database, based on the workload of the server since the SQL service last restarted. (The query just lists the indexes, it doesn’t create them!)

SELECT    'CREATE INDEX IX_Auto_' +

          CONVERT(varchar(max), MID.index_handle) +

          ' ON ' +

          [statement] + 

          ' (' +

          COALESCE(equality_columns + ', ' + inequality_columns, equality_columns, inequality_columns) +

          ')' +

          ISNULL(' INCLUDE (' + included_columns + ')', '') AS create_statement,

          CONVERT(int, avg_total_user_cost * user_seeks * avg_user_impact) AS potential_saving,

          [statement] AS table_name,

          equality_columns,

          inequality_columns,

          included_columns,

          last_user_seek,

          avg_total_user_cost,

          user_seeks,

          avg_user_impact

FROM      sys.dm_db_missing_index_details MID

JOIN      sys.dm_db_missing_index_groups MIG ON MIG.index_handle = MID.index_handle

JOIN      sys.dm_db_missing_index_group_stats MIGS on MIGS.group_handle = MIG.index_group_handle

ORDER BY  avg_total_user_cost * user_seeks * avg_user_impact DESC

The results

Running the query will return results of the following form, with indexes that have the largest potential improvement first.

Column	Description
create_statement	A SQL statement to create the proposed index.
potential_saving	The relative query cost saving of this index.
table_name	The fully qualified name of the table that the index applies to.
equality_columns	Comma-separated list of columns that contribute to equality predicates of the form table.column = constant_value.
inequality_columns	Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form table.column > constant_value.
included_columns	Comma-separated list of columns needed as covering columns for the query. These are columns that aren’t used as key columns for the index, but are included for quick retrieval by queries.
user_seeks	Number of seeks caused by user queries that the index could have been used for.
last_user_seek	Date and time of last seek caused by user queries that the index could have been used for.
avg_total_user_cost	Average cost of the user queries that could be reduced by the index.
avg_user_impact	Average percentage benefit that user queries could experience if this index was implemented. The value means that the query cost would on average drop by this percentage if this index was implemented.

How it works

Every time the SQL query optimiser analyses a query, it works out the best indexes for the filtering it will need to do. If these indexes don't exist, it nevertheless remembers that they would have been useful, including how much quicker the query would have been any how many times they would have been used. After the database has been up and running for a while, SQL Server will have a pretty good idea of which indexes would make a significant difference to your live workload.
The query above uses the Missing Index dynamic management objects to list all these indexes in order of potential query cost saving.

Caveats

• Obviously if you don't understand SQL indexes at all, it's best not to fiddle with them. While there is a very good chance that the top few suggested indexes will be beneficial, there's always a chance that you could seriously degrade write performance, or that you'll lock the table for a prolonged period while the index is created.
• Don’t create every index in the list! It’s better to create them one at a time and measure the improvements – after a while the savings will become more trivial and it’s more likely that the decrease in write performance will negate the query benefits.
• Not every possible index that might help will be returned. See Limitations of the Missing Indexes Feature.

Further reading

Before you dive in, I strongly recommend having a quick read through Finding Missing Indexes in SQL Server Books Online.