What is a read ?

Question

Post reply

What is a read ?

SQLSACT

SSC-Insane

Points: 21838
More actions
October 6, 2012 at 3:19 pm

#268187

Hi All
When using DMV's to assess your SQL instance, what is a read?
For example, when dealing with DMV's like:
sys.dm_exec_query_stats
sys.dm_exec_requests
There are columns like logical_reads, physical_reads etc...
My questions is, is a read when SQL reads one page?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

a logical read is the pages that are read in memory, a physical read is when the page is brought in from disk

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Perry Whittle (10/6/2012)
a logical read is the pages that are read in memory, a physical read is when the page is brought in from disk

Thanks

Does 1 read mean 1 page?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Yes a read is a page. A logical read may need to initiate a physical read first if the requested page is not in cache.

more info may be found at this link

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Chris Yates SSC Journeyman Points: 75 More actions · Answer 4

I've always gone by Microsoft's explanation:

“The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.”

Also, keep in mind that when you run a trace it is considered a logical read and not physical.

Physical reads for me have been a pain point when dealing with performance.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

CYates (10/8/2012)
I've always gone by Microsoft's explanation:
...
{snip}
Also, keep in mind that when you run a trace it is considered a logical read and not physical.

Have you got an MS link that explains that as well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Never mind. I got it. BOL states:

Reads
The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution.

In this case, I don't believe that "logical" means just "logical reads". It would appear to include both physical and logic reads for the "logically named disk".

Of course, I could have misinterpreted that. Any one else have something more finite?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Ah... got it. If found (and immediately forgot to copy the URL for) a post by Microsoft Certified Master Gail Shaw where she explained that SQL Server actually only reads from the "buffer pool" which is all "logical". Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.

It would appear that the only way to easily find out if physical reads were actually involved is to SET STATISTICS IO ON before you run the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Jeff Moden (10/8/2012)
CYates (10/8/2012)
I've always gone by Microsoft's explanation:
...
{snip}
Also, keep in mind that when you run a trace it is considered a logical read and not physical.
Have you got an MS link that explains that as well?

Yes, the link i posted above 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (10/8/2012)
Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.

Easiest way to think about it is that all reads are logical. Some of the logical reads may additionally be physical reads (the page required wasn't in the buffer pool)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 10

GilaMonster (10/8/2012)
Jeff Moden (10/8/2012)
Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.
Easiest way to think about it is that all reads are logical. Some of the logical reads may additionally be physical reads (the page required wasn't in the buffer pool)

Is it safe to say that when a page is requested and it's not in the buffer pool, 2 reads are happening, 1 Physical(To bring the page into memory) and 1 logical (to read the page in memory)?

Thanks

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

SQLSACT (10/8/2012)
Is it safe to say that when a page is requested and it's not in the buffer pool, 2 reads are happening, 1 Physical(To bring the page into memory) and 1 logical (to read the page in memory)?
Thanks

Thats exactly what is happening.

The physical read just go grabs the page, the logical read is interested what's on the page.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

GilaMonster (10/8/2012)
Jeff Moden (10/8/2012)
Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.
Easiest way to think about it is that all reads are logical. Some of the logical reads may additionally be physical reads (the page required wasn't in the buffer pool)

Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Perry Whittle (10/8/2012)
Jeff Moden (10/8/2012)
CYates (10/8/2012)
I've always gone by Microsoft's explanation:
...
{snip}
Also, keep in mind that when you run a trace it is considered a logical read and not physical.
Have you got an MS link that explains that as well?
Yes, the link i posted above 😉

That's what I get for reading threads without the right amount of caffeine in my system. I totally missed that. Thanks Perry.

The key part on the article from that link is the very first paragraph.

The I/O from an instance of the SQL Server Database Engine includes logical and physical reads. A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)