Index missing from execution plan

Question

Index missing from execution plan

SQLSACT

SSC-Insane

Points: 21838
More actions
December 6, 2019 at 7:49 am

#3705249

Hi All
Hoping to get something cleared up.
I have a query, it runs, SQL recommends and index, I create the index.
The query now runs much faster so all good there.
My confusion is that I cannot find this index anywhere in the query plan. I searched through the XML of the query plan and nothing.
The query plan has changed since adding the index.
Is something like this possible?

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I suppose it's possible that the query recompiled after you created the index but the query optimizer chose not to use the index. If you examine the graphical plan do you see any Index Seek operators or anything similar to that?

John

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

is it possible that by creating the index it altered the stats (or created new stats) and that's why you got the new plan?

MVDBA

SQLSACT SSC-Insane Points: 21838 More actions · Answer 3

Thanks all

There is definitely a new plan and the query executes much faster after creating the index, I just cannot find the index in the new plan.

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

It is possible, however unlikely, that when the query plan was recompiled after the index was created that the optimizer determined that it actually wasn't needed and found a different plan not using the index. The only thing I would suggest is dropping the index, run the query to see if the old plan and index recommendation is generated, save the execution plan to a file, recreate the index, run the query and save that plan to a file then compare the two. You could post the plans here if you want other eyes to look at it and give you their thoughts.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 5

Is the index unique? That can change the choices made by the optimizer even though the index isn't used in the plan. Otherwise, I'm with Lynn, test it, post the plans if you need help.

"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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

This is like fixing bad parameter sniffing. In order for the execution plan to change, there had to be a recompile. Causing a recompile is frequently the fix for bad parameter sniffing or a stale execution plan.

I'm also thinking that the index might not actually be needed but still caused the recompile to occur. To find out for sure, simply disable the new index and see what happens on a couple of runs. If things go back to being slow, rebuild the index to bring it back into play.

Like Grant says, an index doesn't every have to actually be used to be valuable. I can, in fact, produce statistics that the optimizer looks at that can cause it to make different, sometimes better, choices.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)