I listened to a .NET Rocks podcast recently on database design, and although I found most of the content near worthless … they did mention in passing some dynamic management views that are built into SQL Server that I was unaware of. They turned out to be immensely helpful, so I thought I would share them.
Some of the dynamic management views are designed to help you find “missing indexes.” While SQL Server is processing queries, it keeps track of some usage stats and this view allows you to see a list of indexes that SQL Server thinks would help performance if they were added. (I think the stats are cumulative since the last time the server was rebooted.)
Here is little T-SQL script I created that finds the top 10 missing indexes with the highest anticipated improvement for user queries. It is based on an example from SQL Books Online, which is where the scoring algorithm comes from. The score is simply meant to provide a relative comparison of the “anticipated cumulative improvement” implementing a particular index would have on query performance.
SELECT TOP 10
GS.avg_total_user_cost * GS.avg_user_impact * (GS.user_seeks + GS.user_scans) AS 'Score',
OBJECT_NAME(I.object_id) AS 'Table_Name',
I.equality_columns,
I.inequality_columns,
I.included_columns,
GS.avg_user_impact,
GS.avg_total_user_cost,
GS.user_seeks,
GS.user_scans
FROM sys.dm_db_missing_index_details I
INNER JOIN sys.dm_db_missing_index_groups G ON G.index_handle = I.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats GS ON GS.group_handle = G.index_group_handle
WHERE I.database_id = DB_ID()
ORDER BY GS.avg_total_user_cost * GS.avg_user_impact * (GS.user_seeks + GS.user_scans) DESC
The scores are relative, and there doesn’t seem to be a hard rule for what the score should be in order to need to create the index … but I have set my personal threshold at 200. If the “anticipated cumulative improvement” score is greater than 200, I create the related index … otherwise I don’t.
Creating too many indexes can hurt the performance of your server just as bad as not having enough indexes. The podcast actually mentioned a company that wrote a script that would look at this view every night, and simply create all the indexes it suggested. That turned out to be a horrible idea. You need to be the ultimate judge of what your “score threshold” is, and whether it would be wise to create a particular index.
What is neat is that often times when I could look at the suggested indexes, and know exactly what functionality would be boosted by adding a particular index. Most the time it was just something I had accidentally overlooked, and this view is designed specifically to help you find those things. It can also help you find ways to optimize indexes for common ad-hoc queries that users might run on the server. All-in-all this view is a great tool … when used wisely.
For more info, see these MSDN articles: