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)
- manual
- internal
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?
Best Answer
It does not directly indicate memory pressure. The value itself is cumulative and does not give much insight into memory pressure.
In the blog mentioned by you I would not rely on following 2 counters for memory pressure.
For BCHR the reason is already explained by Jonathan Kehayias in This Blog. It would give you incorrect interpretation if you rely on it because of SQl Server feature of Read Head.
The work of checkpoint in SQL Server is to decrease the overall recovery time of SQL Server. More frequent checkpoint means less amount of transaction log record needed to roll forward and hence shorter recovery. So I would not equate high checkpoint pages/sec to memory bottleneck. It would need further analysis or simply on a very busy system it can be ignored. It is just the SQL Server trying hard to make sure when database goes through crash recovery it comes online in minimum time letting you meed the RTO.
If I would want to look at memory pressure I would fire following perfmon counters