Execution plan missing.

  • mig28mx

    SSC Eights!

    Points: 933

    Hi all.
    Recently I have found with the help of one of the Bent Ozar, scritps. One called spBlitzCache.
    And found some disturbing when the script tried to get the plan for a specific query.
    The meseage says that it cant find a plan for this query. Possible reasons include SQL RECOMPILE and encrypted code.
    I have reviewed the suspect query and found no sentences with this code.

    Do you know some other reasons to get this strange behaivor?

    Thank you in advance.

  • Sue_H

    SSC Guru

    Points: 89885

    mig28mx - Thursday, April 12, 2018 6:40 PM

    Hi all.
    Recently I have found with the help of one of the Bent Ozar, scritps. One called spBlitzCache.
    And found some disturbing when the script tried to get the plan for a specific query.
    The meseage says that it cant find a plan for this query. Possible reasons include SQL RECOMPILE and encrypted code.
    I have reviewed the suspect query and found no sentences with this code.

    Do you know some other reasons to get this strange behaivor?

    Thank you in advance.

    He posted an answer to the question before:
    sp_blitzcache not returning an execution plan for a long running query

    • The query is encrypted (like an encrypted stored procedure or function)
    • The server has Optimize for Ad Hoc turned on, and the query was only executed once
    • The server is under memory pressure, and plans are being removed from the cache
    • The plan has over 128 levels of nested elements, so sys.dm_exec_query_plan can't show it (but you can still get it via sys.dm_exec_text_query_plan if you want the text version)

    Sue

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    mig28mx - Thursday, April 12, 2018 6:40 PM

    Hi all.
    Recently I have found with the help of one of the Bent Ozar, scritps. One called spBlitzCache.
    And found some disturbing when the script tried to get the plan for a specific query.
    The meseage says that it cant find a plan for this query. Possible reasons include SQL RECOMPILE and encrypted code.
    I have reviewed the suspect query and found no sentences with this code.

    Do you know some other reasons to get this strange behaivor?

    Thank you in advance.

    Your best option is to do the estimated plan if the actual plan isn't cached, the plan will be the same although missing some actual statistics 
    😎
    My friend Grant Fritchley stated this and I'll take his word for it 😉

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Eirikur Eiriksson - Monday, April 16, 2018 12:20 PM

    Your best option is to do the estimated plan if the actual plan isn't cached, the plan will be the same although missing some actual statistics 
    😎

    It won't be missing actual statistics, because plans fetched from cache don't have actual statistics either. Estimated plan is the same as you would get if you pull a plan out of the cache.

    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
  • Jeff Moden

    SSC Guru

    Points: 993884

    mig28mx - Thursday, April 12, 2018 6:40 PM

    Hi all.
    Recently I have found with the help of one of the Bent Ozar, scritps. One called spBlitzCache.
    And found some disturbing when the script tried to get the plan for a specific query.
    The meseage says that it cant find a plan for this query. Possible reasons include SQL RECOMPILE and encrypted code.
    I have reviewed the suspect query and found no sentences with this code.

    Do you know some other reasons to get this strange behaivor?

    Thank you in advance.

    If you have "Optimize for Ad Hoc Queries" turned on, there may only be a shell of the execution plan the first time it executes. (EDIT... just saw that Sue also said that)  It also happens if the query uses a Temp Table.  That's NOT a reason to avoid the use of Temp Tables, though.

    --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."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Jeff Moden - Tuesday, April 17, 2018 7:51 AM

      It also happens if the query uses a Temp Table.  That's NOT a reason to avoid the use of Temp Tables, though.

    Shouldn't happen.
    Plan generation has to be deferred until the point the query executes, but it shouldn't prevent it from being cached.

    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

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

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