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.

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.