THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance Impact: Manual Checkpoints are not Necessarily Evil

In my two previous posts on the performance impact of frequent manual checkpoints and the I/O behavior of frequent manual checkpoints, I demonstrated that frequently issuing manual checkpoints can be bad for performance and why it's bad from the storage perspective. If you were led to believe that manual checkpoints were always bad, that wasn't what I intended to convey. In fact, it's not even the manual checkpoints or automatic checkpoints that were at the root of the issue. Rather, it's whether the storage was fully utilized.

Below, I have reproduced Script 1 from the first post on this topic with the number of inserts in a transaction commit and checkpoint changed from 16 to 100,000:

-- Name: Script 1
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON

DECLARE @i int
SET @i = 1
WHILE @i <= 3145728  -- (1024*1024*3)
BEGIN
    INSERT tbTest(i, filler)
    VALUES (@i, ' ')

    IF (@i % 100000 = 0)
    BEGIN
       COMMIT TRAN 
       CHECKPOINT
    END

    SET @i = @i + 1
END

The following chart shows the performance difference in terms of the duration of the script run between:

  1. Committing a transaction and issuing a checkpoint once every 100,000 inserts (Config. 1 for short) and 
  2. Committing a transaction and issuing a checkpoint once every 16 inserts (Config. 2 for short).

By decreasing the frequency of committing transactions and issuing checkpoints from once every 16 inserts to once every 100,000 inserts, the duration of the script run dramatically reduced from 167 minutes to merely 7 minutes. A huge improvement!

Again, in addition to the inherent overhead of repeated checkpoints, a major contributing factor was whether the storage was used efficiently. In the case of Config. 1--committing a transaction and issuing a checkpoint once every 100,000 inserts, the I/O behavior of the data file and the log file was as follows:

Config.1 100,000 inserts per transaction and checkpoint  
Data File Log File
Throughput ~54 MB/sec ~60 MB/sec
Block size ~26 KB/write ~60 KB/write

Compare these I/O numbers with the I/Os numbers below from Config. 2--committing a transaction and issuing a checkpoint once every 16 inserts--to see the difference in storage utilization:

Config. 2 16 inserts per tranaction and checkpoint  
Data File Log File
Throughput ~5 MB/sec ~4 MB/sec
Block size ~24 KB/write ~24 KB/write

Hopefully with these examples and data, we have established that the important thing to consider is to configure the script so that the database storage is utilized efficiently. It's a red herring whether one relies on manual or automatic checkpoints. Or is it really? That leads us to the question: is the revised script in this post the solution that takes the least amount of time to complete?

I'll tackle this question later.

Published Monday, August 20, 2007 3:27 PM by Linchi Shea

Attachment(s): checkpoint3.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

In my previous posts ( 1 , 2 , 3 ), I focused on the performance behavior of setting the checkpoints

August 23, 2007 3:47 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement