January 27, 2009 at 8:34 am
This will generate the Create Index script based on the queries that are executed on the server by checking the columns
used in equal/inequal /where clause.
SELECT statement, migroupsstats.avg_total_user_cost as AvgTotalUserCostThatCouldbeReduced, migroupsstats.avg_user_impact as AvgPercentageBenefit,
'CREATE INDEX missing_IX_' + CONVERT (varchar, object_name(mid.object_id))
+ CASE WHEN mid.equality_columns IS NOT NULL THEN '_' ELSE '' END
+ ISNULL (replace(replace(replace(mid.equality_columns,', ','_'),']',''),'[',''),'')
+ CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END
+ ISNULL (replace(replace(replace(mid.inequality_columns,', ','_'),']',''),'[',''), '')
+ ' 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, '')+ ')'
+ CASE WHEN mid.included_columns IS NOT NULL THEN + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') ELSE
'' END AS create_index_statement
FROM sys.dm_db_missing_index_groups migroups
INNER JOIN sys.dm_db_missing_index_group_stats migroupsstats ON migroupsstats.group_handle = migroups.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON migroups.index_handle = mid.index_handle
order by 1
comments please........
Comments please....
Srihari Nandamuri
January 27, 2009 at 9:16 am
Index missing are "HINTS" based on usage at a particular time period they are not actual necessary indexes that you need. Be really careful with that!
* Noel
January 27, 2009 at 9:27 am
srihari nandamuri (1/27/2009)
This will generate the Create Index script based on the queries that are executed on the server by checking the columnsused in equal/inequal /where clause.
Not a very good idea.
The missing indexes are suggestions based on optimisations of single queries. The optimiser, when it writes a row there, doesn't check to see if there's a similar index that already exists, or a similar index already in the DMV. In addition, it's very fond of massive INCLUDE lists (often the entire table).
If you create all suggested indexes without checking for overlap, you're going to have a vast number of overlapping, redundant indexes which will bloat the database and slow down your data modifications.
In addition, please submit scripts using the "write for us" link on the left, don't post them in the forums.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply