Automating dm_exec_query_stats and dm_db_index_usage_stats analysis

jchang — Mon, 22 Jun 2009 04:25:00 GMT

Everyone should know by now how really useful the DMVs dm_exec_query_stats, and dm_db_index_usage_stats and the associated DMFs for obtaining the SQL and XML plan: dm_exec_sql_text, dm_exec_query_plan, and dm_exec_text_query_plan. Of course it has been...(read more)

Why Logical IO is a poor performance metric

jchang — Wed, 10 Sep 2008 16:08:00 GMT

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.

Joe Chang : Query Tuning, DMV, Performance

Automating dm_exec_query_stats and dm_db_index_usage_stats analysis

Why Logical IO is a poor performance metric