Dell recently published a TPC-H report for the PowerEdge T610, 2 x Xeon 5570, with 4 FusionIO 80GB SSD storage devices at 100GB scale factor. So why have we not seen TPC-C or TPC-E OLTP benchmark results published?

Now it is much more feasible to run the TPC-H data warehouse benchmark on SSD because the Scale Factor 100 size is still allowed, for which the Line item table is 100GB for data only, not indexes or other tables. The full SF 100 tpch database is about 170GB for all tables and indexes. Additional space is required for tempdb.

The TPC-C and TPC-E benchmarks require the database size to be scaled with performance target ranges. Consider the Fujitsu TPC-E published result for the Primergy RX300 S5 with 2 Xeon 5570. The dual-socket Xeon 5570 system scored 800 tps-E, for which the required initial database size is about 3TB. The space actually allocated for the data files is approximately 4.5TB, plus another 85GB for log space.

For the 360 15K disk drives, based on 200 IOPS per disk, the small block random IOPS capability of this storage system is 72K, excluding RAID 10 overhead. If the actual load is 10,000 IOPS (at the operating system) with a 50/50 read/write mix, then the raw IOPS to disk is 5K reads and 2x5K writes for a total of 15K IOPS to disk. So a 75K IOPS system can actually handle 50K IOPS at 50/50 read/write mix in RAID 10.

If we consider that the active database resides on only 15% of the disk space (3TB of 18TB after RAID 10 overhead), then there is some benefit from the short-stroke effect. If the average disk queue depth per disk were higher than 1, then command queuing capability would result in even high IOPS per disk. The actual IOPS per disk might be anywhere from 200-300 depending on whether the emphasis was on pure performance or balanced price/performance.

Below is a proposed SSD (+ HDD for archival space) configuration.

In additional to the above, we need disk enclosures. Ideally I would like to place no more than 4-5 SSD devices on each x4 SAS port. A x4 3Gbps SAS port can support 1GB/s, but if a HBA/RAID controller with 2 x4 SAS ports is plugged into a x8 PCI-E gen 1 slot, we can only expect 1.6GB/s total (single direction) throughput. The Intel X25 SSDs are rated at 250MB/s sequential read, 170MB/s write for the E, and 70MB/s write for the M (all sequential).

The X25-E random 4K IO characteristics are 35K IOPS read, 3.3K write. In the absence of data, let assume the 8K random read is 15.7K IOPS (probably higher), so under 8K random IO, the bandwidth requirement is only 140MB/s or much less for read/write mixes.

The data sheet also say 8K 2:1 R/W 7K IOPS for the X25-E. No random IO data is listed for the X25-M in the datasheet, so it is not clear the X25-M can meet the TPC-C/E random IO requirements for mixed R/W.

A 1U enclosure with 2 SAS ports (daisy chained enclosures not expected) and 8-10 2.5in bays seem appropriate. The 2U enclosures with 24 bays should have 6 independent SAS ports. The 20 or so 3.5in SATA drives for the 60-day space requirement could be accommodated between the internal bays and 1 or 2 external enclosures. The next generation systems and components should be PCI-E gen 2 (5Gbps per lane) and  6Gbps SAS, but we expect higher SSD bandwidths as well.

So the SSD cost structure does seem to support the TPC-E benchmark. It would probably also support the TPC-C benchmark as well, based on the HP DL370G6 result for a

The main issue above is that I have not included RAID overhead. It is my m opinion that the SSD is not fundamentally a single component device, like a disk drive with a single motor. If the SSD were built with dual controllers, and chip-kill ECC on the NAND, then the SSD would be inherently single component failure tolerant. Of course, this is not the case yet. I am just looking forward to when we can do without RAID in SSD. I am not convinced RAID controllers are going to be able to keep up with an SSD arrays anyways.

Since the IOPS capability of the X25E with SLC NAND (not sure for the X25M with MLC NAND), RAID 5 with the higher small block random write overhead is not an issue. So 190 of 32GB or 77 of the 80GB SSDs in RAID 5 would be required.

I should briefly touch on expected performance benefits of SSD over HDD. The Dell TPC-H result did seem to indicate some benefits from SSD, even though there was not a otherwise similar HDD result to compare with. The TPC-H data warehouse queries may generate many table scans for which HDD is fine, there are still loop joins and key lookups, which generate pseudo-random IO. Several TPC-H queries also dump intermediate results to tempdb.

I am expecting TPC-C and E to show reasonable benefits from SSD over HDD. Consider the main TPC-C new order transaction. A typical TPC-C published result might show an average response time 0.3-0.4sec. This procedures processing an order for upto 15 items (average of 10?) which means one update of the Stock table for each item, one insert in to the Order Line table, and one insert to the New Order table, plus a few a others. Since the TPC-C database is very large, each of the above steps might require a disk IO. On a perfectly configured disk system (for OLTP), the average latency could be as low as 5ms even when the entire system drives 200K IOPS.

Still, if you look at the New Order procedure, it is clear each item must be processed serially. The SQL Server engine might use the Scatter-Gather IO API to consolidate IO calls from multiple concurrent users, but in each step in the new order is issued sequentially, after the previous step completes. Since there are over 20 steps, if each step take 5ms, then we can see why the average duration is well over 100ms.

With SSD, the IO latency should drop to 0.08 milli-sec (80us), meaning 20 steps should in the range of 2ms. Because there are fewer transactions "in-flight" at any given point in time, the expectation is that the SQL Server engine has less to keep track of.

Consider a system supporting 600,000 tpm-C. Thats 10,000 new order transactions per second. If each new order procedure averages 0.3sec, then there are 3,300 new order transactions in-flight at any point in time (plus others).

TPC-C also has performance/size scaling requirements. A 600K transactions per minute result requires approx 50,000 warehouses, each of which requires approx 84MB, for a database size of 4.2TB. The recent 600K tpm-C results required 1000+ disk drives (no RAID requirement) meaning the IOPS load is probably 200-300K, possibly a R/W mix close to 50/50.

Since Wes say the FusionIO 640GB devices are out, lets consider what kind of system would be required. The FusionIO is built with a PCI-E interface, that is, it plugs into the PCI-E slot directly, so it probably comes with its own driver. The second generation FusionIO matches up nicely with either PCI-E gen 1 x8 or PCI-E gen 2 x4 in terms of bandwidth.

For 4TB we need 7-8 of the 640GB drives. So, ideally a system should be configured with 9 PCI-E gen 2 x4 slots, plus with embedded devices (the extra slot or two is for additional network or SATA drives). The new Intel 5500 IOH has 36 PCI-E gen 2 lanes, plus the x4 gen 1 off the ESI. So a single IOH would support 9 x4 slots, plus GE and SAS off the ESI. The HP ML/DL370G6 actually uses 2 IOHs for a mix of x16, x8 and x4 slots.

Per Grumpy below, at this point in time, SSD devices have very different characteristics, particulary with regard to writes. Writes to NAND need to be in large blocks. Depending on how the SSD controller is implemented, expect some issues. So it may not be time yet to deploy transaction processing to SSD. DW might be worth considering. Still, we should see OLTP benchmarks plus accompaning details to better understand SSD characteristics. Where are Bashful, Doc, Dopey, Happy, Sleepy and Sneezy DBAs?

For several months, we have seen ads for the joint HP/Oracle RAC and Exadata storage combination talking about extreme performance (10X faster) for large data warehouses. One thing I like about Oracle is that they have courage to pursue technology with deep hardware design implications, even if it takes several iterations to iron out the major issues. I just got around to looking through the Oracle papers on this. Like OPS/RAC, the Exadata technology has implications for how hardware is built. Hardware vendors can be squeamish on designing silicon for special requirements if there is not a viable installed base. This leads to the chicken and egg, which comes first situation that many other vendors cannot successfully initiate. Oracle dares to do this, and amazingly get customers to shell out big chunks of money on the first iteration. This in turn provides justification for gutless risk averse hardware vendors to do their part.

I will start by saying that the SAN systems out there today are designed for transaction processing, not data warehousing. Most SAN systems are designed with a certain number of FC ports, with the intent of supporting 1-4 disk enclosures (typically 15 disk drives each) per FC port. The SAN controller (or service processor) is a significant portion of the overall cost. Configuring one enclosure per FC port leads to a higher amortized cost per disk than having multiple enclosures per FC port, but sequential bandwidth is still limited by the number of FC ports. Depending on the architecture, it is possible to sustain between 330MB/sec (loop architecture) and 390MB/sec (star architecture) per 4Gbit/sec FC port. So it can require 3 FC ports and 45 disks to support 1GB/sec, even though each individual 15K disk drive can sustain 125-160MB/sec. The amortized cost of a SAN system might be $2,000 per disk, so each 1GB/sec through-put costs around $90K.

This is why I have advocated direct attach storage for data warehouses, where each 15 disk enclosure can sustain 800-1000MB/sec at an amortized cost of about $500 per disk. But most people do not like inexpensive high-performance storage solutions. And none of the expensive SAN systems provide sufficient bandwidth for really high-end data warehouse systems.

In the Exadata system, the interconnect between host and storage is InfiniBand, which signals at 5Gbit/s using a x4 wide connector (like SAS) for a net bandwidth (after 8B/10B encoding) of 16Gbit/s or 2GB/sec. The Exadata Storage Server (or cell) is an HP DL180G5 with 2 Xeon E5430 quad-core 2.66GHz processors, 8GB memory, a P400 RAID controller, 12 450GB 15K SAS or 1TB 7200 RPM SATA disks and dual-port Infiniband HCA. Curiously, of the 5.4TB raw storage with 12 450GB drives, only 1.5TB is available. With RAID 10 overhead, there is 2.7TB. Some space is required for internal use, but 1.2TB seems to be rather large.

A complete pre-configured HP/Oracle Database Machine full rack comprises 8 HP DL360 servers with two Xeon E5430 quad-core processors, and 32GB memory for running Oracle RAC, 14 Exadata storage cells, 4 InfiniBand switches (and 1 Gigabit Ethernet switch for auxiliary communications). A half-rack has half of the above components. Each storage cell is listed as supporting 1,000MB/sec with SAS drives and 750MB/sec with SATA drives. The listed bandwidth for the pre-configured full-rack is 14GB/sec. It is stated that Exadata bandwidth scales linearly with the number of racks, but without explicit performance numbers.

Compare the Exadata cell with an EMC CLARiiON CX4-960 mid-range SAN. The CX4-960 comprises 2 SPs, each with two quad-core processors, 16GB memory per SP, for which the minimum meaningful configuration is 16 disk enclosures (240 disks) over 16 FC ports. So the resource allocation per SP is 2 quad-core processors, 16GB memory and 120 disk, with probable sequential bandwidth of 3GB/sec (380MB/sec per FC port). The Exadata cell provides approximately the same compute power, 8GB memory, for 12 disks targeting 1GB/sec sequential bandwidth.

The purpose of the massive computer power per disk in the Exadata cell relative to a standard SAN is to offload compute functions from the main database engine. Concentrating capability, be it compute power or IO bandwidth, in a single system is always difficult, so distributing work can be useful if it can be done effectively. One candidate is compression. (SQL Server 2008 can store tables and indexes with row or page level compression, which take CPU resources. Oracle probably has comparable capability as well.) [Exadata is for Oracle systems only?] Offloading this to the storage element might be desirable. Finally, the Exadata cell is not just a storage engine, but can also handle database protocols.

In addition to a command such as fetch this block, and decompress, the Exadata can also handle SELECT * FROM Table WHERE col = ‘SARG’ (Smart Scan Offload Processing). In a data warehouse, that expectation is that queries are ad-hoc, for which indexes have not been built. So a data warehouse must be able to power through very large table scans. This requires both IO bandwidth and CPU resources, as a database table scan is not a simple IO operation (see my other posts on this matter).

The very recent TPC-H report for the HP BladeSystem of June 3, 2009 uses the Oracle Exadata Storage Servers (more on this below) and has price information. The full cost for 6 Exadata storage cells and supporting components is $536,516. (Of this, each Exadata Storage Server is $24,000, and the cost of the Exadata software is $360,000. Interesting, the price of a similarly configure DL180G5 is $14,000.) Three-year support is another $479,846 for a total 3-year cost of approximately $1M.  It is unclear how much of the discount applies to the Exadata versus the Oracle database software. The amortized list price per cell is $166K or 14K per disk. That Oracle sells this clearly sends the message that people do not look at cheap hardware for the data center.

Now when HP published a 1000GB TPC-H report for a HP Superdome with 32 Itanium 2 9140 sockets, and 64-cores on April 29, 2009, I was very puzzled. What was the purpose of this publication? HP had already published a 10TB result on a Superdome with 64 sockets (128 cores) of the same Itanium 2 9140 processors back in March 2008 with no real disparity (acknowledging that results at different sizes are not directly comparable). Then about one month later, HP published the 64-node Oracle RAC with Exadata storage result of June 2009 (note that the RAC servers are BL460 with 2 Xeon quad-core 3GHz processors, different from the pre-configured database machine).

Was the intent that people would see the Oracle RAC with Exadata storage and draw conclusions based on the approximately 10X difference in performance with another recent 1000GB result?

What conclusions can we draw from each of the two results? The first matter to understand is that the TPC-H scale factor 1000GB means the LineItem table, data only, is approximately 1000GB in size. The full database with all tables and indexes is approximately 1700GB. So the entire database fits in the 512-core RAC system with 2080GB and not in the 64 core system with 384GB.

Next the 64 core Itanium system has 64 dual-port FC adapters, meaning 128 x 4Gbit/sec ports, which could support 42GB/sec based on 330MB/sec per 4Gbit/s FC port. But it is unlikely that 768 disk drives can sustain this volume (55MB/sec per disk) in a SAN. It is also interesting that system was configured with the EVA 4400 while other HP SuperDome Unix results employ the MSA1000 storage. (It is nice to have 32 EVA SAN systems or 256 MSA 1000s available for performance testing).

Note that TPC-H query 1 (a table scan of most of the LineItem table) takes 169.8 seconds on the 64 core Itanium, and 10.3 seconds on the 512 core RAC system. This means that if the data had to be read from disk, then the disk system would have to support 6GB/sec on the 64 core and 97MB/sec on the 512 core system. The 64 core Itanium system definitely has to read data from disk and the configured disk can easily support 6GB/sec (only 8MB/sec per disk) while the 512 core system has 6 Exadata storage systems which can support exactly 6GB/sec per specifications, nowhere near 100MB/sec. But all the data fits in memory so disk reads for data does not occur given the TPC-H sequence where the test run occurs after the database load and index build.

The data load time on the 64-core Itanium was 1:07:12 and 2:22:57 on the 512-core RAC, which also may indicate relative storage performance

Another point that can be noted is that there is an 8X difference in cores for a 9.5X difference in performance. However, the Core 2 architecture Xeon 3.0GHz cores (45nm) are much more powerful than the Itanium 2 1.6GHz cores (90nm). The gain in Power is 6.6X and 13.6X in through-put. TPC-H scored is based on a geometric mean of 22 queries, some of which are small and other large. The geometric mean has the effect that a 2X speedup in a small query has the small benefit as 2X in a large query. The issue is the getting 10X gain in a small query is very difficult so scaling on Power is attenuated.

In summary, the two performance reports are definitely sufficient to assert that Oracle RAC can scale, but having single node and 8 node performance reports for the BL460 would be confirm this. The two TPC-H reports say nothing of Exadata storage system performance, either in the sustainable sequential bandwidth or the value of the Smart Scan Offload processing. If the 64 node, 2080GB memory Exadata storage result had been reported at 10TB, then we might have an idea of its capabilities. Based on the 100GB/sec table scan estimate above, it would require 100 Exadata cells, which might beyond its actual scaling capabilities.

The performance data cited in Oracle’s Exadata whitepaper lack details to attribute the source of the performance gain. Given that most SAN systems are horribly configured for Data Warehouse performance, it is quite probable that dropping in the preconfigure full rack Exadata with 14GB/sec sustained table scans can easily generate the quoted numbers.

Notes on the Exadata Storage Server

At the time this product came out, the choice of the DL180G5 was reasonable. However, this system, based on the Intel 5100 chipset, has 1 x8 and 2 x4 PCI-E Gen 1 slots.

I am guessing that the Infini-band dual port HCA occupies the PCI-E x8 slot, the P400 RAID controller occupies one of the x4 slots and that the 12 internal disks are connected to one of two x4 SAS ports on the P400.

Technically, the each Infiniband DDR (5Gbit/s) x4 channel is 20Gbit/s, which after 8B/10B encoding is 16Gbit/sec (2GBytes/s) which could fully consume a x8 PCI-E Gen 1 channel. There are 2 IB channels for path redundancy, not bandwidth aggregation, as the x8 PCI-E bandwidth is limited to 2GB/sec.

Since the disk drives are probably on a single x4 SAS port, this bandwidth is limited to 1GB/sec, even though each 15K disk drive can do 160MB/sec (not accounting for RAID 10 implications). So while there is 4GB/s combined bandwidth on the Infiniband links, only 2GB/sec can be sent over the PCI-E port to the IB HCA, and only 1GB/s can be drive from the RAID controller.

Now that the DL180G6 is available, with 1 x16 and 2 x8 PCI-E Gen 2 channels, I would retain the IB dual port x4 (if it supports PCI-E gen 2), go to the new P410 RAID Controller, the 25-bay SFF drive bay (assuming the drive bays can be split across two x4 SAS channels), and 24 146GB 15K or 300GB 10K SFF drives (There is no point offering a 250GB SATA option). This unit should support 2GB/sec.

I might even be tempted to bug HP to split the drive bays 4 channels, 6 bays per channel, x4 SAS on each channel. There would be 2 P410 controllers, each driving 2 channels. The 6 disks in each channel might not drive 1GB/s, but 4 channels might support 3GB/s? I priced this around $19K.

To bad we cannot have a generic Infiniband SAN, and skip the Exadata software ($5K licensing per disk?).

Earlier I talked about the first TPC-C and TPC-E results for 2-way Nehalem, ie, the Intel Xeon 5500 series. The results were spectacular relative to the previous generation Xeon 5400 series, (2.5X gain on the Intel slide deck for database OLTP) and were pretty much hitting the same range as 4-way Xeon 7460.

I pointed out that while these were legitimate results, the TPC-C and TPC-E benchmarks generate high call volume, about 1000 RPC stored procedure calls per second per core. Meaning each call averages around 1 CPU-ms. This type of usage benefits from the Intel Hyper-Threading feature. It was around 10-20% back in the NetBurst days. I am inclined to think it is now much larger with Nehalem, possibly 30-40%. An application like TPC-H would not benefit from HT. Nehalem should still show a moderate performance gain over Core2 on the basis on micro-architecture improvements alone (plus the integrated memory controller).

Well, my thanks to Dell for publishing a TPC-H for Nehalem. Notice to other vendors: get going, slackers! Below is the 2-way Xeon 5500 versus Xeon 5400 or 5300, and 4-way Xeon 7460 or 7350.

System             Configuration                                                    TPC-H@100GB

T610                 2 Xeon 5570 Quad-Core 2.93GHz, 8M L3, 48GB               28,773

ML370G5          2 Xeon 5355 Quad-Core 2.66GHz, 2x4M L2, 64GB           17,687

DL580G5           4 Xeon 7350 Quad-Core 2.93GHz, 2x4M L2, 128GB         34,990

System             Configuration                                                      TPC-C

DL370G6           2 Xeon 5570 Quad-core 2.93GHz, 8M L3, 144GB      631,766 (Oracle/Linux)

ML370G5          2 Xeon 5460 Quad-core 3.16GHz, 2x6ML2, 64GB      275,149

DL580G5           4 Xeon 7460 Six-core 2.66GHz 16M L3, 256GB         634,825

System             Configuration                                                      TPC-E

Fujitsu RX300    2 Xeon X5570 Quad-core 2.93GHz, 8M L3, 96GB     800.00

TX300 S4          2 Xeon X5460 Quad-core 3.16GHz, 8M L2, 64GB      317.45

Dell R900          4 Dunnington Six-core 2.66GHz, 16M L3, 64GB       671.35

It is unfortunate that the last TPC-H results on Intel were the 65nm Xeon 5300 and 7300 series (except for the Unisys 10TB 16-way). Lets just suppose that a Xeon 5470 3.33GHz would score 20% higher than the Xeon 5355 2.66GHz. The 20% frequency difference might contribute 10%, and the micro-architecture improvements going from the 65nm to 45nm Core2 contribute the rest (The larger cache is not expected to benefit TPC-H high row count queries). This would make the Xeon 5500 series 35% faster on TPC-H than the 5400, which is more than I expected just from the Core 2 to Nehalem architecture improvements.

Of course, this Dell result uses the Fusion-IO SSD drives that plug directly into the PCI-E slots, instead of going through a RAID controller, then the SAS interface. I am looking through the individual TPC-H queries, comparing against both the 2-way 5355 and 4-way 7350 results. I think there is reason to believe that the SSD storage improves performance over a large array of disk drives. A large disk array can deliver sufficient sequential bandwidth, but some SQL operations will generate small block IO, and writes to tempdb should be much faster. The 4-way 16-core Core 2 (7350) has better overall performance than 2-way 8-core Nehalem, but on some individual queries, the Nehalem system scores better.

I am inclined to think the 2-way Opteron six-core (Istanbul) could be close to 2-way Nehalem quad-core on TPC-H, despite the large advantage in TPC-C/E. Intel has a myopic view that the big-dog processor should be reserved for the 4-way+ systems. (It may not make sense to put 8-core Nehalem EX into a 2-way system if the 6-core 32nm Westmere core will be available soon.)

To reiterate, it is very important that all key benchmarks are published so we can get a good idea of what to expect under each circumstance. No one (among reasonable people) expects miracles and magic. It is a complete picture that is important. Knowing that you should expect 20% is better than a misguided belief or hope for 2X.

Some people think I am paranoid and deeply cynical. So I will now resemble this accusation. My thinking is Intel had the full set of benchmark results months ago. It was pointed out that some benchmarks, mostly the ones the benefit from HT, showed huge gains, while others just show good gains. Every organization has worthless marketing types that feel the need to justify their salary. So it was decided to withhold the DW results, just so the worthless crap marketing slides could show the big numbers instead of a complete picture. The complete picture is important, we are happy that Nehalem has arrived. We can work its actual performance characteristics; spectacular gain on some, good gain on others. So stop tinkering with the slide deck!

There is the truth, the whole truth, and nothing but the whole truth. Some people can handle the item 1, but know to stay well clear of 2 and 3,

The use of the FusionIO SSD is interesting. As mentioned above, it interfaces directly to PCI-E. The first generation was PCI-E gen 1 x4, and can do 750MB/s (32K) read, 500MB/s write, 116-119K IOPS (4K), in capacities of 80 and 160GB for SLC, 320GB for MLC. The second generation can do 1.5GB/s read, 1GB/s write, 200K IOPS (4K), in capacities 160/320 SLC, 640GB MLC. The interface is PCI-E x8 gen 1 or x4 Gen 2.

The Intel 5520 chipset has 36 PCI-E gen 2 lanes plus the ESI. A 2-way Nehalem system can be built with 1 or 2 5520 IOHs. The Dell T610 has 1 IOH for 2 x8 and 3x4 slots available (x4 for the internal SAS?). The Dell TPC-H config has 4 Fusion-IO drives, which is fine for this test. An actual production system might want to configure more SSDs. The HP ML370G6 with 2 IOHs has 10 slots (2x16, 2x8, 6x4, one for NICs). The x16 slots are useless for database servers because no network or storage IO adapters can really use x16 bandwidth, and definitely cannot make good use of the unbalanced slots. The x16 slots might be useful for HPC or something. Hopefully Dell or HP will make a system with something like 7 x8 and 4 x4 slots. Now to make maximum use of the (current) Fusion IO SSDs, we would have 18 PCI-E x4 gen 2 slots, but I think Fusion IO could be persuaded to do a double wide SSD instead.

SuperMicro does have a dual 5520 IOH motherboard with 7 x8 PCI-E slots. The onboard SAS occupies a x8, and the dual GbE NIC takes another x4. It looks like one x4 is not wired. For a server, I would have used a x4 for the onboard SAS because that may only connect the boot drives, and I would stick the GbE NIC off the south bridge ICH. The x4 Gen2 should be made available for 10GbE. I used to buy SuperMicro systems because their wide motherboard selection allowed me to get the one with the best IO arrangement for database servers. But when SAS came out, I had a hard time getting the right connectors. I may give them another try if Dell or HP does not do a 7 x8 PCI-E Gen 2 system.

SAP numbers were available a few months ago, and now, TPC-C and TPC-E results are available for 2-socket Intel Xeon 5570 systems with the Nehalem quad-core processor. The TPC-C result is on Oracle/Linux, no SQL Server on Windows yet. The TPC-E results are all SQL Server 2008.

I mentioned before that my expectations was that Nehalem would generate substantial performance gain on high call volume applications (ie, the transaction benchmarks), partially attributed to the processor core, but mostly to the return of Hyper-Threading (HT). When HT was first proposed for the Pentium 4 generation, the expectation was that substantial performance gain was possible because certain applications could would many dead cycles waiting for memory access, etc. As with most first generation concepts, the actual performance gains in TPC-C were in the range of 7-10%, and a number of other operations could actually experience performance anomalies, that is, degradation, so HT had to be used with care, or even disabled. I did observe a very large gain (30-50%) with HT in Quest LiteSpeed compression tests on the first Prescott generation NetBurst Xeons. So the expectation was that once the tricky issues with HT in complex code like SQL Server, large gains should also be possible.

System             Configuration                                                    TPC-C

DL370G6           2 Xeon 5570 Quad-core 2.93GHz, 8M L3, 144GB   631,766 (Oracle/Linux)

This came out on the Microsoft website. I have not had time to look over the hardware configuration carefully. Of course the recommended storage is a SAN, vendors love to sell very high margin products when there are perfectly good merely high margin alternatives. Well atleast this one looked at sequential disk IO bandwidth, unlike past configs that were totally silly. HP config for the DL385G5p does 1.5GB/s, the DL585 3GB/s and the DL785 6GB/s. The Dell configs for PE2950 does 1.6GB/s and the R900 4.8GB/s. Gut feeling is the 2-socket systems are underpowered storage wise, but that what happens when you config expensive storage solutions.

http://www.microsoft.com/sqlserver/2008/en/us/fasttrack.aspx

Ok, I withdraw my initial complaint on the MSA 2000, depending on which model. I love direct-attach storage in non-cluster environments for brute force capability. So I like on the HP side, the MSA60 with LFF drives or the MSA70 with SFF drives (because of the high drive count, 25, on a single x4 SAS, this is really for 8K random IO, not large block or sequential). The MSA 60 base unit is $2,999. The MSA 2012sa single controller base unit is $4,499. The MSA 60 requires a RAID controller in the server. The MSA 2012sa has a built-in RAID controller, requiring an SAS HBA on the server. So the MSA 2000 SAS variant is only slightly more expensive than the pure DA solution. The MSA2000 FC variants are far more expensive and (while suitable for cluster environments which this is not) do not contribute to performance.

Intel vs. AMD

The Dell reference systems are on Intel Core2 architecture processors and the HP systems are on AMD Shanghai. For many people, the choice of Intel vs. AMD is highly emotional. Some feel they are Luke Skywalker battling the evil empire. Others feel compelled to quash rebellion in the ranks. I am of the Han Solo thinking ("I ain't in it for your rebellion honey, I'm in it for the money"?). From the technical perspective, the Core2 has the more powerful processor core for CPU intensive operations. Opteron has the integrated memory controller for faster serialized round-trip memory operations. At the 4-socket level, the AMD system has 8 memory channels versus 4 for systems based on the Intel 7300 chipset. The AMD 4-way systems have more IO bandwidth at the 4-way Intel 7300 chipset. One reason HP may have referenced the AMD Opteron line is to have a uniform line from 2-way to 8-way. Unisys now has an ES7000 for the Core 2 architecture. I would like to see the detailed system architecture for that, as well as run some IO bandwidth calibration tests. The HP Integrity line has outstanding IO capability, but is saddled with Itanium 2 on 90nm (soon to be 65nm?).

SAN versus Direct-Attach

I strongly prefer DA over SAN for DW because DA can achieve very high bandwidth at a low cost. A pair of Dell PowerVault MD1000 can support 1.6GB/s for about $12K. I am inclined to think the EMC Clarion CX4-240 with 2 dual-port 4Gbit/s FC HBAs, and 2 DAE will cost in the range of $30K. Yes, the SAN has lots of features, useful for clustered and transactional environments, but not really essential for DW. However, if one were intent on using SAN for DW, I would agree with this approach of achieving very high bandwidth using multiple entry/mid-level SANs rather a single high-end SAN.

What are the top core SQL Server engine performance issues today, after all the improvements that have gone into 2005 and 2008? (I am excluding matters beyond the power of Microsoft, like eliminating bad developers.)

SQL Server already has statement level recompile for multi-statement stored procedures. But there is no option to change a plan for a single statement in the course of execution. SQL Server also it has the OPTIMIZE FOR hint and Plan Guide options. This allows for a single plan for a particular query. What if there needs to be multiple plans for a single query?

Runtime Plan Change.

Consider a single SQL query, with multiple table access steps in the execution plan. It is not always possible to estimate row count at each step in the execution plan. This is why it is called an estimate. (I know some complain when the estimate is off by 10%, Lubor once said he is interested in estimates off by 2X, but I consider catastrophic errors tend to be orders of magnitude, 10-1000X or more). Suppose the SQL Server engine starts the first steps of execution plan. The estimated row count was low, so the subsequent steps employ index seeks with nested loops joins to complete the query. In actuality, the row count is very large, so table scans with hash or merge joins are much better. The opposite scenario is a very high estimate row count that turns out to be low. The remaining steps of the execution plan involve table scans with hash or merge joins, when index seeks with nested loops joins would be far more efficient. Now there are two options. One is to retain the current partial results, but change subsequent steps, to account for the new row count. In some cases, it will be much better to discard the current progress and generate a completely new plan based on the new row count. If this could be implemented, my thoughts are that this should only be enabled explicitly for known problem queries instead of a system wide on/off switch.

Multiple Execution Plans for a single SQL

Now consider a single SQL query, involving multiple search arguments. Depending on the input parameters, the row count involved for each argument varies drastically. So the Optimize For and Plan Guide options can only optimize for one particular scenario. I have handled this by making two or more copies of the original stored procedure, then having the original procedure check actual data distribution, and then call one of the copies. The sub-procedures are identical, but copy A is only called when parameter 1 is high, parameter 2 is low, and Copy 2 is called when parameter 1 is low and 2 is high. It is not necessary to have one copy for each possibility, only if that possibility requires a significantly different execution plan.

My thought on handling this automatically is to extend the OPTIMIZE FOR hint to from specifying a single parameter value to multiple.

Example: instead of

Below are the first performance numbers for the new AMD 45nm Opteron (Shanghai) relative to other recent AMD and Intel Xeon results.

4-way TPC-C

Opteron 8360 Quad-core 2.5GHz (Barcelona) 2M L3, 471,883 (DL585G2)

Opteron 8384 Quad-core 2.7GHz (Shanghai) 6M L3, 579,814 (DL585G2)

Xeon 7350 Quad-core 2.93GHz (Tigerton) 2x4M L2, 407,079 (DL580G5)

Xeon 7460 Six-core 2.66GHz (Dunnington) 16M L3, 634,825 (DL580G5)

Xeon 7460 Six-core 2.66GHz (Dunnington) 16M L3, 684,508 (x3850M2)

(Note: Intel uses inclusive cache, so I just quote the size of the last level. Opteron is exclusive, so technically I should quote L2+L3, the L2 being 4x512K.) 

Both Opteron TPC-C systems have 256GB memory, 700 disk drives, so the Shanghai is running at lower memory per tpm-C and lower disk per tpm-C. Still, the performance gain from Barcelona (65nm) to Shanghai (45nm) is 23% for 8% higher frequency. For reference the 4-way dual-core Opteron result was 262,989 for the 8220 2.8GHz.

8-way TPC-H 300GB

Opteron 8360 Quad-core 2.5GHz (Barcelona) 2M L2, 52,860 QphH (DL785, S2K8 w/compression)

Opteron 8374 Quad-core 2.7GHz (Shanghai) 6M L2, 57,865 (DL785, S2K8 w/compression)

Xeon 7350 Quad-core 2.93GHz (Tigerton) 2x4M L2, 46,034 (x3950M2, S2K5)

The reason I say SPEC CPU integer base is important is that is a reasonable predictor of standard non-parallel execution plans for a wide range of SQL operations. Many people have a favorite query they like to run to evaluate new systems. So even though the Opteron can achieve very good results on the 8-way for well tuned parallel plans (and spec int rate), single threaded operations fall far short of the Core 2 based Xeon. So it is important to test single thread, through-put and parallel plan performance separately.

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.

A curious note. The Dell TPC-E report for the R900 with 4 x Xeon X7460 used the LSI MegaRAID SAS 8888ELP controllers, not the PERC6E (there was a PERC6i for internal drives). Both PERC6 and LSI 8888 use LSI components (and common drivers?)