Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scan Count Vs Logical Reads Expand / Collapse
Author
Message
Posted Tuesday, May 28, 2002 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2002 12:00 AM
Points: 10, 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...




Post #4476
Posted Tuesday, May 28, 2002 8:15 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 1:24 PM
Points: 8,370, Visits: 743
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)



Post #34473
Posted Tuesday, May 28, 2002 8:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 2:51 PM
Points: 6,790, Visits: 1,902
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
Post #34474
Posted Tuesday, May 28, 2002 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2002 12:00 AM
Points: 10, 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?




Post #34475
Posted Tuesday, May 28, 2002 10:52 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 1:24 PM
Points: 8,370, Visits: 743
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)



Post #34476
Posted Tuesday, May 28, 2002 11:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 2:51 PM
Points: 6,790, Visits: 1,902
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
Post #34477
Posted Thursday, March 6, 2014 9:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 1, 2014 10:03 PM
Points: 1, Visits: 74
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.
Post #1548569
Posted Friday, March 7, 2014 1:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
Please note: 12 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1548608
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse