High number of Checkpoint pages/sec and memory pressure 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?
How does a high number of checkpoint pages/sec indicate memory pressure?


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.


Buffer cache hit ratio(BCHR)
Checkpoint pages/sec


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


SQLServer:Buffer Manager object Page reads/sec
SQLServer:Buffer Manager object Free Pages
SQLServer:Buffer Manager object Page Life Expectancy 
SQLServer:Buffer Manager object Free List Stalls/sec 
Memory Manager: Total Server Memory (KB)
Memory Manager: Target Server Memory (KB)
Memory Manager:  Memory Grants Pending
Memory Manager: Free Memory (KB)