Execution plan missing.

Question

Execution plan missing.

mig28mx

Ten Centuries

Points: 1090
More actions
April 12, 2018 at 6:40 pm

#330382

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.

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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: 182882 More actions · Answer 2

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: 1004504 More actions · Answer 3

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: 1003857 More actions · Answer 4

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.

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)

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

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