Search results matching tags 'SQL Server 2005' and 'Storage'

Performance Impact: Some Data Points on Read-Ahead

Linchi Shea — Fri, 04 Jul 2008 19:24:00 GMT

In the next series of posts, I'll focus on SQL Server I/O, revisiting some common issues and taking a closer look at some others. In each post and as always, I'll make the case with specific data points from my tests. For the first two posts in this series, let me check out the read-ahead technique used by SQL server.

Read-ahead is an important I/O optimization technique used by SQL Server. Intuitively, if SQL Server can correctly forecast the need for more pages and read these pages ahead of time when they are needed for query processing in memory, your query is expected to perform better. Bob Dorr in his classic SQL Server 2000 I/O Basics whitepaper has an excellent description on how SQL Server read-ahead works. Every SQL Server professional with any interest at all in the storage engine should read that paper.

But just how important is the read-ahead technique in query processing? Let's look at some results from an extremely simple test.

First of all, a single table was used in the test, and here is the definition:

CREATE TABLE test (
      i           int NOT NULL,
      j           int NOT NULL,
      dt          datetime,
      filler      char(5000) NOT NULL
)

I then created a clustered index on the i column (for this test column j was not used and you can ignore it):

CREATE CLUSTERED INDEX cix_test ON test(i);

And the table was populated with 2,000,0000 rows with the following INSERT statement in a loop with @i going from 1 to 2,000,000:

INSERT test(i, j, dt, filler)
SELECT @i, CASE WEHN @i % 2 = 0 THEN @i ELSE 2000000 - @i END, getdate(), 'abc'

With the statistics updated, I then ran the following SELECT query in two test scenarios:

DBCC DROPCLEANBUFFERS;
go
SELECT max(dt) FROM test;

The two test scenarios are as follows:

Test Scenario	Description
Read-ahead enabled	This is the default SQL Server behavior.
Read-ahead disabled	Read-ahead was disabled with trace flag 652: DBCC TRACEON(-1, 652)

First, let's look at the elapsed time of the above SELECT query in the two scenarios (the numbers reported in the following table are averages over several test runs):

Test Scenario	Elapsed Time of SELECT (second)
Read-ahead enabled	80
Read-ahead disabled	210

In both scenarios, the SELECT query was correctly processed with a clustered index scan. When read-ahead was disabled, the clustered index scan took almost three times as long as did the clustered index scan when read-ahead was not disabled. For this query, the performance difference was astounding.

I don't have access to the source code that controls read-ahead so I can't tell exactly why and how it made such a huge difference from the code logic perspective. However, from Bob Dorr's description in the SQL Server 2000 I/O Basics whitepaper, it's rather clear that SQL Server read-ahead is quite aggressive in exploiting the performance capacity of the storage I/O subsystem. So we can turn to observing the storage I/O behavior for explanation. The following table summarizes the values of the key I/O counters observed during the test runs:

I/O Counter	Value When Read-ahead is Enabled	Value When Read-ahead is Disabled
Bytes/Read	> 350KB/read	~ 8KB/read
Megabytes/sec	180 ~ 200MB/sec	~ 80MB/sec
SQL Server Readahead	~20,000 readahead pages/sec	0

Clearly, with read-ahead, SQL Server was able to take advantage of large sized I/Os (e.g. ~350KB per read). Large-sized I/Os are generally much more efficient than smaller-sized I/Os, especially when you actually need all the data read from the storage as was the case with the test query. From the table above, it's evident that the read throughput was significantly higher when read-ahead was enabled than it was when read-ahead was disabled. In other words, without read-ahead, SQL Server was not pushing the storage I/O subsystem hard enough, contributing to a significantly longer query elapsed time.

That is, the table was about 16,000MB in size. At ~200MB/sec, it would take about 80 seconds to read 16,000MB, and at ~80MB/sec, it would take about 200 seconds to read the same amount of data. And these numbers (i.e. 80 seconds and 200 seconds) match nicely the recorded query elaped times which are reported in the second table above.

In the next post, I'll check out the impact of disabling read-ahead on bookmark or key lookups when a nonclustered index is used.

SQL Server Checkpoint I/O Behavior

Linchi Shea — Sat, 19 Jan 2008 07:11:00 GMT

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

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.