What is a Read?
Published: 16th January 2014
I’ve spoken to a good few people now who didn’t actually know what a Read was inside SQL Server. They were discussing tuning of queries and, correctly, knew that less reads were advantageous and therefore that’s what they were looking for in a better performing query, but didn’t really know why.

Therefore I thought that, following on from my last post, I would quickly cover what a read actually is, what it means internally to SQL Server, and therefore why It is indeed beneficial to reduce them.

As mentioned, a good few people knew that many reads were bad, but they didn’t know why because they didn’t know what a read actually was.

When I asked them to take a guess, the most common answer was that they thought a read was when SQL pulled a record from disk, but therefore they couldn’t understand why there seemed no pattern to reads or why they didn’t mirror the number of records returned.

Well, that’s because that guess is close, but not right.

If you remember from my last post, I explained that records are not written to the hard drive as a standalone item but are, in fact, written to an 8k page.

Therefore 1 physical read is SQL Server reading 1 single 8k page from disk into the buffer pool (memory), and 1 logical read is SQL Server reading 1 single 8k page from memory.

Based on that it’s clear to see why reducing reads can only be beneficial. Reducing reads means that you hit the disk less hard (and we all know that disks can be quite a bottleneck) and that you take up less space in the buffer pool (memory is always precious… especially in Standard edition where you’re limited to 64GB).

This also now explains the issue surrounding someone saying to me that there was no “pattern” to the amount of reads… this is because you can have anything from 1 record per page, to several hundred. Therefore returning 10 rows could involve 10 reads but just as easily be just 1 read.

So let’s have a look at some examples to verify the above:

Firstly we’ll create a table with a small record size. Therefore we can fit a good few records per page.

use testDB
go

if object_id('testRead') is not null drop table testRead
go

create table testRead
(
  
id tinyint identity(1, 1) not null,
  
myDate smalldatetime default(current_timestamp) not null
)
go

insert into testRead default values
go 200


Based on the calculations from my previous post we can easily fit all 200 records on just one 8k page. Therefore running the following query we should expect just a single read and that’s exactly what we get:

set statistics io on

select
*
from testRead


So, now we’ve returned 200 records in just 1 read let’s swing the pendulum the other way:

use testDB
go

if object_id('testRead') is not null drop table testRead
go

create table testRead
(
  
id int identity(1, 1) not null,
  
myValue char(6000) default('a') not null
)
go

insert into testRead default values
go 200


Now, in this case I’ve ensured that we can only fit 1 record per page (as each record is more than half the size of a page), so we should now see 1 read per record? Exactly:

set statistics io on

select
*
from testRead


This should alleviate any confusion as to what a Read actually is and why it’s good to reduce them. It should also give a hint as to how to reduce them even further through database design, but if not then that will be discussed in future posts anyway.
Comments:
NB: Comments will only appear once they have been moderated.