Andrew Kelly in a recent post here advised visiting/revisiting the SQL Server I/O basics, and I completely agree. A firm grasp of the basics can make it easy to understand some system behaviors that otherwise may be puzzling at times.
A question that is often asked is how SQL Server performs the I/O writes in its checkpoints. More specifically, some folks are puzzled at why SQL Server checkpoints don't seem to write to disks using a constant block size. For the basics, Bob Dorr has a detailed description on the checkpoint I/O behavior in his two articles: SQL Server 2000 I/O Basics and SQL Server I/O Basics Chapter 2. You can read these articles for all the information. What I want to highlight is that, "SQL Server 2005 extends the capability of WriteMultiple up to 32 pages (256 KB)." And SQL Server checkpoints make calls to this WriteMultiple internal routine.
Now, note that one of the most fundamental disk I/O rules is that large sequential I/Os are more efficient than small random I/Os. So if SQL Server can write 32 pages in a single I/O, it would try to do it. As a matter of fact, as Bob Dorr mentioned in his article, SQL Server 2005 has gotten more aggressive in finding as many contiguous pages as possible and lumping them into a single I/O request. If SQL Server can't find contiguous pages, it would write one page at a time in its checkpoints, and that would be very inefficient.
To see this in action, you can run a simple test yourself. First, create the following table:
create table PageCheck (
c1 int identity,
c2 char(5000) not NULL
)
And then populate the table with one million contiguous pages:
declare @i int
set @i = 1
begin tran
while @i <= 1000000
begin
if @i % 100 = 0
begin
commit tran
begin tran
end
insert PageCheck(c2) values(' ')
set @i = @i + 1
end
commit tran
Now effectively disable automatic checkpoints:
sp_configure 'recovery interval', 32767
go
reconfigure with override
Case 1
Run the following SQL to update the first 500,000 rows (corresponding to 500,000 contiguous dirty pages):
update PageCheck
set c2 = 'abc'
where c1 <= 500000
Now we are ready to see how a checkpoint may behave by doing a manual checkpoint and observing the Avg. Disk Bytes/Write performance counter.
The Avg. Disk Bytes/Write counter would show that each request would be around 256K, or 32 8K pages.
Case 2
To see how a checkpoint behaves when there is no contiguous dirty pages, run the following SQL to update every other page:
update PageCheck
set c2 = 'xyz'
where c1 % 2 = 0
Do a manual checkpoint now, and observe the Avg. Disk Bytes/Write counter. The counter value would be around 8K.
The first case represents the best scenario for checkpoint I/O performance, and on my machine the manual checkpoint took ~21 seconds to complete. The second case however represents the worst scenario, and on my machine the manual checkpoint took ~71 seconds to complete. The difference was more than three times.