data-konzept

'There are a number of interesting questions in parallel execution performance that can be investigated using the TPC-H kit, which has a data generator. There are also supplemental files that accompany published results. Important questions in parallel execution are: scaling versus degree of parallelism (DOP) on physical cores, Hyper-Threading, Compression and Columnstore. (Memory optimized tables support parallel plans in 2016? perhaps in a later post.) To be clear, the TPC-H data generator (dbgen) and queries are used here, but no attempt is made to adhere to the TPC-H benchmark requirements (there are some outright departures). The material reported here are only for purpose of studying parallel execution characteristics.

In brief, the findings are as follows. The sum average performance gain from Hyper-Threading over the 22 TPC-H queries is 23%, in line with characteristics observed in SQL Server version 2012. (Official TPC-H results use a geometric mean.) There used to be an anomalous behavior on SQL Server 2012 in which performance was better with HT enabled and the second logical processor idle over HT disabled, even for single-threaded execution as reported in Hyper Threading. Compression has an average overhead of 20% across all DOP levels. For SQL Server 2008, I had reported in SF 10 that compression overhead was 40% between DOP 1-8, then decreases to less than 10% at DOP 32. The test circumstances were different, and may have been related to the NUMA effects of an 8-way system. Columnstore performance is excellent and should be considered whenever practical.

• Hyper-Threading performance boost 23% in parallel DW queries

• HT anomalous performance gain in version 2012 at DOP 1 no more

• Page compression overhead 20%, down from 40% in 2008

• Columnstore should run one per physical core, HT can be enabled'...

http://sqlblog.com/blogs/joe_chang/archive/2017/02/01/parallel-execution-on-sql-server-2016.aspx