| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2011-09-21 : 04:55:28
|
Is there a way to relate the missing indexes back to the queries that relate to them?I have some really off-the-scale DMV results (Impact=6,749,100,644 !!), but I'm pretty sure they relate to adhoc queries we have done for analysis, rather than regular queries - and obviously no point creating indexes for those one-off'sAlso, some of the suggestions are clearly covering indexes (with awful selectivity) and I'd like to check the actual queries more carefully before implementing those. Looking at the index suggestion I can't even being to guess which query it might relate to (well ... I could perhaps find it with a global search of the code base, but that's going to be much slower than any reverse engineering I could do from the DMVs  ) |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-21 : 14:23:57
|
| Thanks for that Gail, I'll investigate tomorrow.Separate tack:If I just created all the "reasonable" indexes could I then easily query which ones were being used, or not (and preferably "by what query(s)")? I could then take a view on dropping the ones that are not relevant.We have a load test scheduled for tomorrow morning, and thus I could bung all the indexes on there without having to worry too much about adverse side effects on INSERTS/UPDATES and see what the benefit was during the Load Test. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-21 : 15:53:44
|
| Another reason to be careful with the 'create everything, drop what's not in use'...Create complete duplicate indexes and SQL will use both. You don't need both but that won't show from the index usage stats--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-21 : 16:21:16
|
| SELECT Col1 from tbl1 where Col1 = @Var1SELECT Col1, Col2 from tbl1 where Col1 = @Var1SELECT Col1, Col2 from tbl1 where Col1 = @Var1 and Col2 = @Var2Three queries like that (ones that aren't trivial as those are) will get you three entries in missing indexes. Doesn't matter which order you run them in1) Key = Col12) Key = Col1, Include = Col23) Key = Col1, Col2Three indexes where you only need one, SQL will use all three.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-21 : 19:34:26
|
| It probably won't, but if you have none of them, you'll get all three recommendations in the DMV.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 02:20:19
|
| Yes, I don't have any suggestions for indexes I already have, but there are duplicates / crossovers for the suggested ones. Perhaps the suggestions are for specific queries - so I have two, different, queries needing the same index and thus two, identical, suggestions? In which case I should add the "Impact" together I reckon.Its plenty good enough for me, I can merge them easily where, for example, there are two suggestions only differing by an INCLUDE column.The Load Test is running ... I'll see how it goes :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 03:02:09
|
| Load Test ran much better than previously, so looks like it did some good - now to analyse which bits actually did the good. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-22 : 04:53:25
|
quote:
Originally posted by Kristen
Perhaps the suggestions are for specific queries - so I have two, different, queries needing the same index and thus two, identical, suggestions? In which case I should add the "Impact" together I reckon.
Missing indexes is evaluated by the optimiser one query at a time.You won't get complete duplicates, just ones like I showed, additional include column, column moved from include to key, etc.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 05:01:11
|
| "You won't get complete duplicates"I got identical duplicates, same columns, same ordering - different "Impact"(Sorry, not sure I've kept the output so can't refer back to it, and have now created the indexes so won't see that output again) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-22 : 06:46:18
|
| DMV got cleared between captures? Or different databases?The optimiser checks to ensure that a missing index isn't already there before it puts the recommendation in.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 08:35:07
|
| Don't think so. These duplicates were in a single query, and that was restricted to a single database (unless I mucked up of course ...) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-22 : 10:23:49
|
| Columns in equality in one and inequality in the other is possible.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 11:03:43
|
I'll look out for that next time I see a dup, thanks.(I'm tempted to drop the index to see if I can reproduce the data  ) |
|
|
|
|
Recommended indexes DMV