THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Quick performance check with latest patches

there is some degree of press hysteria, lawsuits etc. concerning the kernel vulnerabilities of Intel processors with out-of-order execution (which originates to Pentium Pro), so I did a quick check.

Earlier, I posted on testing with the TPC-H database and queries at scale factor 100 (in conditions that do not meet official TPC-H standards). The test system is a single socket Xeon E5 2630 v4, 10 core, 64GB memory, SQL Server limited to 32GB, 4 Intel P750 NVMe SSDs for storage. The baseline test was SQL Server 2016 SP1 (probably prior to SP1 cu1) The current test has latest Windows Server 2016 hotfixes and SQL Server 2016 SP1 cu7 build 4466

Sum run time of the 22 TPC-H queries at SF100 database, row-store, no compression was 0.7% longer than before, CPU was 0.2% higher. The total database size is 126GB, so there was considerable IO, mostly scans, but some key lookups.

Note TPC-H scoring is geometric mean, which means small queries need to scale as well

I use straight sum because I am more concerned about big queries, as I do not expect small queries to scale well at high DOP, if at all.

Individual query variation range from: Q2 43% longer, from 0.67 to 0.963 sec, there is a loop join, which would do 8K IO?

Q18 17.8% faster, from 51.5 to 42.3 sec, might be a different execution plan, could be due

There were 4 queries between 20-28% slower, but because Q18 was one of the big queries, its improvement washed out the high percentage impact of the smaller queries

From the description given of the fixes, my expectation is that performance impact will be seen in operations that involve kernel, which include disk and network IO. I would except loop joins and key lookups to be more impacted that scan IO because of the number of IO,

and also network round-trip intensive applications, i.e., the ones that people said not to do.

Edit (Jan 11)
never mind the above
I had recently set my test system to power save,
so in the proper configuration of power - performance (Edit Jan 12, this result was with turbo boost enabled, original was disabled)

linear sum, SQL Server 2016 sp1 cu 7 bld 4466 and OS patches vs sp1 base
9% faster, 12% more CPU efficiently
individual queries range from 24% faster to 0.3% slower.
there probably is a penalty in the recent fixes, but fixes since SP1 also made improvements?

Edit Jan 12
original test was done with turbo boost disable, above test was turbo boost enable, probably running at 2.4GHz versus 2.2 base
new test in (my best estimate) matching conditions except for hot fixes
latest SQL Server 2016 PS + cu about 1.7% faster, 7.6% lower CPU
some queries 5-9% slower, a few much faster - probably with better execution plan, hence not related to recent security issue

It does look like the ability to drive network round-trips is severely impacted. I seem to recall that a stored proc, 1 row index seek, from a 40GbE, 1 thread used to drive 11K round-trips per sec, now 7,900,
peak throughput used to be 270K/s, now 146K/s?
I need to find my old records to see if I can clear this up

Published Thursday, January 11, 2018 10:07 AM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog


Privacy Statement