Reads vs CPU

Question

Reads vs CPU

AVB

SSChampion

Points: 11418
More actions
October 12, 2009 at 12:35 pm

#136499

I have a query that I am tuning because of a large number of reads it was originally producing. I re-wrote the query two different ways.
First Query: CPU 750 ms and 6585 Reads
Second Query: CPU 297 ms and 26,939 Reads
Because of the large difference in reads I would choose the first query however I started wondering at how much of an improvement in CPU time would it take before I considered the second query?
When does the read level or cpu time take precedence over the other?

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

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

Jeff Moden SSC Guru Points: 1004254 More actions · Answer 1

Some reads are actually logical reads from cached data in memory while others are physical reads. I've found that reducing the number of reads usually results in more efficient code which will also reduce the CPU time although with things like a Tally table, that's frequently not true.

A lot of folks would disagree with me but I've found that neither CPU nor the number of reads tell the full story. That's why I'll usually go with "Duration" as the overall target.

So, as with anything else, the answer is "It Depends" and "Duration" is actually a good measure of true performance because it includes CPU time, physical read time, logical read time, and time to deliver to the app.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AVB SSChampion Points: 11418 More actions · Answer 2

Thanks Jeff for the reply.

These were logical reads. I just figured that a few hundred milliseconds didn't outweigh the 10s of thousands of reads I was reducing the query by. I'll have to re-run it an pay more attention to the overall time.

George M Parker SSCertifiable Points: 5412 More actions · Answer 3

Agree that duration is the best measure to use when performance tuning with two exceptions:

1.) If a process is being blocked, the duration reported will be a misleading representation of the true story. In that case, reads and CPU will be very low but the duration could be quite large

2.) Applications that don't process the result set quickly enough - I've seen application servers max out to the point that they had difficulty processing the database result set and that made it look like the database server was the problem because of the long durations. But if you ran the same select statements with a different tool, they almost always came back immediately.

A lot of times I run both statements at the same time and analyze the query plans for both to see which one reports the "best" scenario.

Jeff Moden SSC Guru Points: 1004254 More actions · Answer 4

Great points, George... thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AVB SSChampion Points: 11418 More actions · Answer 5

Thank you both for the responses... now it's time to go weild my newly armed skills - lookout!

Paul White SSC Guru Points: 150467 More actions · Answer 6

Total worker time is the counter that makes most sense to me when tuning SQL Server.

Logical reads can be useful as a rough guide on occasion.

Elapsed time is also relevant.

However, both of these are a bit indirect and easily distorted.

Joe Chang has an excellent series of posts on this subject over at SQLBlog:

Why Logical I/O is a poor performance metric

Also see:

http://www.qdpma.com/SQLServerCostBasedOptimizer.html

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi