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):
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_columns
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 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."
