February 8, 2013 at 2:13 pm
Hi,
I'm using this script to look at recommended indexes:
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [IX_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' 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, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
However, the top recommended index (highest "improvement measure") already exists (I created it a few days ago) exactly as it is recommended by the dmv (I know it sometimes recommends indexes that are different only by the includes, but this is not the case here). How can that be? Thanks!
February 8, 2013 at 3:14 pm
Marius.D (2/8/2013)
I'm using this script to look at recommended indexes:SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [IX_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' 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, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
However, the top recommended index (highest "improvement measure") already exists (I created it a few days ago) exactly as it is recommended by the dmv (I know it sometimes recommends indexes that are different only by the includes, but this is not the case here). How can that be? Thanks!
Allow me to quote Microsoft on this matter: "The missing indexes feature is a lightweight tool for finding missing indexes that might significantly improve query performance. It does not provide adequate information to fine tune your indexing configuration. Use Database Engine Tuning Advisor for that purpose"
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 8, 2013 at 3:17 pm
That's probably the only possible explanation.. I am surprised, I've used it extensilvely and it never happened before. Thanks!
February 8, 2013 at 4:13 pm
Hmm, that still doesn't make sense.
Compare them very closely: there must be some difference somewhere ... or maybe you specified DESC on one of the existing index keys?!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 9, 2013 at 6:56 am
It could be old news too. Have you recycled your server since you added the index?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 9, 2013 at 7:34 am
In 15th line.... just remove >10 and give >0
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 0
February 9, 2013 at 9:48 am
There's a bug in the missing index DMVs that results in them showing indexes that exist (absolutely identical ones)
http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/
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
February 9, 2013 at 9:48 am
rajeshjaiswalraj (2/9/2013)
In 15th line.... just remove >10 and give >0WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 0
Why?
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
February 11, 2013 at 8:15 am
The indexes were identical.. I did create the index as recommended by the script, then I ran a duplicate indexes script, and yes, here they were, both old and new. I dropped the old index, and now it's gone from the missing indexes dmv..
Thanks everybody!
February 11, 2013 at 8:20 am
Scott:
or maybe you specified DESC on one of the existing index keys?!
I did not, but would that make a difference? I didn't think the missing indexes dmv has any kind of sort order information?
February 11, 2013 at 9:44 am
Marius.D (2/11/2013)
Scott:or maybe you specified DESC on one of the existing index keys?!
I did not, but would that make a difference? I didn't think the missing indexes dmv has any kind of sort order information?
I was thinking maybe an existing index had one (or more) columns as DESC, which caused SQL to recommend that same index again only with ASC. But it seems as if SQL simply has a bug and recommends an index identical to one that already exists.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 11, 2013 at 9:55 am
So that means if column C is sorted in the wrong order, it won't be used by that one query?
February 11, 2013 at 10:37 am
Marius.D (2/11/2013)
So that means if column C is sorted in the wrong order, it won't be used by that one query?
No, the sort order is mostly important when you're indexing to support sorts.
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
February 11, 2013 at 11:19 am
So, I understand an index with a column in the wrong sort order will still be used? Please correct me if I'm wrong, I really want to understand this.
If it's still used, it shouldn't be in the missing index dmv... (Scott)
Thanks again, sorry for insisting.
February 11, 2013 at 12:55 pm
I think Gail is saying that the query will use (and benefit from using) the index. However, if you sort by a field using an ORDER BY clause that's different than the sort order of the index, the index won't be as beneficial as it could be if the ORDER BY matched the sort order of the index.
I've always treated that DMV with skepticism myself. After reading Paul's blog entry that Gail referenced earlier, I'm glad I did. I didn't know the DMV contained a known bug, just that it would sometimes recommend indexes that didn't always make sense.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply