0

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?

  • 1
    Your last couple of paragraphs make it sound like SQL Server is responding to low PLE by dumping buffer cache. I think it's the opposite, and you're blaming the wrong thing - low PLE is a symptom, not a cause. – Aaron Bertrand Nov 18 '13 at 22:32
  • 1
    Something else is causing buffer cache usage to drop, which is then causing the PLE nosedive. As Aaron says, low PLE is a symptom - you need to find the root cause. – Simon Righarts Nov 18 '13 at 22:37
  • I am not necessarily blaming it, I just don't see how having 20 GB of free pages in the cache helps the situation. If the buffer cache was closer to 100% max memory and there was just too much data coming in and out causing churn that makes sense. – Jeremie Nov 19 '13 at 2:50
  • Are there any specific Buffer Manager counters that I could be looking at to try to figure out what's going on under the covers? – Jeremie Nov 19 '13 at 2:53
  • Check this article blogs.msdn.com/b/axinthefield/archive/2013/10/22/… We're experiencing similar behavior. – user32587 Jan 8 '14 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.