SQL Server Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
This instance is on a shared physical server with 256 GB of Physical Memory. - The 2012 instance is set at a maximum memory of 97.7 GB. - The 2008 R2 enterprise instance with a maximum memory of 97.7 GB.
Patches were deployed over the weekend and the database server was rebooted.
Prior to the patches I was observing Page Life Expectency from Spotlight on the 2012 instance. I observed that the 2012 instance was using ~30 GB for Buffer Cache and ~3 GB for Proc Cache. What confused me is that Spotlight showed the instances was using 100% of its available memory, 97.7 GB.
30 + 3 < 97 I said to myself so I began to look at the memory drill downs in Spotlight. What I was shown was that there was 55 GB of free pages in the buffer cache.
As a sanity check I checked out what the 2008 R2 instance was doing, compared to the 2012 instance it handles much less volume. I was shocked when I saw the 2008 R2 instance using a total of 70 GB of it's 97.7 GB and 35 GB of buffer cache. The PLE on this instance was 37 Days!
Back to the 2012 instance, while the PLE was below 300 seconds, the Buffer Cache Hit Ratio never dropped more then a .1 of a percent. However we were getting I/O wait alarms and I felt this was due to making too many trips to disk.
After the patching and the server with both instances was rebooted I began to watch the 2012 instance again. In the morning buffer cache was above 80 GB and PLE was doing very well. Around 4 PM, I see a large drop (~20 GB) in buffer cache and a corresponding increase in free pages. As this was occuring, PLE started to suffer and I/O started to pick up again.
So all that long winded story to the question, what causes SQL Server, possibly specific to 2012 to dump it's buffer cache into free pages while PLE is nosediving?
Doesn't that seem to achieve the opposite effect?
