Home, Benchmarks, TPC-H, Details: SF100, SF300, SF1000, SF3000

SQL Server 2008 R2 Data Warehouse Performance Evaluation

While it is evident that there have been tremendous performance improvements in SQL Server versions 2005, 2008 and 2008 R2 pertinent to data warehouse scenarios, there remains certain less than desired aspects that may be rooted in the SQL Server Query Optimizer. More or less unchanged for a long time are:

  1. 1) the ratio lookup & loop join IO to incremental scan IO
  2. 2) and the fixed IO costing in parallel execution plans

Item 1 refers to the 0.003125 lookup & loop join (random) IO cost and the 0.000740741 IO cost per page for an increment scan. This corresponds to 320 random IOPS and sequential IO at 10.8MB/sec (1350 pages/sec x 8kb/page). Another interpretation is 1 IO in a key lookup is equal to 4.2 pages in a scan operation.

Item 2 refers to parallel execution plans costing that reduces CPU by 2X at DOP 2 and 4, and a further 2X for each addition doubling of DOP. IO cost is fixed.

There is some evidence to support this in publish TPC-H performance reports. There is a pair of TPC-H 1000GB reports both on the HP ProLiant DL785 G6 and Opteron 8439 six-core processor, one for SQL Server 2008 EE and the other for Sybase IQ ASE 15.1. There is another TPC-H 1000GB on a 16-way quad-core Itanium 9350 for Oracle 11gR2, not the best comparison but the only recent Oracle result.

Another possible point of comparison is a pair of TPC-H 3000GB reports, one for SQL Server 2008 R2 on an 8-way 8-core Xeon 7560 and the other for Sybase on a 32-way dual-core POWER6.

SQL Server 2008 RTM and Sybase IQ ASE 15.1

The table below shows the SQL Server 2008 and Sybase IQ ASE 15.1 results both on the ProLiant DL785 G6 with 8 six-core Opteron 8439 processors.

SystemProcessorTotal
Cores
MemSQLPowerThrough putComposite
QphH
DL785 G6Opt 8439485122005 sp295,789.169,367.681,367.6
DL785 G6Opt 843948384Sybase 15.1108,436.896,652.7102,375.3
Superdome2It 9350 64512O11g R2139,181.0141,188.1140,181.1

The Sybase is 13% higher on the TPC-H metric and 39% higher on throughput (this will focus exclusively on TPC-H Power).

The table summarizes the significant configuration details for each system.

SystemDL785 G6DL785 G6Superdome 2
DatabaseSQL ServerSybase 15.1Oracle 11g R2
ProcessorOpteron 8439Opteron 8439Itanium 9350
Sockets-Cores8 x 6 = 488 x 6 = 4816 x 4 = 64
Hyper-Threadingnonoyes
Frequency2.8GHz2.8GHz1.73GHz
Memory512GB384GB512G
Storage Controllers6 P8008 x 8Gbps dual-port FC48 DP FC?
Storage Ext12 MSA704 x 2324fc24 MSA2324
Data disks240 HDD96 HDD576 HDD
Controller-Disks3x50, 25, 30, 354x24??
LUNs-disks48x5???
OS2008 R2 EERHEL 5.3HP-UX 11
Database2008 EESybase 15.1Oracle 11g R2

The Sybase system has less memory at 384GB versus 512GB for the SQL Server system. This is not expected to have much impact as other TPC-H report indicate that unless all data can reside in memory, the TPC-H queries performance is not significantly impacted by memory capacity. The other difference is the Sybase has fewer disk drives of 96 versus 240 for the SQL Server system. The storage system is different as the SQL Server system employs RAID controllers with direct-attach storage. The Sybase system has 8 dual-port FC HBAs with the MSA2324fc SAN based storage.

The SQL Server system has a rather peculiar disk arrangement. Three of the P800 RAID Controllers each connect to 50 disks. There are 25, 30 and 35 disks on each of the other 3 controllers. It is presumed that there are 48 5-disk RAID 5 LUNs It might be that the 3 P800 controllers with 50 disks resides in the 3 x16 PCI-E slots, and the other 3 P800 controllers reside in the x8 PCI-E slots. Given that the P800 has a x8 PCI-E port, the exact placement should not matter so long as each P800 resides in a x8 or x16 slot(?). The P800 controller should be able to sustain 1.6GB/sec, but assuming balanced IO per disk, only the 3 P800 with 50 disks in this system will drive 1.6GB/s. Each set of 5 disks will drive 160MB/sec (because of the 50 disks via 1 P800 at 1.6GB/sec), so the other 3 controllers will be driving 0.8, 0.96 and 1.1GB/s for possibly a combined bandwidth of 7.6GB/s.

The Sybase system has 8 dual-port 8Gbps FC HBA for 16 x 8Gbps FC ports. Each 8Gbps port should be able to sustain 600MB/sec+, or 1.2GB/sec per HBA for an upper bound of 9.6GB/sec over 8 HBAs. However this will not be possible in this system because there are only 6 PCI-E slots that are x8 or wider. This would also imply 100MB/sec per disk and 2.4GB/sec per MSA2324fc. The Microsoft Fast Track Data Warehouse Reference Guide has found that 100MB/sec is possible with 2 disk RAID 1 LUNs. An HP report suggests that an earlier model of the MSA with 4Gbps FC connections has a sustained bandwidth of 1.3GB/s. If the bandwidth in limited at the PCI-E x4 slots, then each HBA will be able to sustain 800MB/sec or 6.4GB/sec total.

The Sybase file layout shows 16 LUNs per filegroup. The strategy is that there is 1 file for each FC port as the IO to a specific must travel through 1 path at any given time(?). There maybe fail-over paths. This also implies 8 disks per LUN, meaning that high sequential throughput can be achieved in LUNs with more than 2 disks as employed in the MS FTDW reference.

In any case, both storage system should have sequential IO bandwith (close enough) to support the consumption by the database engines. The significant difference that the SQL Server system with 240 disks will be able to support 2.5X more random IO.

The chart below shows the TPC-H 1000GB Power query runtime for the Sybase system relative to the SQL Server system. There is wide variation in the individual queries, ranging from SQL Server much faster on some to Sybase mucher faster on others. The geometric mean has a 13% advantage for Sybase. The queries that Sybase runs much faster are 6, 11, 12, 17, 18, 19, and 21.

tpch300 DL785 vs DL585
TPC-H Power query run times, Sybase relative to SQL Server, both on DL785 48-core

SQL Server 2008 R2 and Sybase IQ ASE 15.1

When comparing very different database engines, it is desirable to compare them on the same hardware platform, as done above. A comparison involving reasonably comparable systems might also be acceptable. The TPC-H 3000GB report for the 8-way ProLiant DL980 G7 with 8-core Xeon 7560 running SQL Server 2008 R2 can be compared with the 32-way dual-core IBM POWER6 running Sybase 15.1 with some caution to not draw unsupportable conclusions.

Obviously the Intel Xeon 7560 and IBM POWER6 are completely difference processor architectures and support completely different system architectures. At the processor core level, the individual Intel Xeon 7560 core has better performance than the IBM POWER6, even though the 7560 runs at 2.26GHz and the POWER6 in the p595 runs at 5.0GHz. This is evident in the SPEC CPU 2006 Integer (base) benchmark (see below). Still this is valid comparison at the 64-core system level because the IBM POWER6 was design with massive (CPU to memory and node to node) IO bandwidth, both necessary for scaling up.

The table shows the TPC-H 3000GB results for the 8-way 8-core Xeon 7560 system running SQL Server 2008 R2 and the 32-way dual-core POWER6 system running Sybase 15.1.

SystemTPC-H PowerTPC-H ThroughputTPC-H Composite QphH
8 x Xeon 7560185,297.7142,685.6162,601.7
32 x Power6142,790.7171,607.4156,537.3

The 64-core Xeon is 30% higher on the TPC-H Power metric while the 64-core POWER6 is 20% higher on the TPC-H throughput metric. The overal composite queries per hour (normalized per GB) is close enough that these two system could be considered comparable for data warehouse type query performanace.

The system configuration details are below:

SystemHP DL980G7IBM Power 595
ProcessorXeon 7560Power6
Sockets-Cores8 x 8 = 6432 x 2 = 64
Hyper-Threading2 per core4 per core?
Frequency2.26GHz5.0GHz
Memory512GB512GB
Storage Controllers10 LSI SAS 9200
3 P411
24 x 4Gbps dual-port FC
12 DS4800
Storage500 HDD data
144 log
288 HDD
LUNs660 HDD144
OS2008 R2 EEAIX 6.1
Database2008 R2 EESybase 15.1

The HP TPC-H report lists the ProLiant DL980 G7 as being configured with 660 disk drives. Only 500 disks are for data (and tempdb) files. There are 144 disks used only for backups and flat files (as required?) and 16 disks are for the log file. The data disks are connected to the 10 LSI SAS 9200 controller, with 50 disks per controller, 25 disks per D2700 enclosure. The 144 backup disks are connected to the 3 P411 controllers, 48 disks per P411, and 24 disk per D2700 enclosure. Another 16 disks connected by 8 Gbps FC to a MSA2324fc SAN for logs.

The LSI SAS 9200 is actually a simple SAS controller, not a RAID controller. So this system was configured with 500 data files for each file group, one file per disk. My understanding was that this system was used for SSD testing, which did not function correctly with RAID controllers. Otherwise, a system with this many disk drives would have normally use RAID controllers?

HP did demonstrate this system at HP Technology Forum, showing a SQL Server table scan driving 26GB/sec sustained. This works out to 2.6GB/s per controller, in-line with LSI specifications of 2.8GB/s, and the x8 PCI-E gen 2 slot limit.

The IBM system may appear to be under-configured in terms of the number of disk drives. But it does seem that other database engines are better in switching from pseudo-random to sequential scan IO operations than SQL Server, and can work fine with fewer disks.

The chart below shows the TPC-H power query run times for the 32-way IBM p595 with 64-cores relative to the 8-way Xeon 7560 also with 64 cores.

tpch300 DL785 vs DL585
TPC-H Power query run times, 64-core POWER6 relative to 64-core Xeon

The 64 core Xeon 7560 has 30% better TPC-H Power than the 64 core POWER6. The POWER6 in turn has 20% better TPC-H Throughput than the Xeon. Again, there is also wide variation in the individual queries. All seven queries in this example where SQL Server is slower than Sybase are also much slower in the DL 785 example previously discussed.

In query 18 and 19, where the Sybase is faster, the SQL Server execution plan shows key lookups at SF100. It would be helpful if HP could provide execution plans at SF3000. We should not draw too many conclusions when comparing completely different system architectures and completely different database engines. But, I think this is good hint for Microsoft to re-evaluate the execution plan cost formulas.

Below are the individual TPC-H Power (stream 0) query times

ProcessorIntel
Xeon 7560
IBM
POWER6
SystemHP DL980IBM p595
Freq 2.26GHz5.0GHz
Cores8x832x2
Query 1 - Pricing Summary Report 183.8282.7
Query 2 - Minimum Cost Supplier 17.527.5
Query 3 - Shipping Priority 33.3122.1
Query 4 - Order Priority Checking 20.146.4
Query 5 - Local Supplier Volume 45.1149.3
Query 6 - Forecasting Revenue Change17.17.0
Query 7 - Volume Shipping 52.6107.3
Query 8 - National Market Share 63.9112.5
Query 9 - Product Type Profit Measure234.5230.5
Query 10 - Returned Item Reporting 25.9136.7
Query 11 - Important Stock Identification71.441.9
Query 12 - Shipping Modes and Order Priority81.050.3
Query 13 - Customer Distribution 82.0269.9
Query 14 - Promotion Effect 11.314.3
Query 15 - Top Supplier11.555.7
Query 16 - Parts/Supplier Relationship53.540.3
Query 17 - Small-Quantity-Order Revenue66.494.1
Query 18 - Large Volume Customer167.7111.0
Query 19 - Discounted Revenue143.560.3
Query 20 - Potential Part Promotion40.642.5
Query 21 - Suppliers Who Kept Orders Waiting370.0193.5
Query 22 - Global Sales Opportunity40.343.5
RF1173.0126.9
RF2126.3134.4

The colored background highlights the individual queries in which Sybase on POWER6 has better performance than SQL Server on Xeon 7560. Given that the processor and system architectures are completely different and that the database engines are also completely different, we should not try to draw conclusions that have no meaning.

However, given that the overall performance of the Xeon/SQL Server system is somewhat higher than the POWER6/Sybase system, we could surmise that in individual queries where SQL Server is better or comparable, that the SQL Server execution efficiency is probably good, both in terms of the SQL Server engine and the execution plan. In queries that the IBM/Sybase system does better, this is an indication of something that could be improved on the SQL Server side. This could the engine itself or the execution plan. Or could be just that the POWER6 system is better at that type of operation. If the execution plans were comparable, then it is probably the engine that is better. If the execution plans are fundamentally different, this might indicate the other platform has a better optimizer.

SPEC CPU 2006 Integer

Below are SPEC CPU 2006 Integer base results for the Xeon X7560 2.26GHz, the IBM POWER6 (at 4.7GHz) and the new POWER7 4.14GHz.

ProcessorIntel
Xeon 7560
IBM
POWER6
IBM
POWER7
SystemIBM
x3850 X5
IBM
p570
IBM
p780
Freq 2.26GHz4.7GHz4.14GHz
Cores4x81x24x4
400 perlb 20.611.321.8
401 bzip2 15.414.222.3
403 gcc 16.916.824.7
429 mcf 30.735.263.2
445 gobmk 18.715.822.9
456 hmmr 37.412.924.2
458 sjeng 19.813.923.0
462 libq 63451.678.0
464 h264ref27.523.742.6
471 omnetpp19.614.521.5
473 astar 16.412.121.9
483 xalanc 29.318.126.8

base

29.1

17.8

29.3
OSSuSE 11RHELAIX 6.1
CompilerIntel C 11.1IBM XL 9.0XL 11.1

The purpose of excluding lib quantum is to compare single core performance.  The Intel compiler can parallelize lib quantum, so it is not a single core result(?). I am somewhat inclined to also exclude hmmr because the Intel 11.1 compiler made substantial improvement over their 11.0 compiler. AMD results are on the PGI 8.0 complier, which may not have either optimizations.  

TPC-H SF 100 Query Plans

The published 8-way Xeon and 32-way POWER6 TPC-H results were at SF 3000GB. I cannot generate the SF 3000 query plans on my systems. Hopefully HP will be able to provide these. For now, below are SF 100 query plans for query 6, 11, 12, 16, 18, 19 and 21.

 

/* TPC_H Query 6 - Forecasting Revenue Change */

SELECT SUM(L_EXTENDEDPRICE*L_DISCOUNT) AS REVENUE
FROM LINEITEM
WHERE L_SHIPDATE >= '1994-01-01' AND L_SHIPDATE < dateadd(yy, 1, cast('1994-01-01' as date))
AND L_DISCOUNT BETWEEN .06 - 0.01 AND .06 + 0.01 AND L_QUANTITY < 24

TPC-H Query 6 Plan

 

/* TPC_H Query 11 - Important Stock Identification */

SELECT PS_PARTKEY, SUM(PS_SUPPLYCOST*PS_AVAILQTY) AS VALUE
FROM PARTSUPP, SUPPLIER, NATION
WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'GERMANY'
GROUP BY PS_PARTKEY
HAVING SUM(PS_SUPPLYCOST*PS_AVAILQTY) > (SELECT SUM(PS_SUPPLYCOST*PS_AVAILQTY) * 0.0001000000
 FROM PARTSUPP, SUPPLIER, NATION
 WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'GERMANY')
ORDER BY VALUE DESC

TPC-H Query 11 Plan

 

/* TPC_H Query 12 - Shipping Modes and Order Priority */

SELECT L_SHIPMODE,
SUM(CASE WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS HIGH_LINE_COUNT,
SUM(CASE WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH' THEN 1 ELSE 0 END ) AS LOW_LINE_COUNT
FROM ORDERS, LINEITEM
WHERE O_ORDERKEY = L_ORDERKEY AND L_SHIPMODE IN ('MAIL','SHIP')
AND L_COMMITDATE < L_RECEIPTDATE AND L_SHIPDATE < L_COMMITDATE AND L_RECEIPTDATE >= '1994-01-01'
AND L_RECEIPTDATE < dateadd(mm, 1, cast('1995-09-01' as date))
GROUP BY L_SHIPMODE
ORDER BY L_SHIPMODE

TPC-H Query 12 Plan

 

/* TPC_H Query 16 - Parts/Supplier Relationship */

SELECT P_BRAND, P_TYPE, P_SIZE, COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT
FROM PARTSUPP, PART
WHERE P_PARTKEY = PS_PARTKEY AND P_BRAND <> 'Brand#45' AND P_TYPE NOT LIKE 'MEDIUM POLISHED%%'
AND P_SIZE IN (49, 14, 23, 45, 19, 3, 36, 9) AND PS_SUPPKEY NOT IN (SELECT S_SUPPKEY FROM SUPPLIER
 WHERE S_COMMENT LIKE '%%Customer%%Complaints%%')
GROUP BY P_BRAND, P_TYPE, P_SIZE
ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE

TPC-H Query 16 Plan

 

/* TPC_H Query 18 - Large Volume Customer */

SELECT TOP 100 C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM CUSTOMER, ORDERS, LINEITEM
WHERE O_ORDERKEY IN (SELECT L_ORDERKEY FROM LINEITEM GROUP BY L_ORDERKEY HAVING
 SUM(L_QUANTITY) > 300) AND C_CUSTKEY = O_CUSTKEY AND O_ORDERKEY = L_ORDERKEY
GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE
ORDER BY O_TOTALPRICE DESC, O_ORDERDATE

TPC-H Query 18 Plan

 

/* TPC_H Query 19 - Discounted Revenue */

SELECT SUM(L_EXTENDEDPRICE* (1 - L_DISCOUNT)) AS REVENUE
FROM LINEITEM, PART
WHERE (P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#12' AND P_CONTAINER IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND L_QUANTITY >= 1 AND L_QUANTITY <= 1 + 10 AND P_SIZE BETWEEN 1 AND 5
AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')
OR (P_PARTKEY = L_PARTKEY AND P_BRAND ='Brand#23' AND P_CONTAINER IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND L_QUANTITY >=10 AND L_QUANTITY <=10 + 10 AND P_SIZE BETWEEN 1 AND 10
AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')
OR (P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#34' AND P_CONTAINER IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND L_QUANTITY >=20 AND L_QUANTITY <= 20 + 10 AND P_SIZE BETWEEN 1 AND 15
AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')

TPC-H Query 19 Plan

 

/* TPC_H Query 21 - Suppliers Who Kept Orders Waiting */

SELECT TOP 100 S_NAME, COUNT(*) AS NUMWAIT
FROM SUPPLIER, LINEITEM L1, ORDERS, NATION WHERE S_SUPPKEY = L1.L_SUPPKEY AND
O_ORDERKEY = L1.L_ORDERKEY AND O_ORDERSTATUS = 'F' AND L1.L_RECEIPTDATE> L1.L_COMMITDATE
AND EXISTS (SELECT * FROM LINEITEM L2 WHERE L2.L_ORDERKEY = L1.L_ORDERKEY
 AND L2.L_SUPPKEY <> L1.L_SUPPKEY) AND
NOT EXISTS (SELECT * FROM LINEITEM L3 WHERE L3.L_ORDERKEY = L1.L_ORDERKEY AND
 L3.L_SUPPKEY <> L1.L_SUPPKEY AND L3.L_RECEIPTDATE > L3.L_COMMITDATE) AND
S_NATIONKEY = N_NATIONKEY AND N_NAME = 'SAUDI ARABIA'
GROUP BY S_NAME
ORDER BY NUMWAIT DESC, S_NAME

TPC-H Query 21 Plan

TPC-H Query 21 Plan