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.