What are the reasons for a plan to be missing from cache for stored procedures?

  1. WITH RECOMPILE
  2. Dynamic SQL
  3. Encrypted code
  4. Significant data changes
  5. Update statistics
  6. What else?

I've worked on 2 servers (SQL Server 2008 R2 and SQL Server 2012) recently that didn't have plans in cache for very resource-intensive stored procedures. Many, maybe all, of the statements inside the stored procedures also didn't have plans in cache. Some of the stored procedures execute pretty frequently like a few times per second.

No memory pressure whatsoever. One of the servers has far more hardware than is needed.

I thought the missing plans were due to temporary table creations in the middle of the stored procedures, but that appears to be old information from SQL Server 2000 or earlier. Starting with SQL Server 2005, the recompiles happen at the statement level for the statements after the DDL. Is that true in all cases or can it still happen on newer versions?

What else could be the culprit for the missing plans? I've skimmed a few articles on this topic, but nothing seems to fit.

Optimize for adhoc workloads is enabled on the server I am looking at this week. One of the stored procedures is only executed once a day. I do have the code for that one. I don't have the code for the one that's executing over 100 times per minute, but I can get it. I won't be able to post the code, but I can describe it in relation to my question.

I don't believe anyone is freeing the procedure cache or dropping clean buffers. This client is using Solarwinds DPA as one of their monitoring tools. DPA did capture one of the execution plans for the statement in the stored proc that gets called once a day. That statement has a massive amount of reads due to non-sargable WHERE clause. If DPA captured the statement, then it's an estimated plan and was in the plan cache at one time. Just isn't there when we are troubleshooting. I'll have them start logging sp_WhoIsActive to a table.

I'm using sp_BlitzCache. (I work for Brent Ozar Unlimited) This will show the plan for the entire stored procedure as well as the plans for the individual statements, if they exist. If they don't exist, it has this for a warning "We couldn't find a plan for this query. Possible reasons for this include dynamic SQL, RECOMPILE hints, and encrypted code." And that warning is on the statements too.

TF 2371 isn't in place. I'm looking at the wait stats. The server is pretty bored. PLE is over 130,000.

I now have the code for 2 more stored procedures. One of them is using dynamic SQL with exec (@sql) so that one we know why there isn't a plan for it. But the other one, and this is the one that's running over 100 times per minute, doesn't have anything out of the ordinary. The only thing that stands out in that one is that the temporary tables are being created in the middle of over 1000 lines of code. It does call a bunch of child stored procedures too.

Regarding Plan Caching in SQL Server 2008, I'm not seeing any literals >= 8k, but one of the stored procedures has a comment about a bulk insert right before it calls another stored procedure. But the bulk insert doesn't appear in the outer stored procedure that I am looking at. The "Recompilation Threshold" section of the article is interesting. What I'm seeing for the temporary tables is tons of INSERTs (that could result in millions of rows), some updates and deletes. So lots of data changes to the temporary tables. Millions.

  • As you have the stored procs that repro the issue can you create a minimal example that repros it and in doing so find out the issue? – Martin Smith Mar 26 '17 at 13:21
up vote 2 down vote accepted

There are two plan cache DMFs:

sys.dm_exec_query_plan - returns cached plans in XML format, but only up to a certain size (and only as long as they can be formatted as XML in SQL Server, which means up to 128 nested levels.)

sys.dm_exec_text_query_plan - returns cached plans in text format, of any size. But the drawback is that when plans are large, you can't convert them to XML inside SQL Server, and even TRY_CONVERT as XML returns a null.

sp_BlitzCache only hits the former DMV (because it needs to analyze query plans as XML to do all kinds of slicing and dicing.) I made Github issue #838 to improve this so we could at least alert users to go check sys.dm_exec_text_query_plan for their bigger queries. We still won't be able to do XML analysis on it, though.

  • And also the same reason sys.query_store_plan.query_plan is nvarchar(MAX), not XML (SQL trivia). – Remus Rusanu Apr 8 '17 at 6:38
  • @RemusRusanu ooo, so large plans show up there! Nice. – Brent Ozar Apr 8 '17 at 11:37
  • We are checking if the missing plans are due to what you found. As soon as I hear back, I'll post an update. – Tara Kizer Apr 8 '17 at 22:12
  • I am marking this as the answer even though I haven't heard back from the client. It is the only thing left it could be and makes sense given the enormous queries. I will post an update if anything changes. – Tara Kizer Apr 18 '17 at 22:12
  • @TaraKizer you're only doing this because your quarterly review is coming up, right? I see what you did there. BONUS UNLOCKED. – Brent Ozar Apr 19 '17 at 11:08

Possibly you need to adjust the maximum size of the XML that can be returned by SSMS to a grid ?

  • No, because as Tara notes, even if you write it to a table and check length there, no data comes back. – Brent Ozar Apr 10 '17 at 11:46

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.