July 10, 2014 at 8:58 am
I ran one of my 'expensive' stored procedures with 'Include Actual Execution Plan.'
The output ( I green so I notice it - lol ) said I would speed up a query by 56% by adding an Index which already exists.
What might cause this, and how can I convince SQL to use the existing index? 😉
Thanks,
Ozzie
July 10, 2014 at 9:05 am
You might want to actually compare the suggested index creation text with the actual index's creation script, and see if the existing index actually covers the predicate(s?) you have in your proc, and whether the suggested index differs in any detail from the existing one. I can't tell you how often I've had a similar thing take place, only to later realize that the index creation suggestion did indeed differ. Alternatively, you can force the index to be used with a table hint. You can look those up in Books Online.
If you're still stuck, post the existing index creation script AND the suggested one.
Douglas Osborne-229812 (7/10/2014)
I ran one of my 'expensive' stored procedures with 'Include Actual Execution Plan.'The output ( I green so I notice it - lol ) said I would speed up a query by 56% by adding an Index which already exists.
What might cause this, and how can I convince SQL to use the existing index? 😉
Thanks,
Ozzie
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 10:09 am
Douglas Osborne-229812 (7/10/2014)
I ran one of my 'expensive' stored procedures with 'Include Actual Execution Plan.'The output ( I green so I notice it - lol ) said I would speed up a query by 56% by adding an Index which already exists.
What might cause this, and how can I convince SQL to use the existing index? 😉
Thanks,
Ozzie
Have you checked the included columns? Maybe there is the difference.
However, 56% is not very beneficial. I usually use this approach when I see over 95% speed up.
Igor Micev,My blog: www.igormicev.com
July 10, 2014 at 11:16 am
One of the things that can cause that is a bug. http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/
The other is that the sort order needs to be changed. You can check the sort order.
56% can be a huge factor. It all depends on how many times the index is going to be hit.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 11, 2014 at 3:45 am
Check your code. Are you using a function on a column or something along those lines. That might prevent the existing index from being used.
More likely it's suggesting something with an INCLUDE statement to make a covering index out of the existing index.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply