Actual estimated execution plan recommends index to apply and increase perf by 25%, but doesn't work

Question

Actual estimated execution plan recommends index to apply and increase perf by 25%, but doesn't work

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
January 13, 2013 at 9:06 pm

#393929

All,
When I run a actual estimated execution plan for a query it will recommend an non-clustered index to apply that will increase performance by 25%, but after I apply the index it still recommends the same index. Does anybody have insight on why SQL would do this? The process query is still running very slow and I need some help.
Thanks in advance!
-Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Hard to say, we can't see what you see. Please read the second article I reference below in my signature block regarding assistance with performance problems. It will show you what you should post and how to post it to get the best help for solving performance issues.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

There's a bug with missing index DMV where it recommends an index that already exists.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

Bug aside, the only way to be sure what's happening is to read the execution plan to understand why it is doing each operation that it is using to retrieve your data. Then you can figure out if you need to adjust code or structure or both in order to speed up the query.

"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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

In furthering what has been said regarding the execution plans: look for any lookups and/or scans on large tables, clustered indexes, etc) paying special attention to the ones that have thick arrows - for certain a solution can be found if you attached the execution plan to this thread so everyone can take a look at it.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience