SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scan Count Vs Logical Reads


Scan Count Vs Logical Reads

Author
Message
wg26
wg26
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 1
I have used different indexes for the same table to test query performance. I got two different results as the following:

1) scan count 1, logical reads 580
2) scan count 2, logical reads 523

I am wondering which one is better than the other, high scan count but low logical or low scan count, high logical? Can anyone give me some suggestions? specially, when the data set becomes really large, which one has long term benefit? Thanks...



Antares686
Antares686
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25710 Visits: 785
Scan counts should be as low as possible. As for Logical Reads, you want them to be as high as possible and more than physical reads is preferred (read from cache as opposed to drive).

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)



Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24463 Visits: 2746
Just to clarify, you want logical reads to be higher than physical reads (this is based on caching, if you're low on memory or its the first time in a while that you've hit that data it will not be cached) but you want to strive to keep BOTH numbers low. Really low! Obviously if you have a lot of data it can't always be helped. I typically pay extra attention to any operation generating more than 1500-2000 reads, just as my own baseline.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
wg26
wg26
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 1
Thank you guys!

I can understand that we should keep Scan Count and Logical Reads as low as possible. But When you come to the point where you have to choose between high Scan Count and low Logical Reads and Low Scan Count and High Logical Reads,which one we should go with and which one gives long term benefits?

As for Antares686 reply, I am a little confused here, usually we want to keep logical reads as low as possible to increase performance, but you mentioned here, we should want them as high as possible? Can you please tell me why so?



Antares686
Antares686
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25710 Visits: 785
I' sorry I am having one of those brain dead days. So instead of descriding right myself take a look here this should help better than I am. http://www.sql-server-performance.com/statistics_io_time.asp But Logical should be as low as possible and scan count should be kept low but is not as critical as logical reads, so high scan count with a lower logical should perform better.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)



Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24463 Visits: 2746
Look at cpu count along with the those numbers when you profile - given a choice of the scenarios where the # reads are the same (or about) I'd go with the one with lesser cpu usage.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
ariski 64798
ariski 64798
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 154
Ignore Scan Count, it is not important. Focus on how to lower Logical Reads. Based on http://www.practicalsqldba.com/2013/07/sql-server-performance-tuning.html.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213801 Visits: 46262
Please note: 12 year old thread.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search