Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Checkpoint process Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 3:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 1,375, Visits: 2,670
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
Post #1502513
Posted Friday, October 11, 2013 1:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1503894
Posted Friday, October 11, 2013 1:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 1,375, Visits: 2,670
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.
Post #1503898
Posted Friday, October 11, 2013 2:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1503916
Posted Friday, October 11, 2013 3:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1503931
Posted Friday, October 11, 2013 4:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1503938
Posted Friday, October 11, 2013 5:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 1,375, Visits: 2,670
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.
Post #1503958
Posted Friday, October 11, 2013 5:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1503961
Posted Friday, October 11, 2013 5:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 1,375, Visits: 2,670
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.
Post #1503965
Posted Friday, October 11, 2013 6:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #1503968
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse