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

SQL Server Checkpoint I/O Behavior

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.

Published Saturday, January 19, 2008 3:11 AM by Linchi Shea

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

 

Pat Emmons said:

Thank you

February 1, 2008 11:34 AM
 

Philip Orleans said:

I need to know how to disable checkpoints at all for a specific database, not for the whole server. Is tha possible? I am looking insert a few hundred thousand records quickly and it is taking too long. I don't care if there a crash and my data is lost, for I will reconstruct it when the server starts. The tables are a read-only and read-fast buffer.

venefax at gmail dot com

April 24, 2008 1:40 PM
 

student said:

how many kind of " checkpoint " and what are they ?

how can I write a checkpoint statement ?

May 30, 2008 2:50 PM
 

DSC said:

Hi,

but I have to mention that even it's documented that the sql server tries to lessen the burden of checkpointing i/o by itself. Using a SAN with an heavy load of transactions you will see that the stress is rising as far that writing the checkpoint stops all other transactions as long it is written.

Is there anyway to get arround this?

Yes I know there's the possibility of doing checkpointing manually, but I can't believe that there is no other way than to checkpointing a dozen databases manually.

June 19, 2008 3:15 AM
 

jayant said:

..

March 2, 2009 2:51 AM

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