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.

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.

MSDN pages for SET STATISTICS IO (Transact-SQL) and SET STATISTICS TIME (Transact-SQL).

Enjoy and happy performance tuning! 🙂

Missing Indexes DMVs