Checkpoint process

Question

Checkpoint process

SQLSACT

SSC-Insane

Points: 21838
More actions
October 8, 2013 at 3:44 am

#299474

Hi All
I've seen it posted all over the web that using the checkpoint pages/sec counter is one of the counters used to check for memory pressure.
This makes no sense to me because the checkpoint process never actually removes pages from memory, it takes dirty pages, writes the changes to disk and marks the page as clean. As far as I understand, this page still exists in memory.
Is my understanding correct?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Here is the technical description of Checkpoint pages/sec: "Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed."

So I guess it will flush the pages from the memory too as and when required by the system.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

The Checkpoint process never removes pages from memory. It finds dirty pages, writes the changes to disk and marks the pages as clean. So that means that the page still exists in memory.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Correct, checkpoint writes dirty pages to disk to reduce the time required for database recovery should SQL restart unexpectedly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Show SSCertifiable Points: 6143 More actions · Answer 4

You are right. A high unusual metric of check point pages /sec may indicate your database need more memory since free pages in memory is required at high rate. To solve this requirement of more free pages, database engine may kick check point process too often which results this counter as high.

I suggest check for lazy writes per sec as well.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

SQL Show (10/11/2013)
A high unusual metric of check point pages /sec may indicate your database need more memory since free pages in memory is required at high rate.

No, because running checkpoint does not free up memory pages.

To solve this requirement of more free pages, database engine may kick check point process too often which results this counter as high.

Checkpoint is not triggered by low free pages. Checkpoint is triggered by the recovery interval and an amount of dirty pages and SQL's estimate of how long recovery would take

You're thinking of the lazywriter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

So, is it safe to say that measuring checkpoint pages/sec is absolutely useless when investigating memory usage and pressure on my SQL Server.

High lazy writes/sec is the way to go - among other things.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQLSACT (10/11/2013)
So, is it safe to say that measuring checkpoint pages/sec is absolutely useless when investigating memory usage and pressure on my SQL Server.

No, it's very useful. It's not useful alone, but then very, very few counters are useful alone.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 8

GilaMonster (10/11/2013)
SQLSACT (10/11/2013)
So, is it safe to say that measuring checkpoint pages/sec is absolutely useless when investigating memory usage and pressure on my SQL Server.
No, it's very useful. It's not useful alone, but then very, very few counters are useful alone.

Does that mean that if I have memory pressure, I could possibly see a high checkpoint pages/sec ? Doesn't make sense to me, considering that we have established that checkpoint never actually removes pages from memory.

If anything, I would think that high checkpoint pages/sec would indicate a busy write system.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

I said it's not very useful by itself. So alone it tells you nothing. Along with other counters however it can tell you a lot.

If you have severe memory pressure, checkpoint pages/sec will probably be low. Think about what manages memory and you should see why.

There are very, very few single counters that tell you what's happening with the system. Most of the time you need multiple counters or other pieces of information to see what's happening.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 10

Thanks Gail

Regarding the free buffer list. I understand that the Lazy Writer is responsible for maintaining a decent amount of free buffers to serve incoming requests. I found the following statement:

The work of scanning the buffer pool, writing dirty pages, and populating the free buffer list
is primarily performed by the individual workers after they have scheduled an asynchronous
read and before the read is completed. The worker gets the address of a section of the buffer
pool containing 64 buffers from a central data structure in the SQL Server Database Engine.
Once the read has been initiated, the worker checks to see whether the free list is too small.
(Note that this process has consumed one or more pages of the list for its own read.) If so,
the worker searches for buffers to free up, examining all 64 buffers, regardless of how many
it actually finds to free up in that group of 64. If a write must be performed for a dirty buffer
in the scanned section, the write is also scheduled.

Is this a readahead?

I'm asking because I setup a test scenario, the max memory was 3GB and I did a select * on my largest tables which is way more that 3GB. I didn't notice an increase in lazy writes, my PLE was however extremely low. Is this because of the quote above?

Thanks