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:
No sp_configure after 2016-11-23 when I run this query:
No flush in sp_readerrorlog. The server hasn't rebooted.
