Recently I had to do some performance tuning for a client and needed to see which indexes in a specific database were missing to validate a problem query that was taking too long to execute. The following script assisted in that journey.
Full disclosure, I did not create the original script, which can be found here (by Bart Duncan), but did modify to suit my particular needs; specifically filtering based on the database_id to narrow my results and also based on the last_user_seek and last_user_scan times, both of which are commented in the T-SQL code below.
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [IX_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 100
-- *** UN-COMMENT ONE OR ALL OF THE FOLLOWING LINES TO USE ***
-- AND mid.database_id = (select database_id from sys.databases where [name] = 'your db name here') -- sometimes helpful to isolate to a specific database
-- AND (migs.last_user_seek >= GETDATE()-30 OR migs.last_user_scan >= GETDATE()-30) -- sometimes helpful to isolate based on last user usage
ORDER BY 1 DESC
The results should help you identify and give you the T-SQL code to create the necessary missing indexes (since last DB engine restart). If this is your first time using these DMVs, or if you want to get more insight into the understanding of the results, visit the Microsoft MSDN page detailing Index Related Dynamic Management Views and Functions (Transact-SQL), for a deeper dive.
Now a word of warning, not all missing indexes should be implemented. Just because the SQL engine recommends it, doesn’t mean that the missing index will improve performance; it could have the opposite effect, so testing of queries before and after index creation/modification is a must.
When it comes to testing, I am a proponent of using the following statistics settings to give valuable information regarding disk activity and the time required to parse, compile and execute T-SQL statements.
set statistics io on;
set statistics time on;
Enjoy and happy performance tuning! 🙂