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.