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:

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:

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.