Sql-server – Tracking Down Plan Cache Clearing

plan-cachesql serversql-server-2016

I'm dealing with a database whose plan cache seems to be periodically clearing out by itself. As an example, in the span of about 2 minutes, the SQL plan cache went from 12.5k MB to 3.1k MB. At this time and shortly after, the number of compilations shot up dramatically as well. The rest of the cache shrinks as well, with everything seeming to get smaller by a large amount.

I've looked through the default trace and don't see anything obvious. At first, I thought it was related to a linked server due to a correlation of some SHOW_STATISTIC calls coming from it during the same minute as when the cache cleared out, but it seems that that's what happens when a linked server executes a query across the way as it needs statistics to use. I don't see any DBCC FREEPROCCACHE calls in the default trace logs for any of these events either.

I have the transaction log that covers the timeframe when one of these events happened, but I'm still unfamiliar with navigating it.

I haven't seen any evidence to suggest it's one of the ALTER DATABASE or sp_configure commands that will clear the cache either. There are no events in the SQL Server Log that happen at the time of this clearing out.

I've noticed in SQL Sentry that a number of times that this happened, another process on the SQL box was taking up ~10% of CPU, but it's not during all of the events so I'm not certain if that's worthwhile to look into for this.

At this point, I suspect that a lot of data was changed during each of these events, and the resulting statistic changes made many plans in the cache obsolete, so they were kicked out. I'm not certain how to best check this though, without bogging the system down with an expensive trace. Is there an easy way to determine this through the default trace and transaction log?

Another factor in this is that some of the queries have over a minute (!) compile time, and are requesting multiple GB of memory per execution, while using a small fraction of that. I suspect these are all related problems, but am trying to determine the cause of the cache clearing to minimize the minute compilation issues.

In summary:
* Plan cache is dropping considerably in the span of a minute.
* The log doesn't contain any mention of commands that will clear the cache.
* I have the transaction log and default trace contents for the timeframe of a few of these events.

This is on SQL Server 2016 SP1 CU2, running in 2012 compatibility mode. Version: 13.0.4001.0. The server has 300 GB of Memory available. I haven't seen the cache get much larger than 14 GB, but most of the time it is about 12.5 GB.

Any ideas on where to go from here on how to determine what's causing this to drop?

Best Answer

on SQL2014 SP1 changing sp_configure parameters seemed to solve problem :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'access check cache bucket count',512;
exec sp_configure 'access check cache quota',2048;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO

Related Solutions

Sql-server – How to regenerate execution plans after clearing the plan cache

After clearing the plan cache on SQL Server, how do I regenerate execution plans for stored procedures without executing the stored procedures themselves?

You don't / can't. Execution plans are generated when the object is executed. If you want a plan in the plan cache, then the object must be executed. BUT, assuming that there is at least one input parameter to the stored procedure, you would need to use a value that represents probably the majority case, else you could be forcing a non-ideal / "bad" plan into the cache.

if I have to do this again, I'd like to avoid the performance hit from regenerating the plans while a user is waiting for a response.

You are better off taking the performance hit because you can't guarantee that the plan will even still be in the cache by the time the first person uses it. Plans can be dropped if SQL Server decides that it needs more memory for other operations. So if it does get dropped before the first person uses it, you saved them nothing, and now caused the server to generate the plan twice.

Besides, it is only a hit the first time. You are aware of this as you are trying to use it to your advantage in "pre"-caching it. But this also means that it will only be the first user that first time that gets a slight performance hit. After that, as long as the plan isn't dropped, others wouldn't be experiencing the performance hit anyway.

Save yourself a lot of wasted time / effort and don't worry about pre-caching. Worry more about performance problems with the queries in the stored procedures that would affect all users each time and spend your time wisely improving those trouble spots.

Sql-server – SQL Server 2016 SP1 CU3 clears the plan cache

You've got a few different questions in here:

Does the "Lock Pages in Memory" setting preserve the plan cache? Only indirectly. LPIM means that SQL Server won't page out to disk if it comes under memory pressure, but SQL Server will still give up memory when the OS is under pressure. Jonathan Kehayias covers this in detail. The short answer is that when the box is under pressure, given your 16GB min memory setting, you would still see SQL Server giving up memory (and reducing the contents of the plan cache) even with LPIM.

Does SQL 2016 SP1 CU3 clear the plan cache periodically? Not in a way that's different from other versions. Your title suggests that you're seeing different behavior since you applied the CU - if that really is the case, your best bet there would be to uninstall the CU (go back to the previous CU2), and see if the behavior changes. I bet, though, that you're seeing a behavior that just happens to have started recently.

How do I see what's clearing parts of the plan cache? Since you wrote that the plan cache isn't entirely cleared, you need to see what databases/tables/indexes/stats are involved in the plans that are disappearing. For example, you may have a table that's hitting the 20% change threshold that triggers automatic stats updates.

Since it's not really an emergency, I'm going to give you the casual, low-labor-intensive approach I'd take to fix it. I would run sp_BlitzCache periodically (disclaimer: I'm one of the coauthors, although my code in there sucks pretty bad), getting to know the top 10 most resource-intensive queries. (Maybe even log it to table with the OutputDatabaseName, OutputSchemaName, and OutputTableName parameters.) Then, when the plan cache clears, run it and look to see what plans disappeared. Do they have something in common, like do they all hit a major sales table? If so, what's the change rate looking like on that table? You can use the rowmodctr to get a rough idea of which tables are changing how often.

Since you're using SQL Sentry, you can also set up a custom monitor based on this:

SELECT COUNT(*)
FROM sys.dm_exec_query_stats WITH (NOLOCK)

And that'll give you a line graph to identify when your plan cache is dropping. If it's happening at very specific intervals (like every hour), then I'd use the techniques in this answer to track down what's causing it.

Best Answer

Related Solutions

Sql-server – How to regenerate execution plans after clearing the plan cache

Sql-server – SQL Server 2016 SP1 CU3 clears the plan cache

Related Question