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.
Replies