TPC-H comments

TPC-H is under appreciated for its informational value by both vendors and end users. Running this data warehouse benchmark requires both good scaling of parallel execution plans, and the ability to allocate resources between queries with parallel execution plans. At the system level, it requires a well balanced storage system, as mentioned in the storage post, compared with the extravagant random IO required by TPC-C. Unfortunately, there are not as many publications as desired to make detailed assessments. At this time there is only one published TPC-H result with SQL Server 2008.

SQL Server 2005 Results

First, let us look at some TPC-H results with SQL Server 2005. A good reference point for current generation systems is the HP ProLiant DL585G2 (4 AMD Opteron 8220 dual-core 2.8GHz processors and 128GB memory) result of 19,323 QphH at 100GB. The Dell result on their PowerEdge 6950 also with 4 Opteron 8820, but 64GB memory was 17,180 QphH. It is unclear whether the difference is mostly due to the difference in memory, other differences in hardware or in tuning techniques. A TPC-H scale factor 100 test means the Line Item table data is 100GB. The total database size with other tables and indexes is about 170GB. The HP ProLiant DL580G4 with 4 Intel Xeon 7140 dual-core 3.4GHz processors, and 64GB memory scored 17,120 QphH. The HP ProLiant ML370G5 with 2 Xeon X5355 quad-core 2.66GHz processors and 64GB memory scored 17,687 QphH. All of the recent and current generation processors have comparable performance at the 8-core level.

The Lenovo R630 with 4 Intel Xeon 7350 quad-core 2.93GHz processors and 128GB memory scored 33,184 QphH. This shows reasonably good scaling from 8 to 16 cores. The proper comparison system is the Opteron result of 19,323 also with 128GB memory, for a gain of 1.72, which is very good, but not great.

SQL Server 2008 versus Oracle 11g

Microsoft announced some new techniques in SQL Server 2008 that is expected to improve large data warehouse-type query performance. Ideally one would like to see side-by-side comparisons between SQL Server 2005 and 2008 on otherwise identical system and configuration. At this time, there is only one result with SQL Server 2008. The HP Integrity Superdome with 32 Itanium dual-core 1.6GHz processors result at 10,000GB is 63,651 QphH. This is the first Microsoft SQL Server result on the 10TB scale factor. There had been several SQL Server results at 3TB all on the Itanium platform since 2005. The largest scale factor for Xeon and Opteron results is 1TB. One might expect a mature database engine to be able to execute very large query. The fact is that the database engine must allocate resources, including virtual address space (VAS), and physical memory, in handling the intermediate results of a very high row count query. Algorithms that worked on large queries of 100GB or even very large queries of 1TB might break at 10TB unless it is actually tested.

There is an Oracle 11g result at 10TB on the HP Integrity Superdome with 64 dual core processors (128 physical cores, 256 logical processors) of 208,458 QphH. The Windows operating system is still limited to 64 processor cores, physical or logical. While the market volume for very high-end is small, the direction towards more cores per socket means that Microsoft needs to handle more than 64 cores by the next generation at the latest, and preferably sooner. It takes 3-4 years to work out the more serious issues with bleeding edge technology, so the sooner this is available, the better the chances it will be sufficiently mature when 128 or more cores become cost-practical in high volume systems.

The performance gain going from 32 to 64 dual core processors is expected to be less than 2X. The difference between the Oracle and SQL Server result is 3.27X. It is well known that data warehouse queries favor a larger page size. The SQL Server page size has been fixed at 8KB since version 7. Oracle has allowed a range of page sizes for some time now. The minimum used to be 2KB. The page size in this result is 32KB. If page size is a significant contributor, then the next major SQL Server version needs to support larger page sizes (see Linchi’s discussion on this subject). In further examining the Oracle result, I noticed that the tables are partitioned differently. The Line Item table is partitioned first on Date followed by part-key hash. The Orders table is partitioned on Order date followed by the Customer key hash. High row count queries will frequently use a hash join. Aggregate queries will often use hash aggregate operation. In my own testing, I noted that a merge join is about one-half the cost of hash join. It looks like including the hash key in the clustering key can reduce the cost of a hash join. This is potentially a useful feature when the conditions for a merge join cannot be met. Another reason that I pointed out in the storage performance blog is that SQL Server issues 8KB disk IO in scan operations feeding into a hash operation. This is a waste of CPU resources when fewer large block disk IO could have been issued. I also suspect that in parallel execution plans, SQL Server breaks the query into too small pieces. This would cause a higher portion of the cost to be expended in the scatter and gather streams operations.

I do not expect SQL Server 2008 to make up much of the difference. This goal should be for the next major release. Given the broader use of business intelligence, an alternative is pursuing other technologies for order of magnitude gains.