Sql-server – Behaviour of performance counter “Checkpoint Pages/sec” under load

performancesql serversql-server-2016

I'm working on performance analysis of reports, resulting from different test cases. I'm using MS SQL Server 2016 Dev edition

Regarding one of the performance counters ('Checkpoint Pages/sec'), I understand the meaning of the counter. During a 22 second interval, the graph drops from 400 to 200 written changed pages per second, and then returns to 400.

Result from the graph:

  • 1-13 seconds, value is around 400
  • 14-16, falls down to 200, then returns to 400
  • 16-38, value is around 400
  • 39, falls to 200 then returns to 400
  • 61, falls to 200 then returns to 400
  • 84, falls to 200 then returns to 400

The values in the performance counter 'Disk Writes/s' are identical to 'Checkpoint Pages/sec'.

I expected these counters to be a straight line (without the peaks and valleys).

Can you explain this behavior? What do the drops in Checkpoint Pages/sec from 400 to 200 mean?

Best Answer

Checkpoints, like most things in SQL Server, are a big topic. A good place to start is this Microsoft Docs page: Database Checkpoints (SQL Server)

To respond to your main question though, check out this specific quote from that page:

The time interval between automatic checkpoints can be highly variable. A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

So "checkpoint pages / sec" will vary based on a lot of factors:

  • the type of checkpoints you're using (automatic or indirect)
  • if using automatic checkpoints, the value of the "recovery interval" server-level setting
  • if using indirect checkpoints, the value of the TARGET_RECOVERY_TIME database-level setting
  • disk latency

Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 50 milliseconds.

  • the transactional throughput currently happening on the database

All that to say: seeing peaks and valleys in the number of pages being flushed to disk by checkpoint operations is completely normal.

If the purpose of the performance testing you mentioned is to normalize (as much as possible) the number of pages flushed to disk per second, consider reading the documentation page I linked to above and adjusting those settings.

Related Question