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

Checkpoints and Performance Expand / Collapse
Author
Message
Posted Thursday, April 29, 2010 7:40 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 28, Visits: 457
Could I go the whole production day without a checkpoint and just issue the checkpoint command before and after production time?


How much RAM do you have on this system? How much is actually allocated to SQL Server? How much is actually being used day-to-day? Before the dirty pages are written to disk, they are on buffer cache. If you leave all of them there for a day, you might run out of RAM. I would only recommend if you from time to time, command a checkpoint when necessary. But again, if your bottle-neck is on disk IO, it will not help the long run. Your eventual goal is to have the system run smoothly auto-piloted.


Jason
http://dbace.us
Post #912842
Posted Thursday, April 29, 2010 7:46 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: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
2 year old thread.


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 #912855
Posted Tuesday, March 29, 2011 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 28, 2011 10:06 AM
Points: 8, Visits: 107
This is a very informative thread! I am dealing with a similar issue and according to your specs, my avg disk writes and reads are off the charts! They are pegging 100 for extended periods. We are running separate spindles for data files, system files, and log files on a SAN and we still see performance hits on a production db. I too have been monitoring the checkpoint in sysprocesses and ours hangs around 274125 in disk io and 418625 in cpu. This stays at this level throughout the day. Does this indicate poor disk setup?
Post #1085878
Posted Tuesday, March 29, 2011 11:59 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
The values in sysprocesses are cumulative since that session started. Since checkpoint runs on a system spid, it never disconnects.

Hence checkpoint has done 274125 in disk io and 418625 in cpu since the SQL Server last started.



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 #1085987
Posted Wednesday, March 30, 2011 12:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 28, 2011 10:06 AM
Points: 8, Visits: 107
Thanks. Could you tell me how to read the high disk secs/read/write numbers?
Post #1085995
Posted Wednesday, March 30, 2011 12:33 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: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
Could you maybe start a new thread for new questions and give some detail as to what your problem is?


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 #1086004
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse