What perfmon metric will help me to monitor whether the SQL server memory is under pressure and resulting in usage of page file on disk?

Posted on

Question :

SQL server is assigned 110GB memory.

It is consuming the entire memory.

I want to understand whether there is memory pressure or not.

Usually SQL server will remove old pages from the memory (that it currently doesn’t require) and pull in required pages from disk. However, when there is memory pressure – (assuming all pages in memory are required and actively used) SQL server will utilize the page file on disk as an alternate memory area.

What perfmon metric will help me to monitor whether the SQL server memory is under pressure (that is disk page file is being used)?

I know about Memory:Page Fault/sec – but this is not restricted to only SQL server memory pressure. What other perf mon metric can help me?

Answer :

My answer will not be a comprehensive one. Every data professionals have their own way of diagnosing a problem.

As far as performance counters I will look at these ones together.

  • SQL Server: Buffer Manager: Page life expectancy
  • SQL Server: Buffer Manager: Buffer Cache Hit Ratio
  • SQL Server: Buffer Manager: Lazy writes/sec
  • SQL Server: Buffer Manager: Free list stalls/sec
  • SQL Server: Buffer Manager:Free Pages/sec
  • SQLServer:Memory Manager: Total Server Memory (KB)
  • SQLServer:Memory Manager: Target Server Memory (KB)
  • Process: Page Faults/sec

sys.dm_os_sys_memory also exposed few of the counters that are very useful.
There are many conflicting articles about Total Server Memory and Target Server Memory. You want to be careful about what you read. This article has a nice explanation with screenshots.

Here are some general guidelines from Jonathan Kehayias about some of these counters.

Look at Page Life Expectancy, which should be well above the 300
number that most of the stuff online says. This tells you how long
pages are staying in the buffer pool, and a value of 300 equates to 5
minutes. If you have 120GB of buffer pool and it is churning ever 5
minutes, that equates to 409.6 MB/sec sustained disk I/O for the
system which is a lot of disk activity to have to sustain.

Look at Lazy Writes/sec which tells you that number of times the
buffer pool flushed dirty pages to disk outside of the CHECKPOINT
process. This should be near zero.

Look at Free Pages/sec and Free List Stalls/sec. You don’t want to
see Free Pages bottom out which will result in a Free List Stall while
the buffer pool has to free pages for usage.

Look at Memory Grants Pending which will tell you if you have
processes waiting on workspace memory to execute.

There is a nice video by Glenn Berry which comes with his SQL Server Diagnostic Memory Queries.

I also like sp_PressureDetector by Erik Darling.

Leave a Reply

Your email address will not be published. Required fields are marked *