There are two new TPC-H benchmark submissions on SQL Server 2016. This is interesting, because one of these new submissions (from March 9, 2016) is from Lenovo, for a System x3850 X6 running on SQL Server 2016. Lenovo has a previous submission, from May 1, 2015, for an identical model system running on SQL Server 2014. Both systems are running on Windows Server 2012 R2 Standard Edition. Both of these submissions are for 3000GB databases, which is very important when you are comparing score results.
So here are the results:
SQL Server 2016 969,504 QphH@3000GB
SQL Server 2014 700,392 QphH@3000GB
This shows a 38.4% score increase on identical hardware, which is quite impressive.
Both of these systems have four Intel Xeon E7-8890 v3 (Haswell-EX) 18-core processors, and 3 TB of RAM. Both systems have Intel HT enabled. Diving into the full-disclosure report for each submission, the storage subsystem for each of these submissions is virtually identical. For both systems, the storage is mostly flash-based, with a combination of internal drives and PCIe add-in cards (AIC). No SAN used here!
The key point is that they stored their six data files and their tempdb files across six, independent 3.2 TB PCIe flash AICs, which they describe as “3200GB Enterprise Value io3 Flash Adapter”. I believe that these must be SanDisk Fusion-io Memory SX350-3200 devices. Lenovo also describes the storage subsystem like this in the full-disclosure report:
The OS was stored on a RAID-1 protected array of 2 physical drives. The database files were
stored on 6 non-raided Enterprise io3 Flash drives. The log was stored on a 4-disk Raid10 array.
One thing I noticed was some minor inconsistencies between the Executive Summary and the FDR about the storage subsystem details for where the transaction log file is stored on the March 9, 2016 submission. I think this is just a copy/paste error, and log file performance is not important for this type of benchmark anyway.
Microsoft has been publishing a series of blog posts that outline some of the performance and scalability improvements in SQL Server 2016 on the CSS Engineers blog.
4 thoughts on “New TPC-H Benchmarks Comparing SQL Server 2016 to SQL Server 2014”
If the hardware truly holds to be equal that amount of improvement would be stunning to say the least!! I would love to see some indications about what changes came into play in making it almost 40% faster.
Kevin, I would not know how to read a TPC report if Glenn spelled it out to me, but based on what I have heard and seen from SQL 2016, I think that most of this performance was gained by using the improved and extended functionality of in-memory table and the improved and extended functionality of columnstore indexes – probably in combination.
Glenn Looks like from device list they may have used IBM io3 Flash Adapter:
Name IBM io3 Flash Adapter
Manufacturer IBM
Status OK
PNP Device ID PCI\VEN_1AED&DEV_3002&SUBSYS_04D31014&REV_00\4&D7F1D0A&0&0018
Memory Address 0xFBF80000-0xFBFFFFFF
Memory Address 0xFBF5E000-0xFBF5FFFF
Memory Address 0xFBF60000-0xFBF7FFFF
IRQ Channel IRQ 4294967195
IRQ Channel IRQ 4294967194
IRQ Channel IRQ 4294967193
IRQ Channel IRQ 4294967192
IRQ Channel IRQ 4294967191
IRQ Channel IRQ 4294967190
IRQ Channel IRQ 4294967189
IRQ Channel IRQ 4294967188
IRQ Channel IRQ 4294967187
IRQ Channel IRQ 4294967186
IRQ Channel IRQ 4294967185
IRQ Channel IRQ 4294967184
IRQ Channel IRQ 4294967183
IRQ Channel IRQ 4294967182
IRQ Channel IRQ 4294967181
IRQ Channel IRQ 4294967180
Driver c:\windows\system32\drivers\iomemory_vsl4.sys (4.1.2.428, 917.62 KB (939,640 bytes), 12/22/2014 9:36 AM)
There is also some performance gain from being able to tack on some NCIs on a CCI table.
Glenn
@Hugo, are you saying that you suspect they used the same hardware and OS but made changes to the database schema to make use of better performance or are you saying that the gain of about 40% will be useful to in-memory & colstore features exclusively and won’t show up in case you are not using in-memory? How can you know?