I have a production database that is experiencing wildly fluctuating Page Life Expectancy (PLE) issues. (It crashes to zero at random times.)
I have been researching the PLE issue and have found something that seems to point to a VMWare issue, but I am not sure I am using the data right. It seems like I am losing buffer/cache pages.
I am using this query:
SELECT COUNT(*) AS cached_pages_count,
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY cached_pages_count DESC;
(Found here)
I am totaling the results (the count) before and after my PLE crashes. An example is 1,097,820 before and 131,394 after. So I seem to "lose" 966,426 pages.
My guess is that the hardware for all the virtual machines is under stress, so it will randomly swap out some memory from the server for a while. (This is just a guess.) When that happens all the pages are lost, so the PLE plummets.
So, am I using the sys.dm_os_buffer_descriptors
view correctly? From what I read it always shows used buffer/cached pages. So if it is empty (or significantly reduced), I either don't have the memory anymore, or it is empty.
(I would love a way to confirm this conclusion.)
Or is there another explanation as to why the count drops so much?
Information below the line was added from the OP's comments
Our System Admins manage the VMs. I am hoping to understand my query before I go to them with this data. The timing of the PLE crashes seems random from the database point of view. (No re-indexing or other high performance stuff happening during the PLE Crashes)
I have done a ton of work to see if it was work load related. And while there is one poorly performing query, it is not enough to use up all the cache. [There is] no rebuilding or other non-routine user activity on the server when the buffer counts go down. And even if it was, would I not see that being used in my query above? (Meaning if it was a SQL Server action, wouldn't the counts stay the same, just with different stuff?)
I don't have access to the VMWare settings. I was hoping to understand my findings better before involving those that do. The point of this question was to ensure I was using the view correctly first.
At the end of the comment chain:
I was trying to say that the PLE issue lead me to the loss of Buffer Pages issue. The query I was using to get PLE would show a low PLE because the pages were being lost. So what was in them was gone. It was a false reading because the amount of memory was reduced.
Here is my @@Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)