CHECKPOINT statement questions

Question

Post reply

CHECKPOINT statement questions

David Poole-249495

Ten Centuries

Points: 1106
More actions
September 19, 2005 at 3:09 am

#89047

The CHECKPOINT command flushes dirty pages to the disk.

This must mean that using this statement will increase DISK IO.

Am I correct in thinking that when a database is in SIMPLE (truncate log on checkpoint for the older DBA) recovery mode this will mean that the log files will truncate more frequently and therefore be smaller?

If transactions are generally successful then would I be correct in thinking that using the CHECKPOINT command would increase the resilience of the data because it is being flushed to disk?

On the other hand if transactions have multiple points of failure then a ROLLBACK will result in the disks being thrashed because the records affected will have to be physically removed from the disk?

Can we have a general discussion on the pros and cons of this command?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

To get you back up to the head of the queue, I will respond.

I have no idea!

But that is a great question as I try CHECKPOINT in our Development server when it looks to be slowing down... I would like to know more about the reprocussions as well and I know there are some pretty darn smart people here who could probably answer this...

I wasn't born stupid - I had to study.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

I'm not sure of what actually happens, but....

Won't a rollback act in the same way as doing the changes in the first place, i.e. the rolled back pages will be updated in the cache, and only get flushed to disk when a checkpoint is done.

Have a look at BOL for "recovery interval Option" for a discussion of some of the points you have raised.

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 3

I've just found a book called "Microsoft SQL 2000 Performance Tuning Technical Reference" which discusses CHECKPOINT in less that all-encompassing details.

YES - using CHECKPOINT will increase disk writes but increase durability of your data because it is committed to disk.

Committing to disk also lessens the recovery time on a MSSQLSERVER restart because there are fewer entries in the log that are marked as needing to be recovered.

The downside is that the writes can reduce performance of the database and even interfere with user transactions.

noeld SSC Guru Points: 96590 More actions · Answer 4

David,

Not too sure about the nitty-gritty of the command but I have found it to be very usefull when performing BIG deletes !! (Trying to avoid GHOST PROCESS)

In simple recovery mode it is issued automatically by the engine.

About the performance I am pretty sure there is a flag to control the performance "influence" of it!

Just my $0.02

* Noel