Joe Chang

Dell PERC6 RAID Controller Performance

jchang — Wed, 01 Oct 2008 12:36:00 GMT

Last month, Scott pointed out the really bad performance characteristics of the Dell PERC6 in RAID0 sequential write, particularly compared with RAID-5. Granted, this is not necessarily a red flag because few people use RAID 0 in production. Still, if one can't write code or test correctly, one should not be in the hardware/firmware business. Dell recently released firmware 6.1.1, the previous was 6.0.3. The driver was updated from 2.20 to 2.23.

My results for 2 x 4 disk RAID0 arrays on PERC6, writing to another PERC6 with same disks.

Large block Read test: 690-713 MB/sec

SQL Server backup test

Old 6.0.3 firmware: 81MB/sec Write-Back 129MB/sec Write-Thru

New 6.1.1 firmware: 354MB/s Write-Back 631MB/sec Write-Thru

The old driver with new firmware has the same results as new driver with new firmware, so this was really a firmware issue. The old PERC5 did not have as bad RAID 0 write performance as the PERC6 with old firmware. Random write IO testing shows mixed results between write-thru and write back, some favoring WT, some WB but not by a large amount.

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.

Brains still matter - silly SSD notions

jchang — Mon, 08 Sep 2008 19:32:00 GMT

Everytime a good product comes out, people seem to contrive bad ideas for what can be replaced, usually brains, or people with brains, or that people lacking brains can accomplish something on their own.

Let me put it simply. Suppose one had a query that uses a nonclustered index, and required a key lookup, and the key lookup required a disk access.

Key lookup, disk access, 10 HDD: 2000-3000 lookups per sec (4K at high Q depth)

Key lookup, in memory: 200,000 lookups per second per core (2.66GHz Core2)

Key lookup, disk access, SSD array: 40,000 lookups per sec per core (same CPU)

Covered/Included index, in memory: 1-2M rows per sec.

It takes brains to build SQL and indexes to achieve the smallest practical memory footprint, and one that allows the most efficient CPU operations, meaning: 1) minimal avoidable key lookups, 2) enabling merge or hash joins when appropriate over loop joins.

The SQL Server internal cost based optimizer also makes the assumption that disk IO costs are 0.000740741 (seconds) per incremental page in a table scan (1350 pages/sec) and 0.003215 per key lookup (pseudo random 8K) for 320 per sec, about a 4.2 to 1 ratio in scan versus lookup. These numbers already do not reflect current generation disk drives (90-120MB/sec, 200-300 random IOPS). Suppose the new Intel SSD can do 160MB/sec irrespective of whether the access is sequential or small block random. Will MS change the CBO to a 1:1 ratio? And will it do so correctly depending on the whether the data file is on HDD or SSD? If not, then even if the SSD is very fast, SQL is using the wrong plan!

Note: In a poorly designed database/index set, hash and merge joins occur with a scan on one or more sources, and if the scan is big, the query is will be very expensive. So brain deficient people associate hash and merge joins with expensive. If one had brains, one would realize what was expensive was the big scan. The reason for the hash or merge operation is that they are more efficient join mechanisms on a per row basis than a loop join, except for low row counts.