2

If I execute sp_BlitzFirst in my server, sometimes it tells me 2 things:

  • Plan Cache Erased Recently (indeed my plan cache is around a few hundreds MBs)
  • (sometimes) Page Life to low (indeed my page life expectancy is a few minutes and the buffer is down a few GBs instead of 40-50 most of the time)

Most of the time it's ok and the page life is around 2hour.

If I execute this:

DECLARE @path NVARCHAR(260)
SELECT @path=path FROM sys.traces WHERE is_default = 1

--Security Audit: Audit DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG,
--DBCC CHECKALLOC, DBCC CHECKFILEGROUP Events, and more.
SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName  
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (116) AND (TextData like 'DBCC FREE%' OR TextData like 'DBCC FLUSH%')
ORDER BY StartTime DESC

I get nothing, so I guess this means no one executed any DBCC FREEPROCCACHE or DROPCLEANBUFFERS. In case of memory pressure, SQL Server doesn't empty the cache (plan or buffer) but instead remove some items (with an algorithm of it's own), so I shouldn't see everything almost empty.

So what could empty my cache?

Trace with:

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%DBCC%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%ALTER DATABASE%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_configure%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'exec sp_reset_connection'

Didn't give anything, I don't have anything about FLUSHPROCINDB or FREEPROCCACHE or FREESYSTEMCACHE or ALTER DATABASE in my trace.

Perf Monitor of my cache we can see the drop occurring every 3-7min:

Perf Monitor of my cache we can see the drop occurring every 3-7min

No sp_configure after 2016-11-23 when I run this query:

Link

No flush in sp_readerrorlog. The server hasn't rebooted.

4
  • Changing certain sp_configure options will cause plan flush.
    – Kin Shah
    Dec 1 '16 at 15:38
  • 1
    Glen Berry lists some things that can cause the plan cache to be cleared, and notes that you should get a message about this in your error log. Run sp_readerrorlog and search the results for "flush". sqlskills.com/blogs/glenn/…
    – Joe Hayes
    Dec 1 '16 at 15:50
  • @JoeHayes the last message was 10hr ago (which is weird but it doesn't match my problem) Dec 1 '16 at 15:55
  • Not really a duplicate but related: Procedure Cache Dropping
    – Tom V
    Dec 2 '16 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.