May 24, 2011 at 5:00 pm
I've got SQL Idera Diagnostic Manager running across some SQL Servers, and there's one odd occurrence that separates one from the rest of them.
There's a Checkpoint Writes value that various from a value of 1,000 to 4,000 throughout the day where as all the other SQL Servers maintain a constant 0. I've tried googling this and find nothing very helpful.
Is this anything to be concerned about? What is Checkpoint Writes?
May 24, 2011 at 7:02 pm
When a checkpoint occurs all the dirty pages, stuff that has changed, gets written to disk. I haven't looked at Diagnostic Manager for quite a while, but I imagine it's recording this event. Whether or not the numbers you're seeing are bad depends on the general activity of the system. Is there a lot of data being manipulated there?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2011 at 7:14 pm
Thanks Grant.
How do I know when a checkpoint occurs? Yes, there is a lot of data getting thrown around on that server, as there's a server monitoring database on there that is more or less always being read/written to/from.
May 24, 2011 at 11:50 pm
Perfmon, the checkpoint pages/sec counter (that's probably what Idera's using). There's a traceflag I can't offhand recall that writes checkpoint info to the error log. There's an extended event that tracks them too.
Perfmon's probably the easiest. If checkpoint pages/sec is >0, a checkpoint is running.
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
May 25, 2011 at 2:00 am
Thanks Gail.
Is this anything to be concerned about? I don't see it relating to CPU/Memory/Disk usage because that's all low when this Checkpoint Writes gets into the thousands and thousands.
May 25, 2011 at 2:04 am
No.
Unless it's causing poor performance due to excessive IOs, in which case you'd be looking at optimising the IO subsystem. Checkpoints are part of normal operation. They run on a regular basis to write dirty data pages to disk and hence reduce the amount of work that SQL would have to do should the server crash.
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
May 25, 2011 at 3:12 am
Thanks Gail, much appreciated.
May 25, 2011 at 4:21 am
Sorry, went off to bed. Looks like Gail answered all the questions already.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 26, 2011 at 6:05 am
No worries at all Grant, thanks for your help 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply