December 12, 2017 at 11:07 pm
Hi All,
Need some guidelines when considering creating a missing index.
When I get information from missing index DMV, I see the Index Advantage column with a number.
So, would like to know what is the good number to start consider creating the missing index.
I see values something like this.
IndexAdvantage
42956.51403
19178.71682
206.2279923
134.646743
17.41999549
11.9543448
11.64425079
9.910433535
9.291267473
8.844793242
6.151044558
6.137545818
5.731745057
0.208230283
0.054791055
Thanks,
Sam
December 13, 2017 at 1:15 am
I think the number is a percentage?
If you want a clearer indicator try using Brent Ozar's sp_BlitzIndex.
Take the missing index dmv with a pinch of salt though. It tells you exactly what was required to satisfy a single query, which SQL Server could have done with at the time of execution. You could end up with a dozen requests that look very similar.
Best thing to do would be to look at all the activity against the table, and devise an indexing strategy from that. Don't look at a single point in time
December 13, 2017 at 2:24 am
A GIANT pinch of salt. The missing index DMVs do not relate the suggested indexes back to queries in any way. You may have a suggestion for an index for a query that was run one time and will never be run ever again. These are, at best, light suggestions. Test and validate if they help or hurt. Best of all, don't use them unless you can correlate them to a query directly. One approach to solve this is to query the plans themselves for missing index suggestions. You can then correlate a query plan and any associated metrics on the query (query store or the plan cache) with the suggested missing index. That will help to ensure that the suggested missing index is something worth pursuing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 13, 2017 at 6:47 am
Grant Fritchey - Wednesday, December 13, 2017 2:24 AMA GIANT pinch of salt. The missing index DMVs do not relate the suggested indexes back to queries in any way. You may have a suggestion for an index for a query that was run one time and will never be run ever again. These are, at best, light suggestions. Test and validate if they help or hurt. Best of all, don't use them unless you can correlate them to a query directly. One approach to solve this is to query the plans themselves for missing index suggestions. You can then correlate a query plan and any associated metrics on the query (query store or the plan cache) with the suggested missing index. That will help to ensure that the suggested missing index is something worth pursuing.
+1 million to THAT! I'll also state that they frequently suggest key columns out of the best order (it seems to favor low cardinality leading columns, which is VERY bad for page splits... I know because I personally brought Expedia.com to it's knees for 2 minutes with such an index a decade or so ago). You also have to remember that indexes are a duplication of data and having a bazillion INCLUDEs (as frequently recommended by DTA and the like) are usually NOT the way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2017 at 7:27 am
Thanks for those valuable inputs Grant and Jeff. Learn somethings which never known. thanks a lot for taking time.
December 13, 2017 at 7:52 am
Jeff Moden - Wednesday, December 13, 2017 6:47 AMGrant Fritchey - Wednesday, December 13, 2017 2:24 AMA GIANT pinch of salt. The missing index DMVs do not relate the suggested indexes back to queries in any way. You may have a suggestion for an index for a query that was run one time and will never be run ever again. These are, at best, light suggestions. Test and validate if they help or hurt. Best of all, don't use them unless you can correlate them to a query directly. One approach to solve this is to query the plans themselves for missing index suggestions. You can then correlate a query plan and any associated metrics on the query (query store or the plan cache) with the suggested missing index. That will help to ensure that the suggested missing index is something worth pursuing.+1 million to THAT! I'll also state that they frequently suggest key columns out of the best order (it seems to favor low cardinality leading columns, which is VERY bad for page splits... I know because I personally brought Expedia.com to it's knees for 2 minutes with such an index a decade or so ago). You also have to remember that indexes are a duplication of data and having a bazillion INCLUDEs (as frequently recommended by DTA and the like) are usually NOT the way to go.
About the highlighted part, I've seen that the columns are suggested on their Ordinal Position in the table. I would never rely on that order.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply