Jun 1

Written by: Javier Callico
6/1/2011 

More than half of the times I’m asked to troubleshoot performance issues on MS-SQL I end up finding a missing index as the cause of the problem.

I’m sure that as part of the initial database design process the most important indexes are created, but as the application evolves new queries are introduced and verifying if there is an index backing up the new query is usually an afterthought.

Finding the missing indexes before they become a “problem” is one of the best ways to contribute to your overall “health” database.

This query finds the top 100 missing indexes across all databases:

SELECT  TOP 100 
		[Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
		, avg_user_impact -- Query cost would reduce by this amount, on average.
		, TableName = statement
		, [EqualityUsage] = equality_columns 
		, [InequalityUsage] = inequality_columns
		, [Include Cloumns] = included_columns, d.statement
FROM		sys.dm_db_missing_index_groups g 
INNER JOIN	sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
INNER JOIN	sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

Not as bad but inefficient as well are the existing indexes not being used since maintaining them may also come with a performance penalty.

This query finds the top 100 indexes not been used across all databases:

SELECT TOP 1
		DatabaseName = DB_NAME()
		,TableName = OBJECT_NAME(s.[object_id])
		,IndexName = i.name
		,user_updates	
		,system_updates	
		-- Useful fields below:
		--, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
	AND	user_seeks = 0
	AND user_scans = 0 
	AND user_lookups = 0
	AND s.[object_id] = -999
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB	'USE [?]; 
-- Table already exists.
INSERT INTO #TempUnusedIndexes 
SELECT TOP 100	
		DatabaseName = DB_NAME()
		,TableName = OBJECT_NAME(s.[object_id])
		,IndexName = i.name
		,user_updates	
		,system_updates	
		-- Useful fields below:
		--, *
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
	AND	user_seeks = 0
	AND user_scans = 0 
	AND user_lookups = 0
    AND i.name IS NOT NULL
    ORDER BY user_updates DESC
;
'

-- Select records.
SELECT TOP 100 *  FROM #TempUnusedIndexes ORDER BY [user_updates]  DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes

These scripts were taken from a MSDN magazine article named “Uncover Hidden Data to Optimize Application Performance” I read years ago and since then they have become an indispensable part of my tool belt.