Glenn Berry (Blog) writes a lot of queries to extract information from the system DMV’s. One of them in particular I found extremely helpful in fixing some of the issues in my system. I took his query (the CTE at the top) and added some text manipulation to actually generate the create statements for you to save you some time. I had much grander plans for this, but unfortunately I’ve been meaning to post this for over a month now and simply haven’t had time to get back to it, so rather than just let it go by the wayside and never post it, I figured I’d just post what I had now and then possibly post an update sometime in the future if I ever finish it.
A couple of the known problems right now are:
- Index names could already be taken, there’s nothing here that checks to make sure they are unique based on other indexes in your database.
- No compression options are taken into account.
That said, I still found this fairly useful and hopefully somebody else will as well. Thanks again to Glenn for all his excellent work at creating queries to pull information from the DMV’s.
;WITH I AS (
-- Missing Indexes current database by Index Advantage
-- This DMV Query written by Glenn Berry
SELECT user_seeks * avg_total_user_cost *
(avg_user_impact * 0.01) AS [index_advantage],
migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns,
mid.included_columns,migs.unique_compiles, migs.user_seeks,
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
AND user_seeks * avg_total_user_cost *
(avg_user_impact * 0.01) > 9000 -- Set this to Whatever
)
SELECT 'CREATE INDEX IX_'
+ SUBSTRING([Database.Schema.Table],
CHARINDEX('].[',[Database.Schema.Table],
CHARINDEX('].[',[Database.Schema.Table])+4)+3,
LEN([Database.Schema.Table]) -
(CHARINDEX('].[',[Database.Schema.Table],
CHARINDEX('].[',[Database.Schema.Table])+4)+3))
+ '_' + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(
ISNULL(Equality_Columns,inequality_columns),
'[',''),']',''),' ',''),',',''),20)
+ ' ON '
+ [Database.Schema.Table]
+ '('
+ ISNULL(equality_columns,'')
+ CASE WHEN equality_columns IS NOT NULL AND
inequality_columns IS NOT NULL
THEN ','
ELSE ''
END
+ ISNULL(inequality_columns,'')
+ ')'
+ CASE WHEN included_columns IS NOT NULL
THEN ' INCLUDE(' + included_columns + ')'
ELSE ''
END CreateStatement,
'IX_'
+ SUBSTRING([Database.Schema.Table],
CHARINDEX('].[',[Database.Schema.Table],
CHARINDEX('].[',[Database.Schema.Table])+4)+3,
LEN([Database.Schema.Table]) -
(CHARINDEX('].[',[Database.Schema.Table],
CHARINDEX('].[',[Database.Schema.Table])+4)+3))
+ '_' + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(
ISNULL(Equality_Columns,inequality_columns),
'[',''),']',''),' ',''),',',''),20)
IndexName
FROM I
