SQL Server 2016 really represents a drastic change for the better in regards to general performance. And the proof is that, thanks to the engine changes, this is the first SQL Server version where Microsoft actively provides a widespread increase in performance levels by simply upgrading. But what impact can it have in your daily loads if you use columnstore indexes in SQL server 2014 datawarehouse? There was already an amazing increase in performance levels with SQL Server 2014 and the columnstore indexes. Is it worth upgrading to SQL 2016?
Well, as previously stated, there are many improvements in several areas. A few of these are achieved thanks to the engine itself and are directly transparent, i.e., it will go faster directly after upgrading as shown in the following video:
Summary post https://thomaslarock.com/2016/06/sql-server-2016-just-runs-faster/
But let’s focus on the task in hand…
Is it worth upgrading to SQL 2016 if I you are already using columnstore indexes in SQL Server 2014?
Well, so it has transpired that the SQL Server 2014 operators concerning columnstore indexes were not fully implemented, and the execution plan generator was also missing a series of applied optimisations as well. In a nutshell, your perfectly indexed tables with columnstore indexes in SQL Server 2014 are not fully up to speed, despite how quickly you think your queries are currently running.
This test is nothing but the daily execution of reporting queries used by a production customer to support their analysts. It comprises a real time system where operational response times are crucial. To be more specific, we are talking about a facts table with 234,617,736 rows.
We carried out 3 separate executions in each SQL Server version, including a DB restore in SQL 2014 EE SP2 and SQL 2016 SP1
- Cold buffer cache (as soon as the instance had started)
- Re-execution after the previous execution (warm cache)
- Execution without stored procedure after cleaning the execution plans cache (warm cache with compiling)
After measuring and calculating the improvement %, we empirically tested all results, by launching the entire load against SQL Server 2016 in order to validate the estimated SQL Server 2016 timeframes.
We did not use any special machines, only my VM running Hyper-V on Windows 10 from my laptop. We presented the following hardware
- 8 x i7-4710MQ virtual cores @ 2.5Ghz
- 1 x ssd Samsung EVO 840
- Windows Server 2016
- Facts table with 234,617,736 rows and CLUSTERED COLUMNSTORE INDEX
Analysing the results
Below we show you what happens when you run the same test against SQL Server 2014 and then repeat it on SQL Server 2016 just like that, without changing anything in particular.
With the SQL2014 as Mark1 and SQL2016 as Mark2, following are the performance increases related to the above mentioned test:
% performance increase for every KPI tested
And here are the top 9 queries launched during the test, sorted according to their performance increase with regards to duration:
% gain for each KPI in every executed query
Looking good, right? You will find that some queries had over 1,200% performance increase and remember, all we did was to backup the SQL 2014 database and restoring it in SQL2016.
In fact, the full execution of the entire process against SQL Server 2014 lasted approximately 2h 40m, while on SQL Server 2016 it only took 20 minutes.
Why are we seeing such amazing performance increases?
Well, this unbelievable performance increases are mainly the cause of a few “small details”:
- Batch operations implementation
- Execution plan generation optimisation when compared to COUNT(DISTINCT)
- Aggregate pushdown predicates
In this particular load, we benefited from the above improvements, but your load may benefit from other aspects.
This is what makes us go from this type of execution plans:
27 seconds, cold cache
Execution plan on SQL 2014
Every COUNT(DISTINCT) within the original query is translated as a different NESTED LOOP channel in SQL2014.
2 seconds, cold cache
Optimised execution plan on SQL Server 2016
Check the amount of rows read in the clustered index and the lack of nested loops
These differences are directly caused by the engine implementation. In SQL 2016, there is an explicit optimisation towards COUNT(DISTINCT) that prevents branching, in addition to the fact that all operators work in BATCHES (of around 900 rows each) rather than on a 1 by 1 basis, as in previous versions. These are some of the issues that make all the difference.
Is it worth upgrading to SQL Server 2016 if you have a datawarehouse that is constantly being queried?
YES, don’t hesitate any further!
At SolidQ, we have proprietary software that is able to make tests such as the above. Please contact me if you need to do something similar.
- Debugging applications against production data: obfuscation and GDPR - November 22, 2019
- SQL Server performance with Spectre and Meltdown patches - January 18, 2018
- Data Migration Assistant Helper - September 18, 2017