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?