2

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?

Improve this question
12
  • 1
    I've heard of many cases of this, but no real lockdown on the cause. One of the Microsoft devs said to check sys.dm_os_memory_cache_clock_hands and sys.dm_os_ring_buffers after this occurs to see if plans were evicted due to memory pressure. And this thread suggests that possibly Query Store is involved (do you have query store enabled?). What is the other process that is taking up CPU on the box? Can you move that to another machine?
    – Aaron Bertrand
    Feb 9, 2017 at 22:32
  • Do you have LPIM enabled? (Here is how you can check and here is how you can turn it on.)
    – Aaron Bertrand
    Feb 9, 2017 at 22:37
  • Other weird cause: min memory being set to the same value as max memory
    – Erik Darling
    Feb 9, 2017 at 22:39
  • @sp_BlitzErik, min server memory is set to 0 (16 MB running value) and 315000 MB max for both set and running.
    – The Great Laughter
    Feb 9, 2017 at 22:54
  • @Aaron, Query Store has the operation mode of Off, and has 0.0 MB used up. I thought I had enabled it at one point, but apparently not. LIPM comes back as not enabled. I've queued those DMVs up to check the next time this happens. Thank you!
    – The Great Laughter
    Feb 9, 2017 at 22:58

1 Answer 1

Reset to default
1

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
Improve this answer
1
  • 1
    Could you provide some explanation to the script? How exactly did it solve the problem? And what exactly was the problem?
    – Alexander Suprun
    Aug 21, 2019 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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