July 11, 2009 at 8:10 am
when i run missing index script, it give recommendation to create index, but it will give recommendation on column which have already index with same ,
so why it give recommendation to create same index?
which is following :
SELECT t.name AS 'affected_table'
, 'Create NonClustered Index IX_' + t.name + '_'
+ CAST(ddmid.index_handle AS VARCHAR(10))
+ ' On ' + ddmid.STATEMENT
+ ' (' + IsNull(ddmid.equality_columns,'')
+ CASE WHEN ddmid.equality_columns IS Not Null
And ddmid.inequality_columns IS Not Null THEN ','
ELSE '' END
+ IsNull(ddmid.inequality_columns, '')
+ ')'
+ IsNull(' Include (' + ddmid.included_columns + ');', ';'
) AS sql_statement
, ddmigs.user_seeks
, ddmigs.user_scans
, CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) AS 'est_impact'
, ddmigs.last_user_seek
FROM sys.dm_db_missing_index_groups AS ddmig
INNER Join sys.dm_db_missing_index_group_stats AS ddmigs
ON ddmigs.group_handle = ddmig.index_group_handle
INNER Join sys.dm_db_missing_index_details AS ddmid
ON ddmig.index_handle = ddmid.index_handle
INNER Join sys.tables AS t
ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE ddmid.database_id = DB_ID()
And CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) > 100
ORDER BY CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) DESC;
July 11, 2009 at 9:17 am
There's probably a column different somewhere. Missing indexes doesn't take into account existing similar indexes. Take that DMV with a pinch of salt, it's recommendations are not always accurate and less work goes into the recommendation than DTA does.
It's a suggestion, nothing more.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply