Table Scan - Aggregate Cost Structure
In the previous post, I mention that in an earlier post on IO cost structure, that the cost of a table scan was approximately 1 CPU-micro sec per page, plus approximately 0.05 us per row for just a SELECT COUNT(*)
This was calibrated on a 2-way Xeon 5430 Quad-Core 2.66GHz. I should also add that the test tables have a clustered primary key, with between 3-18 columns, all fixed length not null, varying row density per page from 1 to 323. Without hints, the default locking level should be TABLOCK (?) and Max Degree of Parallelism set to 1.
I just looked a table scan query to the TPC-H LineItem table at SF10 and saw significant differences. At SF10, using the SQL Server 2008 table, ie, DATE type instead of DATETIME, there are 59,986,052 rows, and 1,127,206 leaf level pages, averaging 53.2 rows per page. By my formula, I would expect the SELECT COUNT(*) FROM LineItem with a forced clustered index scan would cost around 3.5 micro-sec per page, instead I observed 5.8 micro-sec per page.
So why is the scan on LineItem much more expensive than the calibration from my tests? Is it because the LineItem table has 1 varchar(44) column? or because the LineItem clustered index is not unique? I need to find out, as you heard what I said about people who speculate, without investigation.
So additional information on aggregation: SUM(float) costs the same as SUM(int), there is nearly no difference between SELECT COUNT(*), SUM(float) and SELECT SUM(float), so if you aggregate any column, the count is free. Anyways, the first SUM is about 0.20 micro-sec per row on top on the COUNT(*). A SELECT MAX() is just slightly less expensive than a SUM(), around 0185 micro-sec per row. Adding a second, third or fourth SUM() or AVG() contributes about 0.05 micro-sec per col aggregated. However, SELECT SUM(Col1), AVG(Col1) does not incur the extra column penalty, SELECT SUM(Col1), MAX(Col1) does incur the additional col cost.
thanks to Mario (http://sqlinternals.com/) for looking at what the SQL Server engine actually does in SELECT COUNT(*) FROM Table, and to Kevin who is big into what code paths should be,
and likes to count CPU-cycles (might be an indication of a lack of social life,
of course if chicks like to count cpu-cycles, I might get out more myself)
3 Billion cycles per second, and he still wants to nickel and dime the 133 per row.
http://kevinclosson.wordpress.com/
From Mario
So that's why I fired up my little debugger and did some tracing (on my AMD Athlon PC, win x32, running SS2005 SP2).
And I was surprised to find you are actually right.
SqlServer *does* touch every row, in order to optimize this process it does use hardware prefetching, and it scans the rows using the row lenghts starting from the bottom of the page.
This is for counting.
For a max or avg it drags in all the column data using memcpys, and applies max or avg functions, the actually referencing the data makes the story of course much more expensive. Here it depends if prefetching does what it should do, how long the memory latencies are..are they local (in NUMA cases), and how costly the cache coherency process behind this is.
So the cost of accessing data depends also (much) on the architecture of your box and the amount of sockets. And not only what you measure using on cpu. But since you say that you are damn careful and this is not your first day doing cpu measurements, i guess you are aware of all that..
________________________________________
the parts that I highlighted above imply that scaling is not linear, ie, a test on one processor, or with OPTION (MAXDOP 1) does not scale with the number of processors, either using a single query with a parallel execution plan, or from multiple concurrent non-parallel execution plans.
Dedicated Network Adapter(s) for transferring SQL backups to the tape archival system
One thing that really surprises me is how few people configure dedicated network adapters/ports (and preferably multiple adapters) for copying the SQL Server backup to the server with the tape archival system. The common reason cited by a really naïve system admin is that the percent network utilization as shown in task manager or perfmon never goes above 40-50%. Basically, this is a really worthless counter highly subject to misinterpretation with serious consequences in transaction processing environments. What it comes down to is that the network traffic generated by the backup file copy over the same network used by transaction processing can be highly disruptive even at 20-30% network utilization.
Think about this. On a gigabit Ethernet link, which has been standard for the last few years, a single file copy (or SQL Server backup to a network location) can generate 40-50MB/sec network traffic, assuming good disk system on both the SQL Server and destination and a clean network. Almost everyone is on the default Ethernet frame size of 1500 bytes. This means there can be approximately 30,000 packets per sec sent from SQL Server, and 15,000 packets per sec received. Running perfmon on the SQL Server might show only 4,000-5,000 packets per sec sent. This is because the Windows Operating system presents a very large packet size, which is what the performance counters record, that is broken into 1500 byte packets by the network adapter.
Even though this is not 100% network utilization on Gigabit Ethernet, it is highly disruptive to other activity on the network, especially transaction processing which requires a highly responsive SQL Server.
The Windows default is: for every 2 packets sent, it must wait for an acknowledgement before the next packet is sent. If the network round-trip is 67 micro-sec, then 15K pairs can be transmitted and acknowledged per sec. For a two-transmit, 1 acknowledgement policy, and 1500 bytes per packet, it would require a 30 micro-sec round-trip to achieve the full 100% (or 100MB/sec) network utilization. In the past I tried setting the TCP/IP registry settings for 4-8 window size, but had trouble getting this to behave properly. Large Internet Packet (9000 bytes per frame) does help achieve higher network utilization. It helps to have matching network adapters at both ends, and a gigabit switch that also supports LIP. Configuring LIP might cause issues with the Web/App servers, so I strongly recommend configuring LIP only on the NICs dedicated for backups.
There is no reason today to buy a single port Gigabit NIC with x4 PCI-E slot. So always add dual-port Gigabit NICs. Technically a quad-port NIC would be a good match for a x4 PCI-E slot. Per my storage configuration discussions, every x8 PCI-E slot in a server system should be populated with a disk IO controller (dual x4 SAS or dual port 4Gbit/s FC), and possibly 1 additional controller in a x4 slot. The remaining x4 PCI-E slots should then be populated with dual-port NICs. Now it is not necessary to have each gigabit port be connected to a different dedicated switch. Most gigabit Ethernet switches should have sufficient backplane bandwidth the handle the full saturation traffic of several Gigabit ports. But this should be tested.
It is necessary to assign each port a different network part of IP address. See Thomas Grohser website on this: http://www.sqlserver-hwguide.com/ I will also add that important environments should really be handled by a system/network admin familiar with configuring multiple networks ports on a server.
Early Intel Nehalem Xeon 5500 performance results
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)
DL580G5 4 Xeon 7460 Six-core 2.66GHz 16M L3, 256GB 634,825
TPC-E
Fujitsu RX300 2 Xeon X5570 Quad-core 2.93GHz, 8M L3, 96GB 800.00
x3650M2 2 Xeon X5570 Quad-core 2.93GHz, 8M L3, 96GB 798.00
Dell T610 2 Xeon X5570 Quad-core 2.93GHz, 8M L3, 96GB 766.47
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
First Nehalem TPC-H
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)
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.
Run-time Execution Plan Options
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
OPTION (OPTIMIZE FOR @P1 = 1, @P2 = ‘A’, …)
New:
OPTION (OPTIMIZE FOR @P1 = 1 or 2, @P2 = ‘A’ or ‘B’, …)
The presumption above is that @P1 values 1 and 2 have drastically different distributions that really need separate execution plans. So the SQL Server engine would generate a plan for each option. At run time, depending on whether the input parameter has distribution closer option 1 or 2, the appropriate plan is selected. Of course, this is a rather sophisticated hint that should not be used by anyone without detailed understanding of the execution plan. Also consider that as data distribution changes over time (or on statistics recomputed) the Optimize For values might be rendered ineffective. The SQL Server optimizer could also check whether each Optimizer For option actually leads to a significantly different plan, and discard irrelevant options.
Microsoft might take the position that statement level recompile is the right solution for this. My argument only stands if the cost of recompile is high relative to the cost of execute for the desired plan. So maybe an alternative is I want to use the OPTIMIZE FOR in the low row count scenario, but a RECOMPILE if the actual parameter value has high row count.
Correlated Column Statistics
I posted this a comment in another blog, but am moving here. I do not consider a top issue, but desirable. The reason SQL Server needs correlated column statistics (it is in at the vector level, but not at the histogram).
Consider the query:
SELECT * FROM Women
WHERE Attractiveness > 9 AND MonthlyShoppingExpenses < $3000
Or:
SELECT * FROM Men
WHERE FinancialStability > 9 AND Fidelity > 9 AND IsSingle > 50%
The individual column statistics may indicate 10% on each column condition, leading one to think that the combined AND conditions yields 1%, which will still have a respectable row count output. But in fact, if correlation is taken into account, a drastically lower row count is the result. Yes, I know: get a life, and I have been called MCP before the MS had the MCP program.
Additional Items
Index Seek with Key Lookup versus Table/Clustered Index Scan
In previous blogs, I also complained about the static approximate 4:1 (1350:320) ratio in table scan versus index seek followed key lookup. That is, the cross-over point from an index seek with key lookup to table scan occurs when the number of rows requiring key lookups reaches approximately one-fourth the number of pages. SQL Server 2008 has the hint OPTION (TABLE HINT (table,FORCE SEEK)), which pushes the index seek with key lookup regardless of the number of rows.
I commented that this was rather like giving us a sledge hammer when a chisel was preferred. My preference is to have a hint that changes the cross-over ratio from 4:1 to 1:1.
Hash and Sort spill to tempdb
Back when SQL Server 2005 was in late beta, I looked at SQL Server 2000 and 2005 execution plans with Hash (Join and Match) and Sort operations. I noted that the point at which hash and sort operations incurred an IO cost was function of the size of the intermediate table and system memory. Naturally AWE memory in 32-bit system does not count, i.e. only directly addressable memory counts. I did not verify that this was the actual point at which a hash or sort spooled to tempdb, and I did not notice any performance issues on my systems with adequate storage performance. Of course, many people have really crappy storage performance. Should there be a tunable parameter for handling this? Especially now that full 64-bit systems with huge (128GB) memory (and crappy storage performance) are common? Or does this fall under matters beyond the power of Microsoft?
Default Settings for Cost Threshold for Parallelism and Max Degree of Parallelism
Oh yeah, probably a tier 2 item. It is high time Microsoft change the default settings for these. A default of 0 or all available is potentially silly and severely adverse on systems with multi-core processors. The current cost threshold for parallelism of 5 is also really silly. I recall seeing queries that run in 50ms (non-parallel) get a parallel plan. This is way too low considering the overhead of setting up parallel execution. At minimum, the cost threshold should be recalibrated so that parallelism is not initiated until true CPU (on Core 2 or later architecture) is around 300-500 milli-sec. This is almost getting like how Oracle left the default buffer cache setting to 8-16MB, when system memory typically reached GBs, and the "DBA" neglected to change the default.
Multi-level Cost Threshold and Max Degree of Parallelism
I am thinking this should really be a bi-level or even multi-level setting. That is, a plan cost of 50-1000 allows MAXDOP 2-4, and plan cost 1000-10000 targets MAXDOP 8-16, and so on. (I am winging these numbers. If considered, then I can a more serious study on proper values.)
New Parallel Execution Plan Formulas
I have previously griped about the formulas used in parallel execution plans (OK, so you name it, and I have complained about it!). Anyways, the current execution plan cost model is preponderantly IO weighted, and IO costs are not reduced in parallel execution plans (except for large hash and sort operations). This has the effect of inhibiting parallel execution when in fact parallel execution would be beneficial, especially when almost all hot data is memory, so the IO argument does not hold. One more complaint/request. The bitmap filter operation in large parallel hash operations really help. In some cases, the parallel operation must be inhibited, but it would still be nice to employ the bitmap filter, even though large hash operations should be parallel.
Nehalem, Shanghai, and Dunnington performance notes
Back in March 2008, I discussed the SAP SD 2 Tier results for some AMD Opteron and Intel Xeon systems. Here are some more recent results. All Opteron and Xeon processors below are on the 45nm process except for the Opteron 8360, which is a 65nm product. For some reason, HP has not posted a 4-socket result for the 45nm Opteron 8384, where the bigger 6M L3 cache in known to improve network round-trip performance. The Itanium is a dual-core 90nm product, which is at serious disadvantage (to be replaced Tukwila, a 65nm quad-core). Note that the Xeon 7460 is a six core processor. My expectation is that the 4-way Opteron 8384 should be around 22-23K which would be very competitive for a quad-core going against a six-core.
System Processors Users SAPS
DL785G5 8 x 2.7GHz Opteron 8384 7,101 35,400 (O10,Lin)
DL580G5 4 x 2.66GHz Xeon X7460 5,155 25,830 (S2K5)
DL585G5 4 x 2.5GHz Opteron 8360 3,801 19,020 (S2K5)
DL380G6 2 x 2.93GHz Xeon X5570 4,995 25,000 (S2K5)
DL385G5 2 x 2.7GHz Opteron 2384 2,752 13,780 (S2K5)
DL380G5 2 x 3.33GHz Xeon 5470 2,518 12,600 (S2K5)
BL860C 2 x 1.66GHz Itanium 9140M 501 5,850
Of special note is the exceptional result for the 2-socket Xeon X5570, based on the new Nehalem processor, expected to be available some time in the first half of 2009. This is not entirely unexpected. First, the integrated memory controller probably helps in network round-trip intensive operations. Second is the return of Hyper-Threading, as Nehalem was designed in Oregon, while the Core 2 architecture is an Israeli design (each design team has their own opinion of various micro-architecture features). On the last Oregon design, the Pentium 4 NetBurst core, I noted that Hyper-Threading improved network round-trip performance by 15-20%, but did not actually improve any SQL operation outside of the network round-trip. Later, I measured 40-50% performance gains for HT on LiteSpeed backup compression tests. I have heard that while the theory behind HT is sound, certain operations such as acquiring locks (at the C/C++ level) can cause problems. The compression algorithm has no such issues, and probably represents the upper bound on what could be achieved with HT. Supposedly the Itanium HT, which was introduced with Montecito after the last NetBurst, had some improvements over NetBurst. Now that the Oregon team has had 8+ years to investigate HT characteristics, we should expect a much improved HT with Nehalem.
My expectation is HT has the biggest benefit in SAP type environments, i.e. stored procedure calls that retrieve a single row (50-100 CPU-micro-sec), moderate benefit in TPC-C and E type environments (on the order of 1-3 CPU-milli-sec), and less or no benefit in large TPC-H type queries. When I get the Nehalem Xeon 55xx system, I will look into this.
Unisys to focus on Xeon over Itanium?
On a side note, see the article regarding Unisys.
http://news.cnet.com/8301-13924_3-10167332-64.html?part=rss&subj=news&tag=2547-1_3-0-20
Unisys just posted a 10TB TPC-H result for a 16-socket Xeon X7460. While the X7460 has six cores, Unisys only enabled four cores per socket, in part, because the current version of Windows and SQL Server only support up to 64 cores. The result is 26% higher than for a 32-socket 64 core Itanium 2 system. The presumption is that later this year, Intel will release the quad-core Itanium, codename Tukwila, so this result might be representative of 16-socket systems in late 2009. Even if Tukwila can achieve 2.0GHz, it will probably just be comparable to the X7460. After Windows Server 2008 R2 releases, the 16-way X7460 will have all 72 cores available for the through-put portion of TPC-H.
64-core TPC-H 10TB
Xeon X7460 Six-core 2.66GHz (Dunnington, 4 cores used) 80,172.7 (ES7600R)
Itanium 9140N Dual-core 1.6GHz (Montecito) 63,650.9 (Superdome)
4 x quad-core Shanghai 2.7GHz, 6M L3 635.43 tpsE
SSD Test Platform
Anyway, I am all set to buy the new 2-socket Xeon 5500 series as soon as one becomes available. I will look into Nehalem performance relative to Core 2, with and without HT. I will try to configure this system with 2 PCI-E SAS RAID controllers and 8 SSD (2 per x4 SAS port, 4 devices per controller) initially, and then expand to 4 RAID controllers with 24-32 SSD as budget allows (probably 3 SSD and 1 HDD per x4 SAS port). I should also get a Shanghai platform as well, as my last good numbers for Opteron are now very old, but this is my own money. Business is down with the economy, and too many consultants are dropping their rates to get business. I am not inclined to do so. So I should have time to bring my past performance papers, many of which pertain to SQL Server 2000, up to date. I will also try to re-release some of my performance tools like SQL Clone on www.qdpma.com. I should also be able to release new tools, one for Profiler Trace analysis and another for performance tuning using dm_db_index_usage_stats and dm_exec_query_stats.
Big Iron Revival, Intel Nehalem EX and AMD Magny-Cours
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 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.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?).
