Checkpoints
-
Hi all,
Can explicit checkpoints nested in a stored procedure increase performance? I am running a rather complicated stored procedure that involves inserting/updating around 8 million records for a CDW.
This proc uses a number of steps where it performs a larger inserts or updates. My logic is that after each step a checkpoint will free SQL Server from storing/caching all the inserts and updates associated with that step. Is my logic sound or way off track?
Cheers
-
I don't think so.
The checkpoint process causes modified data pages to be flushed to disk, and additionally writes a checkpoint marker in the transaction log. This happens about once a minute in any case.
By adding more frequent checkpoints, you are simply increasing the overhead of flushing the data changes, and also increasing the number of log writes (more checkpoint markers being written).
SQL server doesn't "store" the inserts and updates in a special area - these modifications are made to the data pages that will be in memory in any case (have to be in memory before the data can be modified at all), and will stay there whether you checkpoint the database or not. Thus, there's no additional overhead to not checkpointing them.
Those pages will only be removed from cache if space is required for other pages by other processes, and that has no connection to the checkpoint frequency.
-
Thanks Philip, I thought that might be the case.
-
I've found when running batches , usually on a database with simple recovery, adding a checkpoint and update stats ( on the tables involved ) between batches definitely improved performance. e.g.
while
exec proc .... parameters
checkpoint
update statistics table(s)
loop
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Interesting. I must confess, I've never actually tried this. My earlier reply was a purely theoretical one, but after Colin's follow-up I think I'd better investigate further!
-
From what I understand, Colin is right. A checkpoint should be about once a minute, but during batch processing, it's possible that they could be delayed, meaning that you have a high exposure of stuff that needs to roll forward in a restart, but also it could cause delays since until the data pages are flushed, the memory can't be reused.
So issuing them during a large load/processing of data could speed things up.
-
I'm afraid I'm sticking by my original reply. I've just run a simple test script that inserts and updates data in a table, once without intermeditate checkpoints and then with the checkpoints. The script was as follows:
create table philtest
(col1 int)
declare @counter int
set @counter = 0
while @counter < 5000
begin
set @counter = @counter + 1
insert philtest (col1)
values (@counter)
checkpoint -- commented out when required
update philtest set col1 = col1
checkpoint -- commented out when required
end
drop table philtest
Whilst this was running, I monitored the progress using SQL Profiler. The results were as follows:
Without additional checkpoints:
==============================
CPU = 20937 ms
READS = 200438
WRITES = 80
DURATION = 35390 ms
With Additional Checkpoints:
===========================
CPU = 36797 ms
READS = 270437
WRITES = 15501
DURATION = 65436 ms
This may be a fairly simplistic test, but I think it's clear to see that the number of writes has increased substantially (courtesy of the checkpoints), and the duration has nearly doubled.
System-invoked checkpoints cause data pages to be written to disk asynchronously via the lazywriter process thread, and so have a far lower impact on performance than when included as part of in-line SQL, when the subsequent code has to wait until the checkpoint has completed before it can continue.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply