February 28, 2010 at 4:30 am
Hi,
I am examining query's executions costs (SELECT queries) using different indexes. it is known that we should have the smallest number of physical and logical reads. But how important is relation between physical and logical reads?? I have few queries costs that makes me confused .
Query 1 using different kind of indexes i've got:
A)
physical reads 251
logical reads 5848
SQL Server Execution Times:
CPU time = 1547 ms, elapsed time = 6427 ms.
B)
physical reads 273
logical reads 3695
SQL Server Execution Times:
CPU time = 1594 ms, elapsed time = 6873 ms.
elapsed times very similar but where i have less physical reads i have more logical reads.
Query 2
A)
physical reads 8
logical reads 1133
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 1179 ms.
B)
physical reads 46
logical reads 576
SQL Server Execution Times:
CPU time = 313 ms, elapsed time = 1472 ms.
elapsed times very similar but where i have less physical reads i have more logical reads.
Query 3
A)
physical reads 806
logical reads 26265
SQL Server Execution Times:
CPU time = 531 ms, elapsed time = 7207 ms.
B)
physical reads 574
logical reads 267375
SQL Server Execution Times:
CPU time = 469 ms, elapsed time = 2188 ms
Here we have an interesting situation, in B i have less physical reads but logical reads increases 10 times. Although logical reads increases 10 times elapsed time decreases 3times !!
Could you help me in making decision which costs are better/ more efficient??
February 28, 2010 at 7:37 am
The difference between Logical Reads and Physical Reads is essentially one of Caching & Batching. There is a lot more detail to it in terms of Microsoft's specific terminology & jargon, but in strict performance theory, that's what this is.
Caching is the act of turning a physical read request into a logical read request, by return the requested information from memory without having to wait for a physical IO. Of course, in order to be able to do this the requested data must already be in memory, which gets into the many dozens of different caching strategies, which is a much more complex discussion. The important things to know are 1) Physical Reads require you to wait for an actual Disk IO, however Logical Reads require only the CPU time to copy (or remap) the data, and 2) you do not control this directly, SQLServer does this transparently for you.
Batching refers to the differences in size* between Logical IO and Physical IO. In general Logical Reads tend to be smaller than Physical Reads, so this too affects the difference that you see numerically between the two.
The takeaway from this is that 1) the number of logical reads is determined by the SQL query and the data sizes and structures and should be consistent with respect to those two things. Thus, logical reads are consistent, deterministic, and predictable, and therefore in all cases but actual production load (or an extremely good simulation of it), you should use the Logical Reads.
and, 2) because Physical Reads, are at *least* 1000 times slower than Logical Reads, your actual performance (elapsed/response time) is primarily determined by the Physical Reads, and not the Logical Reads. However, because Caching and Cache effectiveness are primarily influenced by all kinds of things neither directly under your control, nor reliably predictable ahead of time (amount of memory, SS config, HW config, number of current and recent users, the current and recent queries of those users, the current and recent activity rate of those users, etc.), there is no reliable nor consistently fair way to go from physical reads in a test to what that will actually mean in production.
Which all leads to this seemingly paradoxical rule:
* (technically, things other than size play into this also, but thats a detail)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 28, 2010 at 8:29 am
Here is an example that demonstrates what I said above:
CREATE SYNONYM [dbo].[SomeBigTable]
FOR [dbo].[<Insert Your Table Name Here>]
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
set statistics TIME ON
set statistics IO ON
GO
print '==== 0 ===================='
SELECT COUNT(*) FROM SomeBigTable WHERE CHECKSUM(*) < 0 --
GO
print '==== 1 ===================='
SELECT COUNT(*) FROM SomeBigTable WHERE CHECKSUM(*) < 0 --*
GO
print '==== 2 ===================='
SELECT COUNT(*) FROM SomeBigTable WHERE CHECKSUM(*) < 0 --**
GO
print '==== 3 ===================='
set statistics TIME Off
set statistics IO Off
GO
DROP SYNONYM dbo.SomeBigTable
If you run this script (remember to change the Synonym's table name to one of yours), you should notice that the Logical IOs are exactly the same for all three queries.
However, the first query is slow and has substantial Physical IOs (physical reads + read-ahead reads), whereas the second is faster and has much less (though it may still have some), and by the third query, physical IOs should have disappeared altogether.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 28, 2010 at 8:42 am
Ah... very cool, Barry. I was going to simply state that physical reads are disk reads and since disk reads are a lot slower than memory reads, when all else is equal, one should probably strive to reduce the number of physical reads. I would have missed the "predictability" factor and a couple of other things by a mile though.
Hmmm... A lot of people miss the difference between logical and physical reads and that total reads (like what shows up in Profiler) isn't necessarily a good measure of performance. I'm thinking that maybe this would make an excellent short article on your part.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2010 at 8:43 am
Heh... you posted a code example while I was writing a reply. Now that I've seen that, I'm sure this would make an excellent article. You should go for it!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2010 at 9:21 am
Barry,
A very small thing regarding your script: if you are going to issue DROPCLEANBUFFERS, you should always issue CHECKPOINT first. Otherwise, you're just dropping unmodified data from the Buffer Pool. CHECKPOINT flushes dirty pages to disk, so a subsequent DROPCLEANBUFFERS will just about clear the data cache completely.
Second point. My own personal view is that logical IO can be misleading in SQL Server. CPU usage is often a more reliable indicator.
See:
The SQL Server Cost-Based Optimizer and
Why Logical IO Is A Poor Performance Metric
Paul
edit: qualified my opinions
February 28, 2010 at 10:52 am
Guys,
Thank you for answers. But i still have to determine which of these costs mentioned above are better. Let's look on Query 1
having similar times what is better?. When i have 22physical reads less but 2000 more logical reads or vice versa??
and please take a look on Query 3 . What is better 232 physical reads less but 240 000 more logical reads or vice versa? ?? In this example i have additionally elapsed time 3 time less .
I know that it's not so easy to say b/c it depends on many many thing but if you can say looking only on my results what will you choose???
Regards
February 28, 2010 at 11:14 am
Here's my interpretation of the numbers:
Query 1:
There really seems to be no big difference. Run it with a larger data set to see which query scales better. You could also run each query 10 times and compare the measurements. Maybe you'll find that sometimes a) is faster than b) and vice versa.
Query 2:
Option a) because less physical reads,(wrong statement. see Barry's following posts) less CPU.
Query 3:
Probably option a) because less physical reads,(wrong statement. see Barry's following posts) less CPU.
I would still compare execution plans to see if I could find a reason for the high number of logical reads (e.g. table spools).
Hard to tell without having the query and the execution plan available.
A test with an increased number of rows would also be an option to see which query scales better.
February 28, 2010 at 1:34 pm
Paul White (2/28/2010)
Barry,A very small thing regarding your script: if you are going to issue DROPCLEANBUFFERS, you should always issue CHECKPOINT first. Otherwise, you're just dropping unmodified data from the Buffer Pool. CHECKPOINT flushes dirty pages to disk, so a subsequent DROPCLEANBUFFERS will just about clear the data cache completely.
Yeah, I was futzing over whether to do that or not, as it can have bad side-effects and should not be needed as modified pages shouldn't affect this example at all.
Second point. Logical IO is misleading at the best of times in SQL Server. A much more reliable indicator of efficiency is CPU usage.
See:
Yep, this is a frequent claim in the MS world, and in every case that I have been able to track the facts & details down it turned to be either just flat-out wrong or (more often) hugely misleading. I haven't read your first link yet, But I had read the Joe Chang article back when it came out, and he just completely missed the nuance of what the Oracle presenter was trying to make. This is a very common problem in the MS world because of the extent to which MS goes to try to insulate us from the actual mechanics and dirty details of what happens under the hood vs how that turns into response time. The Oracle and IBM customer base tends to much more knowledgeable better and better informed about "Performance Theory" and its construction/deconstruction principals (not so much in other things though).
IIRC, what Joe Chang is missing, is the nuance that I explained above: for test measurement, relative comparisons and prediction, Logical IO is what you you should use for the IO side (you should never just use CPU or Logical IO as alone either is dangerously misleading). Physical IO should only ever be used in production (and tests or models designed to duplicate production performance) and general as a diagnostic metric.
The general advantage of Logical IO, is that like CPU, it is 90 to 99% source-dependent and repetition invariant. Physical IO is entirely at the whims of circumstance and the anticipation algorithms of the OS.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 28, 2010 at 1:43 pm
fabi150 (2/28/2010)
Guys,Thank you for answers. But i still have to determine which of these costs mentioned above are better. Let's look on Query 1
having similar times what is better?. When i have 22physical reads less but 2000 more logical reads or vice versa??
and please take a look on Query 3 . What is better 232 physical reads less but 240 000 more logical reads or vice versa? ?? In this example i have additionally elapsed time 3 time less .
I know that it's not so easy to say b/c it depends on many many thing but if you can say looking only on my results what will you choose???
Regards
Actually I have already answered your question and very directly: You CANNOT rely on physical IO as a predictor of performance. Period. Physical IO is only good for telling you what you have to look at or try to fix ("tune") in production.
Throw out the Physical IO numbers, they do not mean anything.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 28, 2010 at 1:53 pm
Paul White (2/28/2010)
Second point. Logical IO is misleading at the best of times in SQL Server.
I am sorry if I appear peevish in coming back to this, but there is so much misinformation and confusion around this in the MS world (as the OP's reply just demonstrated) that I feel honor-bound to be clear and direct about this:
Logical IO is the single best measure of a process, application, Server Instance or System's IO pressure, IO needs and IO performance predictor.
And I will take that to the mat with anyone up to and including the likes of Paul Randal, Connor Cunningham, etc (not that I think that they would disagree with this). Physical IO is not reliable as anything other than a diagnostic indicator and guide.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 28, 2010 at 3:46 pm
Oh I see, both articles are by Joe Chang. Well Joe clearly has a great deal of knowledge in low-level detail and HW measurement, however, both articles come the similar incorrect conclusions because of a combination of unstated assumptions, a failure to clearly state what the goals or purpose against which Logical IOs were being evaluated, and a couple of fairly serious gaps in reasoning. In fact these unstated assumptions are so thick and deeply rooted in the entire perceptual and conceptual framework that Joe presents the appearance of, that I could easily spend all day trying to elucidate the subtleties of them all, and I sure don't want to do that.
However, both articles, cover some very different ground and are very revealing and instructive about a lot of other technical information, so it is difficult to treat them together and briefly without some high-level generalizations on my part. So bear with me on that, I will try to fairly summarize his reasoning below and why I think that it is off:
1. People (MS Cost Based Optimizer, Oracle Expert, etc.) are using "Logical IO Count" as a "Performance Metric".
2. But, I can find instances where either:
-- Logical IO may vary, but they do not account for that, and/or
-- Logical IO does not always translate into the same number of Physical IOs
3. Therefore, Logical IOs are a bad performance metric...
4. Therefore, you should abandon Logical IOs as a Performance Metric, and use CPU as it is a better representation of elapsed time.
...
OK, so my wife is calling me to dinner, I should be back to finish in an hour. Meanwhile, if there is anyone actually following this at this hour, then I invite to hone your Logic & Reasoning skills by trying to figure out what thing or things may be unsound in the above line of thought...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 28, 2010 at 9:25 pm
RBarryYoung (2/28/2010)
Paul White (2/28/2010)
...if you are going to issue DROPCLEANBUFFERS, you should always issue CHECKPOINT first...Yeah, I was futzing over whether to do that or not, as it can have bad side-effects and should not be needed as modified pages shouldn't affect this example at all.
CHECKPOINT has bad side-effects? The script already dumps the entire procedure cache and any unmodified pages from the buffer pool! It's tough to see which side-effects might be worse.
Your script has no way to determine how many pages belonging to SomeBigTable are marked dirty at the start of the test. Worst case, all the pages for the table are dirty, and DROPCLEANBUFFERS has no effect on it. In that circumstance, the first run would come entirely from cache and produce no physical reads at all. So, it can't be accurate to say "...modified pages shouldn't affect this example at all".
Paul
February 28, 2010 at 9:43 pm
On the subject of logical reads as a performance metric.
I find Joe's reasoning to be sound - he presents his case lucidly, with plenty of examples and some very thorough testing. His technical analysis of the issue, and in particular the way SQL Server reports logical reads, adds weight to his view. The references I cited are also pretty accessible to a moderately skilled reader, which is always an important factor in communicating a concept.
My experience over the years leads me to broad agreement with Joe's conclusions. I tend to give much more weight to properly-measured worker time than to reads. That is not to say that I ignore the number of reads: at the very least, a higher number of logical reads implies a possibly less efficient plan, and that warrants a deeper look. Of course, a higher number of reads necessarily involves higher worker time too.
My approach is to give a greater weight to CPU usage, but with an eye on reads as well. Naturally, elapsed time is an important consideration too.
My concern with your responses on this thread so far, is that you are long on unsubstantiated criticisms and short on a clearly-reasoned and justified alternative. I am, as always, quite prepared to modify my position or completely change my mind, given a suitably convincing counter-argument. To be clear about that, it is not the sheer length of a response that I find to be persuasive 😉
Paul
February 28, 2010 at 11:35 pm
Paul White (2/28/2010)
I find Joe's reasoning to be sound - he presents his case lucidly, with plenty of examples and some very thorough testing. His technical analysis of the issue, and in particular the way SQL Server reports logical reads, adds weight to his view. The references I cited are also pretty accessible to a moderately skilled reader, which is always an important factor in communicating a concept.
Well, first, this clearly dodges my question, which was, did my outline above fairly represent Joe's reasoning? Or at least, insofar as his reasoning was convincing to you Paul. And to be clear, I never questioned the accuracy of Joe's test's, I just question the reasoning that surrounds all of it and informed those test and internals technical analysis.
My concern with your responses on this thread so far, is that you are long on unsubstantiated criticisms and short on a clearly-reasoned and justified alternative.
!!! ???
Wow, I hardly think that that is either justified or fair Paul. First of all I am waiting for you to answer my question, yea or nay, as to whether or not I have fairly expressed the relevant points and reasoning on two other sites that you simply pointed to as justification, without context or connection or establishing relevance.
Secondly, as far as "clear reasoning" and "substantiation", I think that I have made quite a bit more effort in that regard on my side than you have on yours. Whether or not you agree with me, saying that I am "short" on reasoning or backing my claims up is simply unfair when you just look at the posts here. If you think that some part or point is unclear, then point to it and say so, that's fair. But just acting like I haven't kept up my side of the discussion is not.
Thirdly, I hardly think it fair to accuse me of this when the only substantiation that you have provided for your own "unsubstantiated criticisms", i.e. "Logical IO is misleading at the best of times in SQL Server. A much more reliable indicator of efficiency is CPU usage.", is to point to two opinions by the same guy, who may know his Hardware and how it is used extremely well, but otherwise does not seem to be any more qualified to speak on this subject than you or I. Other than that, you have not provided any justification for your criticism that "Logical IO is misleading at the best of times" other than to say that you like what Joe says. That's fine, but what would be more substantive would be to connect the points and details of those articles to this discussion in some logical way. I am trying to do that, but as I am not referencing them to support my case, I cannot be the one who says how they are supposed to prove your points, I can only lay out what I *think* that you meant.
Worse, though the claim that the we should use CPU instead Logical IO is clearly stated both here, and in Joe's papers, no attempt at justification is made in either place.
Finally, and most surprisingly, I just do not understand where the statement that I am "short" on a "justified alternative" is coming from. I thought I had been pretty clear about this, but just so there is no misunderstanding, Let me lay it out plainly:
1) In order to best characterize the performance profile of a process, task, application function (called a "transaction" in Performance Measurement, not necessarily the same as any particular DB transaction), best practice is to use BOTH:
A) Logical IO to characterize the IO demand, and
B) CPU to characterize the processing demand
As long as I'm on this, let me make it complete, so I don't get accused of trying to end-run anyone later on:
2) To report perceived performance of applications, tasks, processes or other transactions to Users or other consumers of service, it is best practice to use Response Times.
3) To report on or to characterize the Capacity Consumption of System Resources, it is best practice to use the physical components Utilization%, preferably in conjunction with both queuing statistics for these same things and knowledge of and adjustment for their inherent architectural parallelism (ie., multi-cores, RAID 5, Mirroring, etc.). (The simpler, but jargonistic way to express this is "either Util%/device-multi-threading, OR traffic-rate", which are the same thing).
4) To detect or to initially diagnose either potential or imminent process-level performance problems in production environments, it is best practice to start with the process's or transaction's queuing contributions to each of the physical devices. Since MS does not have this available, then second-best practice is to use the transaction's utilizations contributions to the physical devices under load. MS also does not have this available to us either, so third best practice is to use the process/transaction's CPU and Physical IOs to each device (but yep, they do not exactly have that either).
Now, that's a lot of stuff, but I am more than happy to justify, substantiate and reason the heck out of it, because there is over forty years of sound & on-going scientific research into it and just as much successful real-world practice. I have the sense that you half-suspect that I am just making this stuff up or just pulling it out of my arse to win an argument. I promise you that I am not, this really is a professional and scientific specialization (though the PC era's financial inversions has marginalized it quite a bit). The two most prominent groups in this field are, on the research side the PMG (Performance Measurement Group, part of the ACM, I think), and on the practice side, the CMG (the Computer Management Group). So feel free to check out my back-story if you want, ;-).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply