patternsqlMinor
Missing execution plans for stored procedures
Viewed 0 times
storedproceduresexecutionformissingplans
Problem
What are the reasons for a plan to be missing from cache for stored procedures?
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
WITH RECOMPILE
- Dynamic SQL
- Encrypted code
- Significant data changes
- Update statistics
- 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.Solution
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.
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.
Context
StackExchange Database Administrators Q#167178, answer score: 7
Revisions (0)
No revisions yet.