Patrick Purviance – SQL Server DBA Blog
Blogging impactful solutions for SQL Server DBAs worldwide

“Indexes? We don’t need no stinking indexes!”

Yeah, right…..Alright, now that I’ve got your attention (or at least I better have if you’re a DBA worth your weight in any known unit of measure), below is a quick query for finding AND generating a script to create missing indexes for SQL Server 2005+. Now, this is just my version of a way to do it, and you may have opinions which you’re welcome to share if it will improve the query in some meaningful way for myself and everyone else, but I’ll throw out the normal disclaimer: You MUST review the recommended indexes and determine for yourself if they are appropriate for your database/workload. They may make sense, they may not, so TEST, TEST, TEST!

Have fun and good luck optimizing!

UPDATED! Now includes better formatting in the output to more closely mimic the missing index feature recommendation in SSMS execution plans.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT  Obj.name as [Table Name]
	, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS [Relative Impact]
	,  'CREATE NONCLUSTERED INDEX [IX_' + rtrim(Obj.name) + 
		CASE WHEN mid.equality_columns IS NULL THEN ''
			ELSE '_' + replace(replace(replace(replace(IsNull(mid.equality_columns, ''), ',', '_'), '[', ''), ']', ''), ' ', '') 
		END + 
		CASE WHEN mid.inequality_columns IS NULL THEN ''
			ELSE '_' + replace(replace(replace(replace(IsNull(mid.inequality_columns, ''), ',', '_'), '[', ''), ']', ''), ' ', '') 
		END + 
		'] ON [' + Sch.name + '].[' + Obj.name COLLATE DATABASE_DEFAULT + '] (' + 
		CASE WHEN mid.equality_columns IS NULL THEN '' 
			ELSE mid.equality_columns 
		END + 
		CASE WHEN (mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL) THEN ', '
			ELSE ''
		END + 
		CASE WHEN mid.inequality_columns IS NULL THEN ') '
			ELSE mid.inequality_columns + ') ' 
		END + 
        CASE WHEN mid.included_columns IS NULL THEN ') '  
		ELSE 'INCLUDE (' + mid.included_columns + ')' 
		END + ';' 
		AS [Create Index Statement]
	, mid.equality_columns as [Equality Columns]
	, mid.inequality_columns as [Inequality Columns]
	, mid.included_columns  as [Included Columns]
FROM sys.dm_db_missing_index_group_stats AS migs 
	INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
	INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() 
	INNER JOIN sys.objects Obj ON mid.OBJECT_ID = Obj.OBJECT_ID 
	INNER JOIN sys.schemas Sch ON Obj.schema_id = Sch.schema_id
WHERE (migs.group_handle IN 
		( 
		SELECT TOP (500) group_handle 
		FROM  sys.dm_db_missing_index_group_stats WITH (nolock) 
		ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
					AND OBJECTPROPERTY(Obj.OBJECT_ID, 'isusertable')=1 
ORDER BY 2 DESC , 3 DESC

Leave a Reply

Your email address will not be published. Required fields are marked *