Skip to content

Improve SQL Server performance with a simple query

July 26, 2010
tags:

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.

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: