May 28, 2002 at 7:52 am
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...
May 28, 2002 at 8:15 am
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)
May 28, 2002 at 8:27 am
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
May 28, 2002 at 9:58 am
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?
May 28, 2002 at 10:52 am
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)
May 28, 2002 at 11:20 am
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
March 6, 2014 at 9:47 pm
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.
March 7, 2014 at 1:41 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply