sql Find missing Indexes

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.*,
	migs.group_handle,
	migs.unique_compiles,
	migs.user_seeks,
	migs.user_scans,
	migs.last_user_seek,
	migs.last_user_scan,
	migs.avg_total_user_cost,
	migs.avg_user_impact,
	migs.system_seeks,
	migs.system_scans,
	migs.last_system_seek,
	migs.last_system_scan,
	migs.avg_total_system_cost,
	migs.avg_system_impact,
	mid.database_id,
	mid.[object_id],
	mid.statement,
	'DROP INDEX ' + mid.statement  + '.[missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' As drop_index_statement
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
--And database_id = 6 /*use to restrict to a single database*/
--And mid.statement = '[MyDataBase].[dbo].[MyTable]'/*use to restrict to a single table*/
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Recommends indexes to be added - use with caution. Thanks to: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx. A good rule of thumb - add those with an improvement measure greater than 100,000.00 Note: need to remove the database name from the drop statement before executing.

Updated: Wednesday 16th May 2012, 14:44am

There are 0 comments

Leave a comment of your own

Comments are currently closed.