Increased row size decreasing I/O throughput

September 30th, 2011 - 07:09 pm ET by Jeroen Mostert | Report spam
Here's a little puzzler. If anyone's encountered a similar issue or knows
what I'm doing wrong I'd love to hear it.

SQL Server 2008 R2. I've got a database housing a fact table for a data
warehouse. It has 722 million rows with 55 columns. Indexes are fully
rebuilt, statistics are good, etc. If I do this:

bcp "dbcc dropcleanbuffers; select one_column from bigtable;" queryout
nul -n -T

Then the I/O-subsystem is fully saturated. But if I do this:

bcp "dbcc dropcleanbuffers; select * from bigtable;" queryout nul -n -T

Then performance drops through the floor. Note that network I/O is not the
issue -- this is local (and this reflects production use).

I know what you're thinking -- "select one_column" uses an index while
"select *" does not. Good guess, but not this time. Both queries use a
clustered index scan, since there is no index on one_column.

Frankly, this is baffling to me. A clustered index scan is a clustered index
scan, right? In fact, gathering query statistics shows me SQL Server is
reporting the same number of the same types of reads, it just seems to read
a whole lot slower if more columns are involved. In fact, performance seems
to be roughly inversely proportional to the number of columns selected. The
position of the columns doesn't seem to be a factor.

Googling around gives no suggestions as to why a bigger row size should be
such a significant factor, but it is -- highly significant, to the tune of a
tenfold slowdown. Surely constructing the result set doesn't produce so much
overhead that I/O itself stalls... right? I'm open to ideas.

J.
email Follow the discussionReplies 6 repliesReplies Make a reply

Replies

#1 Erland Sommarskog
October 01st, 2011 - 12:58 pm ET | Report spam
Jeroen Mostert () writes:
SQL Server 2008 R2. I've got a database housing a fact table for a data
warehouse. It has 722 million rows with 55 columns. Indexes are fully
rebuilt, statistics are good, etc. If I do this:



And there are no LOB columns, all rows are safely below 8000 bytes in
size (so there is no overflow data)?

What is the average row size?




Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx

Similar topics