SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Checkpoint process


Checkpoint process

Author
Message
SQLSACT
SQLSACT
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5190 Visits: 2969
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
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7719 Visits: 2250
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
SQLSACT
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5190 Visits: 2969
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233663 Visits: 46361
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
SQL Show
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 1078
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233663 Visits: 46361
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
SQLSACT
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5190 Visits: 2969
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233663 Visits: 46361
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
SQLSACT
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5190 Visits: 2969
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233663 Visits: 46361
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search