THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Why Logical IO is a poor performance metric

Many years ago, I attended a conference where a very highly respected Oracle performance expert advocated the use of logical IO count as a performance metric. The argument was that a logical IO is a fairly substantial operation relative to others. I suppose that detailed logical IO was already collected supported this. So why would logical IO be advocated over CPU? On the Windows side, many people have probably noticed that Profiler frequently shows 0 CPU for low cost queries.

(The resolution of Profiler in SQL Server 2000 was some approximate integer multiple of 10 ms for the single processor kernel and 15.625 ms for multi-processor kernel. It does turn out that averaging many calls yields a reasonably accurate CPU measure, possibly to the range of 1 msec.)

I am not sure what the situation is for Oracle, which runs on many platforms, UNIX, Windows and other OS. It cannot be easy collecting performance counters whose meaning is uniform across all the different platforms.

 

Anyways, the argument was convincing enough that I thought it warranted investigation on the SQL Server side. By comparing logical IO and average CPU (measured in full saturation load tests where SQL Server is driven to near 100% CPU), it was quickly apparent that Logical IO and true cost had no meaningful relation.

 

Consider a table for which the indexes have depth 4, so that there is a root level, 2 intermediate levels and the leaf level. The index seek for a single index key (that is, no key lookup) would generate 4 logical IO (LIO). Now consider if a key lookup is required. If the table has a clustered index also of depth 4, each key lookup generates 4 LIO. If the table were a heap, each key lookup generates 1 LIO. In actuality, the key lookup to a heap is about 20-30% less expensive than a key lookup to a clustered index, not anywhere close to the 4:1 LIO ratio. Now consider a loop join between two tables. The index seek to outer source generates 4-5 LIO, 4 for the index depth, and possibly one or more IO for additional rows that do not fit in the same leaf level page. Each index seek to the inner source generates 4 LIO as the index is traversed. For a loop join involving 100 rows in a one-to-one join, (no key lookups) there are 404 or so LIO. Now consider a hash or merge join for the same number of rows (for which there is an explicit SARG on each source). There are 4-5 LIO to each table, for a total of 8-10 LIO. In actuality, the cost between the 3 join types at this row count is not far different (the hash join has a higher startup cost than a loop join, but lower cost per incremental row), but the LIO ratio is 404:10!

 

The SQL Server Cost Based Optimizer itself clearly does not use LIO for its cost estimation. Just compare an index seek for tables and indexes with different index depth. The cost is the same, which is approximately true. After assembling a very deep collection of SQL Server performance measurements, I went back to the Oracle expert to ask about the LIO argument. He said that in the intervening time, a huge amount of evidence on the Oracle side also indicated LIO is a poor metric and this had been abandoned.

 

The SQL Server 2005 DMV dm_exec_query_stats keep milli-sec resolution CPU stats, and micro-sec in SQL Server 2008. I strongly suggest people abandon LIO as a performance metric. Use CPU (total_worker_time) for non-parallel execution plans. For parallel plans, the DMV reports 1000. So I suppose one must revert to Profiler traces for parallel execution plans. (If any one know how to get parallel plan CPU from DMV, please advise.)

 

Note also, the other post indicating physical IO has a completely different cost structure than logical IO.

Published Wednesday, September 10, 2008 1:08 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mario A Chavez said:

Joe;

I don't know what was the exactly advise from the Oracle "expert", but reducing LIO is meant that a LIO can cause PIO, not all the time but it's possible, this situation can have or not noticeable CPU usage.

Even if you have all data in DB buffers cache and all your operations are pure LIO, if you have a big LIO number everytime you run your query, this big number will be reflected on how quick your operation can return the data asked and how quick can you leave alone the db buffers latch for other requests.

So the advise to tune LIO on your query is not really meant to reduce your CPU usage, but to reduce the resources - if it's possible - that your queries use and be a good database citizen.

Mario

September 10, 2008 2:19 PM
 

jchang said:

What you are saying is that you use LIO to assess the guilty party, its some resource which may not be related to CPU. What I said is LIO is not a meaningful measure of any guilt: PIO or memory or CPU, which is most of the resources that have meaning.

Consider 2 queries, one doe 10000 key lookups to a clustered index, another does 10000 to a heap. The first generates 40K LIO, the second 10K. Both have the same likelihood of generating PIO, both generate the same memory usage, the second does have 20-30% lower CPU, and yet LIO counting assessed 4X the guilt to the first!

If you are after PIO, then go after PIO not LIO. Even if you are after memory, after fixing the big CPU consumers, its very likely you will have fixed the memory consumers as well.

The main resource to watch is CPU, if a query finishes quickly with minimum CPU, thats being a good citizen, LIO has no bearing. (Duration is a good counter to watch after CPU has been brought down.) There are examples of good citizenship, but again LIO count has no bearing.

September 10, 2008 6:20 PM
 

Uri Dimant said:

Joe

In general,if query used less logical IOs is better and faster than using more,thus tuning query towards lower number of logical IOs in most cases produces query that is faster and uses less resources. But  you are absolutely  right, that reading the same data may end up showing the different results as you showed us above.

In OLTP applications people have trying avoid using a heap so I would say your example is a little bit artificial. I think it depends on what kind of queries you issue on the server whether it is large queries (larger amount of LIO) or queries that executed very very frequently.

September 10, 2008 11:36 PM
 

Michael Zilberstein said:

Uri,

> if query used less logical IOs is better and faster than using more

That's not always the case - depends on IO type (sequential or random) for example. Classical case of the opposite: scan vs seek + lookup. Optimizer usually prefers scan over seek + lookup when > 1-1.5% of the table should be returned. In such a case scan would consume more IO but the overall duration will be lower.

September 11, 2008 2:29 AM
 

jchang said:

Sometimes people just want to be difficult. The situation is: there is a reliable metric (CPU) and an unreliable metric (LIO) available. Why is it people to gravitate to the unreliable metric? In most cases? yes, but if you tune to CPU, it is all cases! So why work with the metric that is not reliable? If query averages 10000 LIO, nobody has any idea what that means, because we do not know the source of the LIO. If CPU is 100ms, I can guarantee you that the upper bound on your system is 10 calls/sec per core and if you have an 8 core, the upper bound 80/sec (more likely 20-30% lower than 80). If you tune a query reducing it 10 LIO, you don't know how much a gain you made. If you tune it down to 10ms, you made 10X gain. If you are doing tuning, it probably means you are changing the execution plan. When you change the execution plan, LIO from one plan has no relation to LIO in a different plan!

If you what another example. Suppose a query involves a loop join for 10000 rows (outer source) and the index depth to the inner source table is 4, This means your query is about 40K LIO. Suppose the inner source table is 30K pages in size (240MB) and you forced a hash join. Then LIO will be around 30K. Did you improve it? by LIO yes, by CPU no! The loop join for 10K rows probably cost around 50 cpu-ms. The hash with scan (mostly from the scan) will cost more.

September 11, 2008 2:49 AM
 

Michael Zilberstein said:

Joe,

What you're talking about is an ideal world. When I come to the client that has severe performance problems, usually I don't have enough time to set Profiler and analyze results afterwards with cup of coffee. If I have to detect most problematic queries instantly, going on logical IO from dm_exec_query_stats is the first choice. When frequently running query shows tens or hundreds thousands logical reads on average, it indicates one of the 2 options: scan on heavy table or extremely heavy seek + lookup. Both should be taken care of. Third option is that it is heavy report "by design" but from my experience this is unlikely option.

By the way, scan + hash join can cost more CPU than seek + lookup + nested loops join but still will be first choice because Optimizer will estimate that this way overall duration will be lower. So measuring by CPU only can be misleading, sometimes more CPU is a better choice.

September 11, 2008 3:47 AM
 

Uri Dimant said:

Misha

I t depends on data access as I pointed in my post (how SQL Server Optimizer counts SEEKs and TABLE Scans)

Joe

>If you are doing tuning, it probably means you are changing the >execution plan. When you change the execution plan, LIO from one >plan has no relation to LIO in a different plan!

Yes ,before I add/remove the index (for example)I think how it can be affected in terms of readig data/index pages.In most cases (again)if query made less LIOs it producess better.

September 11, 2008 5:23 AM
 

Alexander Kuznetsov said:

Joe,

Clearly CPU usage strongly depends on isolation level:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/07/12/cpu-overhead-for-higher-isolation-levels.aspx

Have you researched if the optimizer's choice depends on isolation level?

September 11, 2008 9:53 AM
 

jchang said:

what idea world! if you can go to sys.dm_exec_query_stats for LIO, why is it so hard to aggregate total_worker_time??? So why the stubborn persistent clinging to the less reliable indicator?

I will talk about hash vs loop join in a later post. Per my earlier post, the 1-1.5% idea is another peeve. The SQL optimizer does not use an x% rule, so we should not tell people that it does. The simplified version of the rule that the optimizer uses is: each incremental sequential IO page cost 0.00074074 sec or 1350/sec which works out to 10.5MB/sec. Each "random" IO in a key lookup costs 0.003125 sec or 320 IOPS, with a method for estimating how many IOs are required per row. For a large number of rows, its usually 90+%, so essentially the index seek + key lookup versus scan decision is made based on the 1350 : 320 ratio (4.2:1), which the rate of scan pages per sec versus key lookups per sec.

September 11, 2008 10:02 AM
 

jchang said:

alex, for some reason i thought it was linchi's post that talked about this. Very good post anyways. Several years ago, I published performance versus lock level, NOLOCK, default, PAGLOCK and TABLOCK. My NOLOCK to default results are more or less in the range of your dirty versus RC. As far i know, the optimizer does not consider isolation level. The purpose of the optimizer is to determine the best table access strategy. It is not meant to reflect other costs that do not affect the index/scan/join order choice.

Back to your post. What I found was lock level assesses a specific cost per row, irrespective of the operation. In your query, the plan is a clustered index (range) seek?, which has relatively low cost per additional row, hence the lock/isolation level has a large impact. in a more expensive operation, key lookup, loop join, the lock level has slightly less relative impact (but the time differentials will be about the same)

September 11, 2008 10:29 AM
 

Michael Zilberstein said:

> if you can go to sys.dm_exec_query_stats for LIO, why is it so hard to aggregate total_worker_time?

Because poorly written heavy queries tend to run in parallel! So total_worker_time is usable only on single-core paleolithic artifacts and on instances where for some reason maxdop is limited to one on instance or query level.

September 11, 2008 11:06 AM
 

jchang said:

ok, i missed that you mentioned it in your post. so we are stuck as far a dm_exec_query_stats goes. I am ok with using high LIO or elapsed time with low cpu to identify parallel plans. In SQL 2008, I noticed that several parallel plans reported 1000 for worker time. Not sure we can rely on this. I do not think you should ignore worker time. Use high LIO or duration together with low worker time to identify parallel plans, but use worker time to rank non-parallel plans. it should not be ignored

September 11, 2008 11:54 AM
 

Adam Machanic said:

No reason to play guessing games to identify parallel plans...

select *

from sys.dm_exec_query_stats c

cross apply sys.dm_exec_query_plan(c.plan_handle) p

where

p.query_plan.exist

(

'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";

/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Parallelism"]'

) = 1

September 11, 2008 12:55 PM
 

jchang said:

my concern is that this is not cheap query to run (as is) if there are very many query plans, i have seen > 100K

perhaps a nested query first finding the very high LIO as Michael proposed, but with unusually low cpu, then just check those,

in SQL 2008, worker time = 1000 might indicate a parallel plan suspect, I will try a couple of queries, unless Adam gets to it first

September 11, 2008 1:33 PM
 

Adam Machanic said:

I don't have access at the moment to a SQL 2008 instance that can do parallel queries, and I don't want to reconfigure my VMs to fake it since I'm doing some work.  But I'm looking at a 2005 instance and worker times are all over the place for the plans returned by that query.

By the way, you're certainly correct that it's not a cheap query; the instance I ran it on has over 67,000 plans in the cache at the moment, and I accidentally left the query running and walked away for a while.  I came back 42 minutes later and found the thing still spinning, having returned only 54 rows... Granted, this is a somewhat underpowered server, but still.  Use with caution!

September 11, 2008 1:42 PM
 

Anon said:

What rubbish... Considering how cheap CPUs are...

September 15, 2008 10:07 PM
 

jchang said:

The fact the performance topics are immensely popular at conferences, in papers, and blogs indicates CPU power is not cheap enough to consider performance analysis rubbish

It is really great how powerful systems are today, a small app really does not need to worry about performance. Good design is still important if you want correct functionality and coding transparency. If you do not want to worry about CPU, small apps is a good field to practice.

September 16, 2008 5:21 PM
 

Adam Machanic said:

Sure, "Anon", CPUs are plenty cheap and solve all of our problems automatically.  Tell that to my recent customer that dropped half a million dollars into a gigantic server and discovered that its application still wouldn't perform well even running with all of that hardware.  How do you think that I fixed the problem?  Simple: I used all of this rubbish and analyzed why the performance was so bad.

September 16, 2008 10:07 PM
 

DonRWatters said:

Intersting.  At the last Pacific Northwest SQL Server Users Group, Kalen talked about reviewing Logical I/O of long running queries as a measure for determining if using an Index Seek or just doing a Table Scan was better for overall performance.  She showed cases where a Table Scan was better than an Index Seek, and the only way to see it was by looking at Logical I/O.  By cutting the Logical I/O by using a Table Scan, the performance was better.  I'm sure you're not advocating NOT looking at Logical I/O anymore, but just in case someone misses the message, it still HAS to be considered as part of the performance equation...just not the whole thing.

September 24, 2008 8:09 PM
 

jchang said:

How can logical IO must be any part of the performance equation if no one, NO ONE, can say what it means? Consider the pre-euro days, you are traveling throughout europe, every time you buy something, you increment a counter for each of the local currency you spent. 5L in england, 9 francs in france, 8 marks in Germany, 1000 lire in Italy. At the end of your trip, what does your counter tell you? how meaningful is it? unless you know the actual execution plan and rows that made up your LIO, you don't know what it means? And if you did have the components, you don't need LIO. If LIO was so meaningful, why doesn't the SQL Optimizer use it? it doesn't, because it has no real consistent quantitative value.

On index seek + key lookup versus table scan, for better performance, you need to specify CPU or duration. In any case LIO doesn't tell you anything. If you are talking about when the execution plan switches from IS+KL to Scan, it occurs around 1350:320 ratio (4.2 to 1, or 10.5MB/sec to 320 IOPS) used by the optimizer. If your table data size is 8GB, ie, 1M leaf level pages, then around 250K or so rows, the execution plan changes from IS+KL to Scan. If your clustered index key depth just happens to be 4, then the switch just happens to ocurr around the same LIO count(order of magnitude). But if the key depth was 3 or 5, then you are way off. See my other posts on the formulas used by the SQL optimizer. If you are talking about CPU, with all data in memory, then 1 row in a key lookup might cost about the same as 1 page of a table scan depending on the rows per page, which is drasticly different from what the optimizer model. If you are going to disk, and looking at duration, then it all depends on your actual random IOP to sequential transfer rate is. For a single 15K SAS drive, assuming tightly packed data, you might get 400-800 IOPS (note this is not a true random IOP) in the key lookup (with scatter gather activated) or you could get 80-120MB/sec in the table scan, depending exactly how your data is laid out and how the SQL is written. Again this is far different than the optimizer model in the other direction than in-memory.

Yes I know many people have written about LIO as if it were a key system resource, but know one could actually say what. The truth is it is sued because it is easy to collect, which does not say it is useful. Like people who cling to guns and religion, the scientific dba needs to let go of LIO, and the db buggyman is not lurking in the closet at night.

September 25, 2008 6:15 PM
 

Mario said:

You start your story with: 'many years ago..', that is very important. What Cary Millsap (which I think you are refering to) tried to say back then was that there was a huge focus on the 'buffer cache hit ratio' as the magic performance metric. And that LIO was good and PIO was bad. That LIO were 10000x cheaper than PIO etc. He tried to put things in perspective, and showed that LIO's don't come free, especially if you are overdoing it (poor queries or database design). ANd that you have to reduce PIO if that is what you determine is a bottleneck after measuring. What he definitely didn't do, as you say in your second or third sentence, is stating that LIO is a more important metric than CPU.

LIO==CPU, and that his point was try to reduce LIO so you reduce CPU utilisation.

There are multiple problems with LIO as a metric, one of them is that nobody know what a LIO is, is it a block access? Is it a row access? ANd there a many types of LIOs (as I saw Linchi showing in his blog recently), that can't be distinguished in SQLServer (as you can in Oracle, btw).

Excessive LIOs can also result in excessive latch contention and spinning which also add to cpu utilization. In short LIO and CPU go hand in hand, an it's very likely that of you reduce LIOs you reduce CPU utilization. ANd the other way around, if you fix a query and reduce CPU utilisation, you most likely reduced LIO.

The fact that the optimizer doesn't use LIO as a metric says something about the (simplicity of) the optimizer not about LIO as a metric I think. OPtimizer can hardly predict CPU utilisation, they might be able to predict LIO though based on table stats.

ANyway, I still don't see why LIO si a poor MEtric. It's as good as PIO and CPU utilization.

It depends on what you're after..

November 21, 2008 5:53 PM
 

jchang said:

Mario: outstanding!, I will now say that many years ago was 1999 or 2000, my own memory is seriously degraded. That was before the extensive use of waitstats. When I caught with the Oracle person in 2004, he was heavy into wait stats,

Still, the point is LIO is not a consistent measure of CPU, nor is their any internal limited resource governed by LIO. Now I have made my complaints about the SQL optimizer cost formulas. (MS has made their point that they can not fix past errors because so many people now depend plans generated from the existing silly model.) The SQL Server Cost Based Optimizer since version 7.0 is based on duration, not cpu, and not LIO. The duration is based on (P)IO time and CPU time. The CBO is not trying to predict CPU usage, but does use a model of CPU usage. Yes, LIO count can be much more accurately predicted than true CPU, but clearly MS (and probably Oracle as well) felt that LIO was worthless as an optimization strategy, or they would have used it.

If you catch up with, and have 5-6 hours, I will explain in detail why it is worthless.

November 23, 2008 11:51 AM
 

Mario said:

You are right: LIO is not a consistent measure of CPU. But I would like to use it the other way around: If a query uses a huge amount of cpu, then it's very likely it's doing a huge amount of LIOs. And that can be something to address (or to accept).

How else would you try to reduce a query's CPU time, other than reducing the LIO involved?

About what is right or wrong for optimizers: I don't know exactly the state of the art right now for the Oracle optimizer. The last time I looked at Oracle10g, I do know that it could collect 'system statistics' to feed the optimizer. It collects CPU speed, index and tablescan access speeds to tables. But it's always extremely hard (impossible I think) for an optimizer to predict concurrency.

I think LIO is meaningful if to try to compare one query to the same using a different access path or optimizer strategy. I think it's dangerous to use it as a metric for 'good performance' or even to compare workloads. Simply because not all LIOs are equal.

I have 5-6 hours, it's Thanksgiving weekend after all, so go ahead and explain why LIO is a worthless metric :-)

November 24, 2008 1:56 PM
 

jchang said:

ok, so long as you understand that the primary objective is to reduce query CPU. (Ocassionally, network transmission or disk io might be more important, but let set that aside for the moment). First determine whether the query time is in the compile/recompile, or the actual execution. Assess whether the cpu is in the table/row access, or logical. For example, SELECT SUM(col1), SUM(col2), SUM(col3) FROM X is more expensive than just SUM(col1) FROM X (assuming this difference does not change the plan, indexes etc). Its not just the SELECT logic, but any join or where clause logic. The optimizer does not consider the difference if this does not affect the plan, but it can be substantial. Consider the number of rows and pages accessed and whether this is the most efficient possible. If your query is WHERE Col1 = A and Col3 = B and your index is leads Col1, Col2, Col3, the plan may have an index seek, buts it not the most effective. So keeping the rows involved close to min helps. Next, of the rows involved are they colocated? or spread out? There are "experts" out there who talk about leading an index with the most selective column, which is not entirely true. In a join, it is more important to keep the rows accessed together stored together. Even better would be if they were in the correct sorted order.

Anyways, I meant if you were in the same area.

November 28, 2008 6:02 PM
 

Gokul said:

I disagree with the author. LIO is the only reasonable measure for PIO, if the entire database doesn't fit into Buffer Cache. We can't go after PIO, because while doing query tuning, mostly the query would get executed again and again and PIO would probably be zero. Moreover if the CPU time is more for a query, increasing the CPUs might improve the performance. The same analogy won't get apply to disks. So reducing the workload on disks is essential in my opinion.

May 10, 2009 12:34 PM
 

jchang said:

About 15-20 years, there were "experts" that said tuning database performance meant reducing PIO. When buffer memory got really big, it was found there was alot more to tuning for DB performance. Good to know there are still old timers out there!

June 4, 2009 10:50 AM
 

Randy In Marin said:

The CPU metric does not tell you how well the query will scale.  

I like the logical IO metric because it helps detect gross violations of good scaling behavior.  Comparing it to the size of the tables involved can be telling.  For example, recently one of our developers created a procedure that ran in less than a second and seemed to work fine.  However, the logical IO indicated GB+ of IO.  It might seem fine on a dev server where they are probably the only user.  In production where there are 100s of users and several databases on a heavily loaded server, it would not be good.  It would take closer to a minute to execute because the server was not dedicated to one person.  The execution also impacted the performance of the other systems.  The procedure was updated to read a few KB of logical IO, doing the same job.  

Yes, it was also faster by a fraction of a second on the dev server - most of the second in fact.  However, it makes a bigger impact to the developer to tell them they are reading a GB when a KB will do.  Do you want all your clients to be reading a GB each call or a KB each call?  Do you want the developers of the other systems on the same prod server to add procedures that read GBs when KBs will do?  Do you want 100s of users to be calling this thing?

It would be nice to have one metric that rules them all.  If I have to choose one, CPU is not it because it's misleading during development.  Disk IO is our bottle neck, not CPU.  This might not have been the case if the logical count was checked for each query during development.  

There is also the extra locking required, the effect on size of the transaction, and the effect on the transaction log.  The more logical IO, the more chance for blocking.  The more I think about it, the less important CPU is to me.  

June 4, 2009 4:40 PM
 

jchang said:

I think you are kidding yourself if you think LIO indicates good scaling. If PIO is your issue, you need to work on that, not LIO. You are also confusing duration with CPU. Why do you think a query that runs in less than 1 sec is ok for a situation of 100's of users. Why did you not look at the CPU? How can you say CPU is not important if you did not look at CPU?

Suppose you have a server with 10 CPUs. If you need to support 10,000 statements per sec, then you know absolutely that the average CPU needs to be less than 1 CPU-ms (not 1 milli-sec duration) or your model will not work.

You cannot say that this same system is limited to 2M LIO per sec, because an LIO in a key lookup to a clustered index is different than a key lookup to a heap or other SQL operation.

So why do you persist in clinging to an inconsistent metric?

June 5, 2009 12:34 PM
 

plahoud said:

> So why do you persist in clinging to an inconsistent metric?

There are so many reasons that spring to mind I'm finding it difficult to generate a concise response, but my immediate thoughts are because it's simple and it works.  It's consistent enough to produce good results in most cases.  Of course there are some fringe cases where higher LIO doesn't correlate to more work, but I would say this is the exception, not the rule.

CPU may not be cheap, but it's certainly cheaper than disk.  Most of the real world systems I've come across bottleneck on disk before they get anywhere near bottlenecking on CPU.  In the comments above I see the answer to this argument is that you should be troubleshooting PIO rather than LIO, but PIO is not deterministic.  In the absence of context (as most tuning exercises are), reducing LIO is the first step to reducing PIO, and I would say is successful 9 out of 10 times.

Of course anyone who has done extensive performance tuning will tell you that any metric you look at doesn't exist in a vacuum, everything is related and interdependent.  While you shouldn't rely on LIO alone, neither can you rely on CPU alone.  If higher LIO doesn't necessarily mean higher CPU, than lower CPU doesn't necessarily mean lower LIO, so if the buffer pool is your bottleneck, reducing CPU is not necessarily going to improve performance.

Clearly you have shown that LIO is not reliable as a metric in and of itself, but this does not immediately eliminate it's usefulness in tuning exercises.  Until the act of reading data is so fast that its expense is trivial compared to processing time, the database tuner's best course of action is read less data.  "Read less data" is an approximation of "do less work."  Of course it's not always the case, but it's true enough of the time to be useful and is a lot easier for a human to get his or her head around than "use fewer CPU cycles" which is non-deterministic.  We use approximations to solve mathematical problems all the time, what's the problem doing it with database tuning as well?  Sometimes "good enough" is good enough.

October 7, 2009 2:13 AM
 

Daniel, Wu said:

I comes from oracle background and learning sql server now.

LIO roughly can be expressed by cpu, such as one LIO is about 40us (depending on cpu speed). But the cpu time used might not be dominated by LIO, such as hash join, when we do a hash join, after the data read in (LIO is done), there are still much work to do such as hash a row, etc which will also consume cpu. So LIO doesn't equal to CPU, but in most cases, LIO is a good indication of how much cpu will be used.

The most important fact to use LIO is: LIO will use latch which will prevent the scalability of the system if consider the concurrency.

Normally a LIO will 40us in our system, but when many nested loop run at the same time, we will see lots of contention because of many sessions wants to read the same blocks again and again, when contention happens, it will use more cpu as oracle will spin to get the latch.

In your test case, nested loop is faster than hash join. But if run 100 sessions at the same time, things might be different.

August 13, 2010 2:15 AM
 

jchang said:

40us for LIO corresponds to 25,000 LIO per sec! I am thinking this was true for the Pentium III 500MHz. Some of my old papers may have PIII 600MHz data. The current Core II 3GHz should be able to 250K key lookups per sec, or 4us for a RIAD LIO.

For basic operations like index seek and key lookup, there should be very little difference in performance between Oracle and SQL Server.

There is difference to consider. In SQL Server, a key lookup to a heap organized table generates 1 LIO. A key lookup to a clustered index (Index Organized Table?) generates multiple LIO depending on the B-tree depth. In Oracle, IOT still has file-page pointers(?) so it could still do it in 1 LIO(?)

August 30, 2010 11:19 AM
 

Al Mason said:

Logical I/O is the true measurement of through put. CPU can easily be pegged with poorly optimized code. Logical I/O measures how much data is being processed at any moment (indexing and SQL being equal). Locking reads on SQL Server will prevent logical IO from occuring. Oracle does not lock data for reads nor does it allow dirty reads. This feature allows Oracle to process higher volumes of data on highly active OLTP systems thus producing higher metrics in Logical I/O. The reason SQL Server doesn't want to boast about it's logical I/O is that it is a weakness compared to Oracle.

This is not an opinion. It is a fact.

Opinion: SQL Server is a Pseudo (poser) RDBMS engine

almasondba@gmail.com

June 29, 2011 9:47 AM
 

Alberto said:

LIO = PIO when there is no buffering.

PIO is measured in milliseconds, so targeting LIO and indirectly PIO is a must.

July 19, 2011 3:42 PM
 

Jay Wallters said:

Many years ago the Oracle metric was a query that uses more than 15 LIO/row was probably poorly written.  As a Dba/Performance tuning consultant with Oracle i was able to make many customers happy just by using this simple rule.

November 5, 2012 1:22 PM
 

jchang said:

For a non-excessively large database, a reasonable index b-tree depth is 3-4, so the 15 LIO per row rule in essense says good indexes and joining no more than 4-5 tables in your query, which seems to be a very reasonable rule. Of course, in the old days, it was very important to have a pure transaction processing database. Today there is no such thing. If queries are a mix of index seeks, loop joins, hash joing and table scans, then LIO has no meaning between the different SQL operations. Which seems to be a concept that noone can understand.

November 5, 2012 9:21 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement