I have a love/hate relationship with MS SQL Server. While its easy to use and setup, its a pain to grow and manage. The tools seems to cover only 20% of the normal use case.
Anyways, wouldn’t it be nice if MS SQL Server actually told you where you are missing indexes, and where performance can be improved.
Well, it can! Actually, it won’t tell you, unless you ask… so to ask, run this script, and it will even have a column with the CREATE INDEX script for you!
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + 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) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
More details found here: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
Comments on: "Have SQL Server Tell You About Missing Indexes" (2)
This is a good thing indeed. I’ve blogged about similar scripts on http://blog.1smartsolution.com myself. One thing to note though is that not all indexes should be added blindly. Too many indexes is same bad as no indexes. So often this may be a back and forth process where some indexes are added, then the server is monitored and if needed some indexes are deleted, then other added etc. But it worth the time spent.
Sami,
I saw a post from you on Luis Majano’s blog about your deployment process and it sounds very similar to the process I have been working to set up. Would you take some time to write a post about your process and describe what it does, how to set it up, and the benefits you have realized by using the process?
Kind Regards,
Micah Knox