One of the reasons for a SQL Server database performance problem can be missing indexes.
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.
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,
- OBJECT_NAME(c.object_id, c.database_id),
- '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 a
- JOIN sys.dm_db_missing_index_groups b
- ON a.group_handle = b.index_group_handle
- JOIN sys.dm_db_missing_index_details c
- ON b.index_handle = c.index_handle
- JOIN sys.objects d ON c.object_id = d.object_id
- WHERE 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."