Home > Handy Scripts, Tuning and Optimization > Finding Missing Indexes

Finding Missing Indexes

Another words, those indexes SQL Server beleives it would have benefitted from having

Original Script was written by Brent Ozar, Quest Software

The scipt uses another series of DMVs, introduced in SQL Server 2005:

– sys.dm_db_missing_index_group_stat

– sys.dm_db_missing_index_groups

– sys.dm_db_missing_index_details

I’ve modified it a bit to produce self-descriptive index names, instead of static ones and ON <filegroup> clause at the very end to account for index-dedicated filegroups. (the way it should be, right? 😉  )

DECLARE @v_index_filegroup VARCHAR(30);
--set the dedicated filegroup for indexes to be built at
SET @v_index_filegroup = '[INDEX]'
SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS [IMPACT]
,  'CREATE NONCLUSTERED INDEX IX_'
+ sys.objects.name COLLATE DATABASE_DEFAULT + '_'
+ REPLACE(SUBSTRING(REPLACE(REPLACE(REPLACE(mid.included_columns, '[', ''),']', '_'), ',', ''),
1, LEN(REPLACE(REPLACE(REPLACE(mid.included_columns, '[', ''),']', '_'), ',', ''))-1), ' ', '')
+
' ON ' + sys.objects.name + ' ( '
+ ISNULL(mid.equality_columns, '')
+
CASE
WHEN mid.inequality_columns IS NULL
THEN ''
ELSE
CASE
WHEN mid.equality_columns IS NULL
THEN ''
ELSE ','
END
+ mid.inequality_columns
END
+ ' ) '
+ CASE
WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')'
END + 'ON ' + @v_index_filegroup + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.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 WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE migs.group_handle IN
(SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats)
AND
OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
--"heavily needed" indexes
--and (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) > 10000000
ORDER BY [IMPACT] DESC , 3 DESC

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: