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.

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.

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.

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)

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

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

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.

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.