Joe Chang : Scalability

Big-Iron Revival II, Nehalem-EX and Windows Server 2008 R2

jchang — Sun, 27 Sep 2009 22:39:00 GMT

At Intel Developer Forum 2009 last week, Microsoft disclosed significant advances in Windows Server 2008 R2 with the elimination of many locks, most prominently the Dispatch Scheduler lock, that impact the ability scale performance up to and beyond 64...(read more)

Benchmark Omissions for the Six-Core Intel Xeon AMD Opteron Processors

jchang — Sun, 13 Sep 2009 20:23:00 GMT

To date, no 4-way or 8-way TPC-H data warehouse benchmark result has been published for the six-core Xeon X7460 and no TPC-C or TPC-E OLTP benchmark result has been published for six-core Opteron. Usually, the absence of published results means the results...(read more)

First Nehalem TPC-H

jchang — Wed, 03 Jun 2009 19:55:00 GMT

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.

Big Iron Revival, Intel Nehalem EX and AMD Magny-Cours

jchang — Wed, 27 May 2009 20:37:00 GMT

Yesterday Intel held a product announcement press event for the upcoming Nehalem EX, which will succeed the current Xeon 7400 series based on the Core 2 micro-architecture for "expandable system", i.e., 4-way and higher, in late 2009 or early 2010. The current Xeon 5500 series (also Nehalem architecture) has 4 cores, 8M shared L3, 2 QPI links, and 3 DDR3 memory channels. Nehalem EX has 8 cores, 24M shared L3 cache, 4 QPI links and 4 ~~FBD~~ memory channels (there is now a Scalable Memory Buffer between the memory interface and memory, did Intel just move the AMB from the DIMM to the motherboard?).

AMD has also recently discussed their plans. The current quad-core Shanghai gets a frequency bump from 2.7GHz to 3.1GHz, and a six core Istanbul should be released very soon (June, announced at 2.6GHz). See the Johan de Gelas Anandtech article on Istanbul. It describes HT assist, (essentially a snoop filter for HT) as using 1M of the L3 cache. The HP ProLiant DL585G6 for Istanbul also appears to be HT version 3.0 or HT3, upping the HT transfer rate from 2GT/s to 4.4GT/s.

Later on, there will be Magny-Cours, which would be 2 Istanbul die in one package. Istanbul has six cores, 3 Hyper Transport links and 2 memory channels. In Magny-Cours, the two six core chips are linked by one HT link, so the external package will have 12 cores, 4 HT links and 4 memory channels. After this, a new improved micro-architecture would arrive?

Now there have been big iron Windows systems for many years. The HP Superdome supports up to 64 Itanium 2 sockets. The problem has been that Intel has not kept pace with Itanium. The current Itanium 9100 series, Montvale, is a 90nm dual core, while the Xeon line is at 45nm and six+ cores. Tukwila, the 65nm quad-core Itanium that should have been launched in 2008, was recently delayed until 2010. Supposedly Itanium should finally be caught up on process technology in 2011 with the 32nm Poulson. Unisys (ES7000 7600R), NEC (Express5800/A1160) and IBM (x3950M2) all have had 16-socket capable Xeon systems for a while. HP has the 8-way ProLiant DL785G5 for Opteron processors (I really would like to get the architectural diagram for how HP connects the 8 sockets). I have not followed Sun since I focus on Windows/SQL Server. (Sun has the 8-way x4600 for Opteron. see http://www.sun.com/servers/x64/x4600/arch-wp.pdf for an architectural diagram on how 8 Opterons are connected in a twisted ladder)

Still, I consider this to be a revival or perhaps true arrival of big iron because of the issues in the past on scaling beyond 4-sockets, both in terms of performance and price-performance.

Previously, there were technical challenges in scaling the Intel Xeon beyond 4 sockets, both for the system vendors in designing such a system, and the DBA/developer in getting their application to scale beyond 4-sockets. For an OEM to build an 8-way+ system, it required the effort to built custom chips, the market volume was low, and Intel kept changing the FSB. All of this meant there was a big step up in price per socket going from a 4-socket system to 8, 16 or 32.

This was the rational for Oracle RAC. Instead of buying really expensive big-iron hardware, one can buy lower cost high volume hardware and really expensive software licenses. Think about it. Scaling up on big iron or a RAC-type technology depends on interconnect bandwidth and latency. For either the Intel QPI or AMD HT, it should be possible to achieve far better bandwidth and latency in big-iron than a RAC-type solution. The best Infini-band can do now in a x4 link is 40Gbit/s (5GB/s) at approx 1us latency.

Now that there is prospect of stability in the Intel processor interconnect, my expectation is that we should now see 8-way+ systems at a less severe price premium over 4-way systems. (there will always be a premium because validating and supporting big systems requires deeper technical skills). On AMD Opteron, having the 4 HT ports from one package enables 8-way glue-less systems (with fewer hops) and helps in building 8-way+ (with glue?).

In the Intel announcement was that 8 OEMS have 15 or so 8-way+ (including 16 and 32-way) Nehalem EX systems in the works. IBM, NEC and Unisys are obviously 3 of the OEMs, given their recent commitment to big-iron Xeon. Fujitsu and Hitachi might be another 2, as the Japanese players love big-iron. Sun should be one for 6 of the 8 OEMs. I am guessing this means HP and Dell are the two remaining OEMs. HP is no surprise. They already have the 8-way Opteron. Their commitment to Itanium means that HP would have built a chipset around QPI for the next generation, which is the same processor interconnect on Nehalem.

Dell is the question. Their attitude might be that they do not expect to sell many big-iron systems, considering the technical difficulties they had in the past on this. To sell big iron, it is absolutely necessary to have top technical expertise to go into customer shops to find out if it is the right solution and what changes need to be made to deploy successfully. (OEM reps are invited to drop hints, even if its still a company secret, we will keep it just between us)

[OK, I forgot about SGI, they have big iron Itanium, which means if they do a chipset for the next gen Itanium with QPI, they can do a Nehalem-EX too. plus they just blogged this http://ceoblog.sgi.com/]

Up to Windows Server 2008 RTM, the OS does not support more than 64 cores, physical or logical. This limit will be lifted with Windows Server 2008 R2, accompanied by SQL Server 2008 R2(?). Both the Unisys 7600R and NEC A1160 posted TPC-E benchmark results for 16-sockets, but only 4 of the 6 cores in the Intel X7460 processor enabled, to stay under the current 64-core limit. Scaling was decent, but not spectacular, going from 721tps-E@4-sockets/24 cores, to 1156 tps-E@8S/48c, to 1400tps-E@12S/64c and 1568tps-E@16S/64c.

Note that scaling large/(hard) NUMA systems require proper use of port affinity settings, and how interrupts are handled. Windows 2008 R2 supposedly has a much improved disk I/O handling on NUMA systems.

The Intel announcement mentioned that 4-way Nehalem EX will have 2.5X+ performance over 4-way Xeon 7460, based on a very recent internal measurement using OLTP workload, i.e., TPC-C or TPC-E. This is also inline with the huge TPC-C & E gains posted by 2-way Xeon 5500 over Xeon 5400. Previously I discussed this matter. Each Nehalem core should have moderately better performance than a Core 2 micro-architecture core. Nehalem systems have more memory channels to better support multi-core scaling. The Nehalem EX 4-way system has 16 memory channels supporting 32 cores, versus the Xeon 7400 (7300 MCH) 4 memory channels supporting 24 cores. Nehalem EX will have 8 physical cores compared with 6 on Xeon 7460. Finally, both TPC-C and TPC-E benefit from Hyper-Threading, a feature from the Pentium 4 (NetBurst) micro-architecture (designed in Oregon), but not implemented by Core 2 (designed in Israel). Anyways, 2.5X over X7460 means 1.6M tpm-C or 1700 tps-E.

Now both TPC-C and TPC-E are OLTP benchmarks (workloads). The interpretation should not be that HT (and large cache) benefit OLTP workloads as in any one else's OLTP workload. Each TPC-C transaction involves on average 2.25 or so RPC calls (network roundtrip) and each TPC-E transaction involves approximately 22.3 RPCs. By looking at the recent results on Xeon 7460 or Opteron Quad-core, one can figure out that the average cost per RPC in both TPC-C and E is on the order of 1 CPU-millisecond (the duration of the complete RPC might be longer, say 80-400ms)

The correct interpretation should be that HT and large cache benefits high call volume applications, transaction processing or not. HT benefits mostly in the network round-trip. This was based on tests done on the previous version of HT, i.e., Pentium 4 architecture. I did not find one SQL operation that benefited from HT except in handling just the RPC overhead. The Quest LiteSpeed compression engine did show huge gains with HT, 40%. This indicates the theory behind HT is valid. One just needs to figure what in the SQL Server engine does not like HT. It is possible that the HT in Nehalem now works better with SQL Server.

The large cache reduces the (fixed) startup cost of an SQL operation, but not the incremental cost per additional rows. So if someone else's OLTP application average 10 CPU-ms per call, then it might not show as much gain going from Core 2 to Nehalem.

I suspect this is the reason Intel has not posted any TPC-H benchmark results. It should show some gain over Core 2, just not the spectacular gains in C & E. I am inclined to think that the 4-way Xeon 7460 is memory bandwidth constraint in TPC-H, and that is alleviated in Nehalem, but there are no published TPC-H results to substantiate this matter.

Dunnington and Nehalem EX are both 45nm. Dunnington has 1.9 billion transistors, 6 cores, there is a 3M L2 cache shared by each pair of cores, and a 16M L3 cache shared by all cores for a L2+L3 total of 25M. Nehalem EX has 2.3B transistors, 256K L2 cache dedicated for each core and 24M L3 cache for 26M L2+L3 cache. Granted there is a big increase in latency from L2 to L3. I would interest to see the supporting data (estimates made before the design work) for the big L2 caches in Dunnington.

Even with all of the improvements over time, on the hardware with Nehalem, integrated memory controllers, QPI, on the software stack, w2k8r2 and s2k8r8, scaling on NUMA systems is not trivial. What SQL execution plan operations scale?, what does not?, what might have negative scaling? etc, what problems can be fixed with code changes etc. All of this should be done with proper expertise. (Not to be construed as an advertisement or solicitation for services, this will not be cheap either)

PS -

I am neither advocating nor criticizing big-iron systems. The important point is that new systems coming every year are approximately 40% more powerful at comparable price ranges. That means the value of compute power depreciates at 30% per year (1/1.4 = 0.71). So it does not make sense to buy now for what you do not expect to need for 2+ years. Buy what need for the next year, and buy a new system after that, rotating the existing system to a less important task. Of course, if you work for an inflexible government agency that mandates replacement at 5 year intervals, or if buying the $1M system makes you more important than the other group that runs on a $30K system, well then go for it! On the flip side, one should not argue for the minimum system that meets requirements, but rather think about how massive compute power can be used to generate value.

I used have many complaints about Intel, particularly on the chipsets. Most have been addressed. The remaining complaint is that Intel has a twisted view that 4-way systems are special, ie, compared to 2-way systems. This is why the 6-core Dunnington is only used in the Xeon 7400 series and not the 5400 series, even though there is no reason it cannot be used in the 5400. The same applies to the upcoming 8-core Nehalem EX being only positioned in the 7000 line and not the 5000 line. AMD has no issues offering 6-core Istanbul in a 2-way. Hopefully, hardware vendors will have a better picture of customer interests, and offer a 2-way for Nehalem EX. Sure I know it is not cheap, this is why the different between men a boys is the size and price of their toys.

HP/Oracle just published a RAC TPC-H result with 64 BL640c blade servers at1000GB. This system comprised 128 quad core Xeon 5450 processors (512 cores), 32GB memory per node (64GB on one node). The total memory was 2080GB. The full database size should be around 1700GB. The 1000GB description is for just the LineItem table, not including the two non-clustered indexes and the other tables.

CPU memory Power Throughput QphH

32 Itanium2 DC 256GB 90,909 53,899 69,999

128 Core2 QC 2080GB 782,609 1,740,122 1,166,977

Based on the published Oracle RAC results, I should point out that RAC scaling on TPC-H does look good. The almost total lack of TPC-C (1 published?) may indicate an issue in scaling high-call volume applications. In the above mentioned Oracle RAC result, the blade server hardware costs were about $700K, $500K for storage, $3M for Oracle, $1.5M for RAC, $700K for partitioning, $700K for compression, $400K for support ($100K for unbreakable Linux support, if its unbreakable, why the support?) for about $6M in software, minus $1.8M in Oracle discounts. If I could charge that much, I would get myself a 400ft yacht. Never mind, Larry already did.

Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

jchang — Sun, 24 Aug 2008 17:25:00 GMT

I had been meaning to do a somewhat comprehensive review of SQL Server performance from versions 2000 to 2008 for both 32 and 64-bit on Data Warehouse type queries, with in depth examination of scaling in parallel execution plans.

For now, I can provide a short summary. The test platform is a Dell PowerEdge 2900 with 2 quad-core Xeon E5330 2.66GHz processors, and 24GB memory. The operating system is Windows Server 2008 64-bit for both 32 and 64-bit SQL Server versions. Technically SQL Server 2000 is not supported, but this is just a performance comparison, not a production environment. The database is generated using the TPC-H dbgen kit for scale factor 10, meaning the Lineitem table is approximately 10GB, and the entire database is approximately 17GB, which fits entirely in memory. There was some tempdb activity, which is spread across 10 15K drives.

All tests are run twice to load data into memory and pre-compile the execution plan for the second run. All results shown are for the second run. For SQL Server 2008, the tables use the new Date data type in place of Date Time, and queries are modified to avoid conversion anomalies as noted below. Below is the total (sum) CPU time in milli-seconds to execute the 22 queries in sequence for max degree of parallelism: 1, 2, 4, and 8.

DOP 1 DOP 2 DOP4 DOP 8

2000 RTM 534,912 663,848 656,232 697,794

2000 bld 2187 514,881 589,245 657,543 770,272

2005 RTM 32 463,526 444,479 456,567 498,623

2005 SP2 32 464,478 403,668 413,685 452,134

2005 RTM 64 379,363 377,570 394,962 474,200

2005 SP2 64 370,206 327,149 345,155 436,491

2008 RTM 375,136 324,264 343,250 410,220

Duration in milli-seconds to run 22 queries by max DOP.

DOP 1 DOP 2 DOP4 DOP 8

2000 RTM 553,900 293,411 191,552 149,568

2000 bld 2187 566,333 276,085 188,497 164,677

2005 RTM 32 480,839 237,933 134,644 84,721

2005 SP2 32 483,842 214,804 119,525 72,515

2005 RTM 64 379,563 194,199 107,409 65,094

2005 SP2 64 370,374 166,579 94,844 59,388

2008 RTM 375,135 171,390 94,028 56,795

On SQL Server 2000 build 2187, notice that CPU increases from 514.8 to 589.2 seconds going from Max DOP 1 to 2 and so on to Max DOP 8. This is expected because there is overhead to employing a parallel execution plan, and the overhead increases with the number of threads involved. Between SQL Server 2000 RTM and build 2187, there was a sharp jump in the CPU required at Max DOP 8. I will disregard this as there were significant changes and code fixes between the two builds concerning correctness of parallel execution plan results. Still, there is an overall performance gain from Max DOP 4 to 8. Several years ago, I mentioned that SQL Server 2000 performance is very problematic beyond Max DOP 4. That was before multi-core processors, and there were at most, 4 cores per NUMA node. So the more correct interpretation is that SQL Server 2000 is very problematic on NUMA systems. An earlier look at SQL Server 2005 RTM showed no such problems on NUMA.

In SQL Server 2005, and 2008, there is actually a decrease in CPU going from Max DOP 1 to 2. This is mostly attributed to the bitmap filter in hash operations. Some queries show a significant drop in CPU from DOP 1 to 2, others no change, and some an increase. From DOP 2 to 4 there is a slight increase in CPU and more significant in going from DOP 4 to 8. This might indicate that DOP 2 and 4 are very good for overall efficiency, benefitting from bitmap filters in hash join operations, yet without incurring excessive parallelism overhead. (This is unrelated to the recommendation of Max DOP 4 on Itanium systems based on cores per NUMA node). Unrestricted parallelism on the 8 core system yields the best single stream completion times, although this should really be tested on 16 or more cores before setting any rules.

In the transition from SQL Server 2000 to 2005 RTM, both 32-bit, the duration performance gain is a modest 15% for non-parallel plans and a very substantial 49% at Max DOP 8. From SQL Server 2005 32-bit to 64-bit, both RTM builds, the performance gain was a solid 20%. The CPU efficiency improvement was a little less, so the tempdb configuration affects the results. Even though the entire data fits in memory, a large query with intermediate results is more likely to spool to tempdb in 32-bit than 64-bit. From SQL Server 2005 64-bit RTM to Service Pack 2, an additional 10% was realized at DOP 2 and higher.

SQL Server 2008 RTM is marginally better than SQL 2005 SP2. There is significant variation from query to query, so improvements should be expected over time hopefully to correct the query plans that are slower while maintaining the performance advantage of plans that are better. One of the big disasters in 2008 parallel execution plans occurs on Query 5, Local Supplier Volume. The query is:

SELECT N_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION
WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY

AND L_SUPPKEY = S_SUPPKEY AND C_NATIONKEY = S_NATIONKEY

AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY

AND R_NAME = 'ASIA'

AND O_ORDERDATE >= '1994-01-01'

AND O_ORDERDATE < CONVERT(DATE,DATEADD(YY, 1, '1994-01-01'))
GROUP BY N_NAME

ORDER BY REVENUE DESC

The MaxDOP 1 plan is essentially:

SELECT N_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
FROM SUPPLIER
INNER JOIN (

SELECT N_NATIONKEY, N_NAME, L_EXTENDEDPRICE, L_DISCOUNT, L_SUPPKEY
FROM NATION
INNER JOIN REGION ON N_REGIONKEY = R_REGIONKEY
INNER JOIN CUSTOMER ON C_NATIONKEY = N_NATIONKEY
INNER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY
INNER JOIN LINEITEM ON L_ORDERKEY = O_ORDERKEY
WHERE R_NAME = 'ASIA'

AND O_ORDERDATE >= '1994-01-01'

AND O_ORDERDATE < CONVERT(DATE,DATEADD(YY, 1, '1994-01-01'))
) x ON L_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY
GROUP BY N_NAME ORDER BY REVENUE DESC
OPTION (FORCE ORDER)

At MaxDOP 1, the actual CPU is 21,965 ms for the original query,

the MaxDOP 2, the CPU is 28,721ms for the original.

MaxDOP 2 CPU for the forced query is 13,323.

So this one query added 15.4 CPU-sec to the total 22 query 324.3 CPU-sec,

close to 5%, and about 8.0sec duration.

Query 8 was also bad news on the parallel plans, with about 5 CPU-sec lost on the MaxDOP 2 parallel plan compared with a forced parallel plan modeled on the non-parallel plan. One might think that MS should have caught these anomalies. I think the reason they do not is that MS does not look at SF1-30 TPC-H results. The minimum for publication is 100GB, and that will probably increase to 300GB soon, because 30GB is not a real data warehouse. I do think MS should look very carefully at SF1-30. The queries are at the onset of eligibility for parallelism. The really big queries in SF100 and higher are less likely to encounter plan problems. While not strictly a data warehouse, most transactional databases I have seen do not remotely resemble TPC-C or E. I would say most have TPC-H SF1-10 sized queries mixed in with smaller transactions. So a bad execution plan can be really bad news.

I am sufficiently satisfied that SQL Server 2008 has a very powerful engine, and a decent optimizer. However, I have complained in the past about the rigid assumptions that all query costs factor in IO time, the use a fixed random to sequential IO performance model (320 IOPS to 10.5MB/sec) and an out of balance IO-CPU ratios. If a proper calibration of the true cost formulas were to be done, there would probably be fewer silly mistakes resulting in goofy execution plans. Given that many people do not know how to diagnose this type of problem, a simple test of 2000 or 2005 and 2008 can encounter this matter, leading to a decision to stay with 2000/2005, when a few simple adjustments would have corrected the 2008 results.

SQL Server Settings

Generally I follow the HP TPC-H publications on optimization settings, particularly -E and -T834. Neither changed results by more than 1% either way. I had also looked at -T2301 in the past finding no apparent differences. I really would like MS to provide more details on T2301. Are there set points below which it has no effect?

SQL Server 2008 new Date data type changes

The 3 datatime columns in the LineItem table from 2005 become Date columns, for an apparent savings of 12 bytes. The 2005 tpch SF10 database was 13.77GB (rather million KB) data and 3.68G indexes for a total of 17.46G. In 2008, using the Date data type in place of datetime, the size is 12.77 data and 2.96G index for a total of 15.74G. The average bytes per row of LineItem drops from 169 to 153, because one of the DateTime/Date columns was the cluster key.

Nornally, a simple reduction in size on column width, not row count, does not improve performance unless it impacts fit in memory. I always try to exclude this factor because one can generate any difference in performance by adjusting the amount of disk IO.

The original TPC-H queries may have SARG of the form

AND O_ORDERDATE >= '1994-01-01'

AND O_ORDERDATE < DATEADD(YY, 1, '1994-01-01'))

Even before SQL 2008, the date functions would return a datetime or smalldatetime result as appropriate. In SQL 2008, the nature extension is to return a date type when the comparison is a date column. I made this request in connect and was told to bugger off. So SQL 2008 will convert the column to date time to equate with the function, losing the benefit of a proper SARG. Anyone upgrading to SQL 2008 with the date type and not changing code as below may get a nasty suprise.

AND O_ORDERDATE >= '1994-01-01'

AND O_ORDERDATE < CONVERT(DATE,DATEADD(YY, 1, '1994-01-01'))

Little things like this can cause people to refuse to budge from SQL 2000, which really needs to be retired.

Duration for SQL 2008 64-bit

	1P D	2P D	4P D	8P D
Q1	50,013	26,317	12,591	7,159
Q2	504	268	150	107
Q3	16,296	5,186	3,158	1,902
Q4	19,232	5,288	3,452	2,340
Q5	21,648	16,150	8,371	5,120
Q6	1,845	929	496	312
Q7	17,397	4,369	2,388	1,376
Q8	5,734	6,765	3,628	1,849
Q9	48,361	22,034	11,335	6,372
Q10	15,281	5,822	3,595	2,425
Q11	4,423	1,238	657	600
Q12	9,363	4,828	4,356	2,365
Q13	21,699	11,310	5,751	2,967
Q14	2,146	1,033	547	334
Q15	1,368	970	521	249
Q16	6,599	3,615	2,018	1,848
Q17	1,243	521	294	213
Q18	50,909	27,945	15,439	9,365
Q19	2,096	1,093	607	378
Q20	841	430	255	165
Q21	69,191	22,064	12,826	8,337
Q22	8,946	3,213	1,592	1,010
	375,135	171,390	94,028	56,795

my apologies, Linchi post SQL 2005 64-bit results, so my duration results for SQL 2005 64-bit, SP2 (no cu) below

	1P D	2P D	4P D	8P D
Q1	64,761	32,553	16,428	8,344
Q2	504	295	158	106
Q3	14,733	4,782	3,003	1,989
Q4	17,506	5,338	3,747	2,519
Q5	19,716	7,376	4,654	3,159
Q6	1,609	893	471	309
Q7	15,855	5,472	3,306	2,403
Q8	5,225	2,391	1,333	2,147
Q9	44,611	23,291	12,213	7,222
Q10	13,989	6,384	3,934	2,754
Q11	4,093	1,192	669	495
Q12	8,166	4,497	4,022	1,714
Q13	25,830	13,566	7,521	4,260
Q14	2,060	1,020	526	352
Q15	1,358	1,931	1,139	235
Q16	6,476	3,476	2,429	1,215
Q17	1,012	524	291	199
Q18	46,954	26,156	13,896	9,209
Q19	2,133	1,172	623	450
Q20	830	446	253	172
Q21	64,231	20,850	12,536	9,087
Q22	8,722	2,972	1,692	1,049
	370,374	166,579	94,844	59,388

Is any one seriously looking at the HP ProLiant DL785G5?

jchang — Sun, 20 Jul 2008 16:22:00 GMT

Or the IBM x3950M2 8-way Quad-Core Xeon?

Consider the following specs:

8-way Quad-Core Opteron (32 cores total)

Max Memory: 512GB (64 DIMM sockets)

11 PCI-E slots: 3x16, 3x8, and 5x4 or option for 7 PCI-E and 2HTx

Compare this with the HP ProLiant DL585G5:

4-way Quad-Core Opteron (16 cores total)

Max Memory: 256GB (32 DIMM sockets)

7 PCI-E slots: 3x8, and 4x4

Aside from 8 Quad-Core processor sockets, the significant differences are 64 DIMM sockets, doubling the maximum memory of the 4-way, and 11 PCI-E slots (depending on the actual architecture, this could be 92 PCI-E lanes!)

Some pricing from the HP web site below.

ProLiant DL585G5

w/4x2.2GHz $10,389, 4x2.3GHz $12,189, 4x2.5GHz $16,189

64GB 32x2GB +$4,495, 128GB 32x4GB +$13,455, 256GB 32x8GB +$55,863

ProLiant DL785G5

w/4x2.2GHz $16,973, 8x2.3GHz $27,291, 8x2.5GHz $46,891

128GB 64x2GB +$7,612, 256GB 64x4GB +$26,492, 512GB 64x8GB +$75,316

8-way systems were somewhat popular in the Pentium III Xeon/ProFusion days, but Intel did not follow it with a respectable (read on when you stop laughing) 8-way chipset for the NetBurst based Xeon processors. HP did their own chipset for the DL740(?) which they considered moderately successful (in that it was profitable but did not warrant continuation for the next generation dual core processors).

The ProLiant DL785 posts a respectable TPC-H benchmark result of 52,860 QphH@300GB (SQL Server 2008), compared with 46,034 for the 8-way IBM x3950 with 8 Xeon 7350 (SQL Server 2005). The ProLiant DL585G5 also posted a top 4-way SQL Server TPC-C result of 471,883tpm-C on the AMD Opteron 8360 at 2.5GHz. I was really expecting that the AMD quad-core needed to be at 2.8GHz to reach this performance level.

Now getting an actual database (SQL Server or any other) to scale to 16 or 32 cores is not a simple matter. I do suggest conducting a proper quantitative scaling analysis, i.e., measuring maximum throughput with 1, 2, 4, 8, 16, and 32 cores.

The other reasons for going with the 8-way is the extra DIMM sockets. The 4-way QC 2.3GHz DL585 with 256GB memory (32x8GB DIMMs) is $68K, versus $54K for the 8-way DL785 with 64x4GB DIMMs (it is necessary to populate all 8 processor sockets to get 64 DIMM sockets, ~~but one could restrict SQL Server to 4 sockets if per processor licensing is involved,~~ see Andy's comment below). Even at 128GB, it is $26K for the 4-way DL585 with 32x4GB DIMMs versus $35K for the 8-way with 64x2GB. The extra 16 cores for a $9K delta is highly attractive in CAL situations.

One would also think that the 11 PCI-E sockets could support phenomenally high sequential disk transfer rates: 800MB/sec per first generation PCI-E SAS RAID controller, 1,100MB/sec+ for second generation (in x8 slot). But there is no published data on this for the AMD systems with PCI-E. The HP Itanium systems can do over 15GB/sec in SQL Server table scans.