Unexplained PLE drop

SQLSaurus 2015-01-23 12:16:56

SQL 2008R2 SP3 instance, running along generally without issue.

Out of nowhere PLE drops from 30k to 0. No noticeable lazy writer activity, no change in buffer size, and no real change in activity other than 3 queries that were aborted around the same time (suspicious).

No evidence trail in the form of error logs, or other performance data, not even IO latency that would be considered out of the ordinary for this server.

Afterward, PLE slowly made it's way back up throughout the day, and applications didn't seem to take any memory related performance hits, so it basically seemed like a false alarm.

Any thoughts? Possible faulty counter?

Aaron Bertrand 2015-01-23 14:18:45
Did you look at those three queries? Is it possible any of them was some massive cross join that would have filled the buffer pool? I'm not sure what happens if a query evicts data but then is cancelled; I'm sure the buffer pool doesn't issue a recall on the data it evicted.
SQLSaurus 2015-01-25 10:33:56
I did, they weren't out of the ordinary, but we did have a parameter sniffing issue on this same instance earlier in the day with another query, and had to throw out that plan, plus there was maintenance on the whole server from the weekend that caused the whole plan cache to be emptied (thus all the new plans), so this is a possibility.

Unfortunately I can't retry them to find out for certain, but there's a likelihood similar queries will come in next week about the same time, so I'm setting up a condition to watch for them.

aawara 2015-02-26 18:06:19
Looking for the obvious, any server maintenance running at the same time? Checkdb or index rebuild would be a good candidate to look at. Also, if these 3 queries have silly order by clauses, that would generate a lot of tempdb buffer pool. A good script to find out the values is here.
SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' — Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
January 23, 2015
No Comments