Recently I read a blog post on mssqltips.com about memory bottlenecks on SQL Server. In this article I read following:
The following performance counters on SQL Server: Buffer Manager object can also indicate memory pressure:
- High number of Checkpoint pages/sec
- High number of Lazy writes/sec
- High number of Page reads/sec
- Low Buffer cache hit ratio
- Low Page Life Expectancy
What caught my attention was that a high number of 'checkpoints pages/sec' can indicate memory pressure.
My understanding was that a checkpoint writes 'dirty' pages to disk. This can be triggered in different ways.
- automatic (to maintain recovery interval)
- indirect (to maintain target recovery time)
So high number of checkpoints indicates a very busy system (in case of automatic and indirect checkpoints).
Because a checkpoint never removes a page from the buffer cache I don't quite understand how high number of checkpoints/sec can indicate memory pressure. If there is memory pressure I would except to see a high number of 'lazy writes/sec'. The lazy writer removes 'cold pages' from memory, to make place for new pages.
How does a high number of checkpoint pages/sec indicate memory pressure?