Logical Read vs Physical Read

Question

Logical Read vs Physical Read

Bobby Glover

SSCarpal Tunnel

Points: 4143
More actions
July 2, 2012 at 9:57 am

#277154

Hi
I just reduced logical reads in a sproc from 350,000 to 611,
but physical reads are up from 0 to 14.
The table rows = 325,000
No sure if this represents a good gain in performance as it is now going to disk.
The table is in a heap. Yes I know I should have a clustered index but there is some history that goes with that in this DB. Not for this topic.
Disk Sub-system is pretty fast, no real bottles neck there.
Regards
Robert

Viewing 5 posts - 1 through 4 (of 4 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

You only have physical reads when SQL has to go and retrieve the data from disk. If the data is already in cache then you won't have any of those. As you created a new index you would have a different data access pattern which more than likely invalidated the data being held in cache thus causing the physical reads. As a rule I ignore the physical reads side of performance tuning as it's very difficult to know when data will or will not be stored in the cache and thereby affecting performance. Stick with the logical reads, they will be pretty consistent (depending upon data distribution, DML and statistics).

Shamless self promotion - read my blog http://sirsql.net

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

Nicholas has stated it as I would. You want to concentrate on the logical reads, since lowering those lowers work. You can't control the physical reads, unless you can load the entire DB into memory. Otherwise, you are dependent on the state of the buffer pool. If it's a large table, obviously you want to not read the entire thing into memory, since you'll have physical reads, and you will also throw other stuff out of the buffer pool, but given a normal workload, you want to reduce the amount of reads overall so that there is less of a chance of needing to go to disk (more stuff in the buffer pool).

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 3

Nice one, I'll go with the changes that brought the LReads to 611.

Cheers

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Bobby Glover (7/2/2012)
Hi
I just reduced logical reads in a sproc from 350,000 to 611,
but physical reads are up from 0 to 14.
The table rows = 325,000
No sure if this represents a good gain in performance as it is now going to disk.
The table is in a heap. Yes I know I should have a clustered index but there is some history that goes with that in this DB. Not for this topic.
Disk Sub-system is pretty fast, no real bottles neck there.
Regards
Robert

Hi Robert

Interesting...any chance of posting the plans, pre and post changes? Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden