CHECKPOINT statement questions
-
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?
-
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.
-
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.
-
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.
-
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 -
Here's a couple of links about the trace flags:
http://support.microsoft.com/kb/315447
http://support.microsoft.com/default.aspx?scid=kb;en-us;906121&sd=rss&spid=2852
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
NICE Job Rudy! I knew I had seen that before
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply