Slow SQL Server Caused by Missing Indexes?

One of the reasons for a SQL Server database performance problem can be missing indexes.

Limited Approach

One way to easily determine if there are missing indexes is to use two dynamic management views (DMVs):

Between these two DMVs, you can easily identify many of the indexes that need to be created to improve performance. However, there's a catch: The system doesn't understand that indexes already in existence might only need another column added to their included columns list.

Don't just take the information from the DMVs and create all the specified indexes. The reported indexes should be compared against the indexes that already exist in the database to ensure that duplicate indexes aren't being created. Duplicate indexes can lead to additional writes, updates, and deletes, causing performance problems.

Best Approach

A better approach is to use a query to identify the indexes that are missing from the database on which the query is being run. When reviewing the output, look for queries that have a high value in the first column. The higher the number, the more savings that will be seen by adding an index.

SELECT a.avg_user_impact  * a.avg_total_user_cost  * a.user_seeks,  db_name(c.database_id),  OBJECT_NAME(c.object_id, c.database_id),  c.equality_columns,  c.inequality_columns,  c.included_columns,  c.statement,  'USE [' + DB_NAME(c.database_id) + '];CREATE INDEX mrdenny_' + replace(replace(replace(replace  (ISNULL(equality_columns, '')  + ISNULL(c.inequality_columns, ''), ', ', '_'),  '[', ''), ']', ''), ' ', '') + '  ON [' + schema_name(d.schema_id) + ']  .[' + OBJECT_NAME(c.object_id, c.database_id) + ']  (' + ISNULL(equality_columns, '') +  CASE WHEN c.equality_columns IS NOT NULL    AND c.inequality_columns IS NOT NULL THEN ', '    ELSE '' END + ISNULL(c.inequality_columns, '') + ')    ' + CASE WHEN included_columns IS NOT NULL THEN    'INCLUDE (' + included_columns + ')' ELSE '' END + '    WITH (FILLFACTOR=70, ONLINE=ON)'FROM sys.dm_db_missing_index_group_stats aJOIN sys.dm_db_missing_index_groups b  ON a.group_handle = b.index_group_handleJOIN sys.dm_db_missing_index_details c  ON b.index_handle = c.index_handleJOIN sys.objects d ON c.object_id = d.object_idWHERE c.database_id = db_id()ORDER BY DB_NAME(c.database_id),  ISNULL(equality_columns, '')  + ISNULL(c.inequality_columns, ''), a.avg_user_impact  * a.avg_total_user_cost * a.user_seeks DESC

This query uses the db_id() system function to restrict the output to that from the current database. So, to run it in your database, simply specify that database in the Available Databases drop-down box in SQL Server Management Studio (SSMS) or add a USE statement before the query.

Learn more about resolving SQL Server performance bottlenecks and missing indexes in "Troubleshooting Slow Servers."