2

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?

Improve this question
1
  • Is there anything else running on that server other than SQL Server? Otherwise I'd say you can be almost certain anything happening on that box is caused by SQL Server. That said, even the counters that are not restricted to SQL Server will report how SQL Server is behaving (or misbehaving).
    – Ronaldo
    Oct 6, 2021 at 11:33

1 Answer 1

Reset to default
8

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.

Improve this answer
5
  • 1
    Isn't there 1 counter that tells me whether page file is being used or not?
    – variable
    Oct 6, 2021 at 12:08
  • 1
    I think Taiob's answer here is pretty complete. However, if you want to know just one thing, that there is memory pressure, try querying sys.dm_os_ring_buffers. Here's an example: red-gate.com/simple-talk/databases/sql-server/…
    – Grant Fritchey
    Oct 6, 2021 at 12:37
  • @variable you are most likely referring to \Paging File\% Usage.
    – SqlWorldWide
    Oct 6, 2021 at 13:21
  • @grant - memory pressure specifically resulting in disk page file usage
    – variable
    Oct 6, 2021 at 13:39
  • If you run out of memory and trigger the write to the dm_os_ring_buffers, I guarantee, you're getting disk page file usage.
    – Grant Fritchey
    Oct 6, 2021 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.