In my previous blog post on the performance impact of frequent manual checkpoints, I highlighted the performance peril of going overboard with manual checkpoints, and I suggested that a major contributing factor was the failure of frequent manual checkpoints to take advantage of the throughput potential of the underlying storage. But I didn't offer any proof for that statement.
In this post, I'll give you some empirical evidence in support of that statement.
I tested two configurations with respect to how often transactions were committed and checkpoints were issued in Script 1 listed in the previous post. I reproduced below the section of the script that controlled the frequency of committing transactions and issuing checkpoints for each of the two tested configurations:
Config 1: Committing a transaction and issuing a checkpoint once every 16 inserts
if @i % 16 = 0
begin
commit tran
checkpoint
end
Config 2: Committing a transaction once every 16 inserts and using automatic checkpoints
if @i % 16 = 0
begin
commit tran
-- checkpoint
end
Note that since a row in the test table is 8008 bytes in size, 16 inserts would add about 128KB of data to the table.
For each configuration, the following four I/O metrics were read off the Windows performance monitor:
- Data write throughput -- MB per second for the data file,
- Log write throughput -- MB per second for the log file,
- Data write block size -- KB per write for the data file, and
- Log write block size -- KB per write for the log file
The following four charts show the above I/O metrics for each of the test configurations:
Fig. 1 shows that the test script was able to insert significantly more data per second with automatic checkpoints than with manual checkpoints once every 16 inserts, ~190MB/sec vs. ~5MB/sec. In fact, 190MB/sec was near the maximum throughput of the I/O path on which the data file was placed. Fig. 3 shows that automatic checkpoints were able to issue much larger I/Os--in this case ~260KB/write--than were frequent manual checkpoints (~24KB/write). What is not shown in the charts is that when configured to use automatic checkpoints, the test script issued very few checkpoints during the test run, whereas the test script issued a checkpoint almost every one or two seconds. In short, automatic checkpoints were able to write to the data file much more efficiently than were frequent manual checkpoints.
It is rather interesting to note in Fig.2 and Fig. 4 that checkpoint frequency had a huge impact on the performance of transaction logging, even though the same number of inserts, thus the same amount of transaction data, were committed in both test configurations. With automatic checkpoints, SQL Server was able to perform larger log writes--64KB/write as opposed to ~24KB/write in the case of frequent manual checkpoints. In addition, with automatic checkpoints, SQL Server was able to do more writes per second than with frequent manual checkpoints--~740 writes/sec vs. ~166 writes/sec.
So in a way, committing a transaction and issuing a checkpoint once every 16 inserts was dealt a double whammy--significantly poorer write performance to the data file and significantly poorer write performance to the log file at the same time. No wonder the overall performance was so much worst than relying on automatic checkpoints.
Now, it should be reiterated that using automatic checkpoints is not the only approach to better performance. It is not even the most optimal approach. I'll discuss other solutions in a future post.