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


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.

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.
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.