Index missing from execution plan

  • SQLSACT

    SSC-Insane

    Points: 21562

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148544

    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: 21247

    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: 21562

    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: 442205

    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: 395928

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995494

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

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

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