Scan count vs logical reads

Question

Scan count vs logical reads

SQL Show

SSCertifiable

Points: 6143
More actions
July 16, 2013 at 5:25 am

#295551

I have two versions of a query.
1. Scan count 136, logical reads 2776907,CPU time = 230848 ms, elapsed time = 55753 ms.
2. Scan count 0, logical reads 89225788, CPU time = 437696 ms, elapsed time = 46195 ms.
The only difference I can see is 2 query it taking lesser of 10 minutes. Which is good here? Both Execution plan has lots of hash joins.
Thanks

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

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Can you please post the execution plan?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Ignore the scan count, it doesn't tell you much of use.

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

SQL Show SSCertifiable Points: 6143 More actions · Answer 3

So I shall go ahead with 2nd plan? Should I consider the cpu time (more than first one) as well? I am testing it in Dev , have no idea of what production load will be. Is there a chance the 2nd query will result high cpu usage? I have no access to client's production box.

Thanks Gail.

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

SQL Show (7/16/2013)
So I shall go ahead with 2nd plan? Should I consider the cpu time (more than first one) as well? I am testing it in Dev , have no idea of what production load will be. Is there a chance the 2nd query will result high cpu usage? I have no access to client's production box.
Thanks Gail.

Can you post the actual execution plans?

^{“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

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

SQL Show (7/16/2013)
Is there a chance the 2nd query will result high cpu usage?

Since it uses twice the CPU of the first, yes.

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