This post is also available in: esEspañol (Spanish)

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.

Test performed

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.

Hardware used

We did not use any special machines, only my VM running Hyper-V on Windows 10 from my laptop. We presented the following hardware

  • CPU
    • 8 x i7-4710MQ virtual cores @ 2.5Ghz
  • Drive
    • 1 x ssd Samsung EVO 840
  • OS
    • Windows Server 2016
  • DB
    • 62Gb
    • 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”:

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:

SQL2014

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.

To these:

SQL2016

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.

Enrique Catalá

Enrique Catalá es Mentor en SolidQ, Microsoft Data Platform MVP desde 2013 e Ingeniero en informática. Es Microsoft Certified Trainer (MCT) y está centrado en el motor relacional SQL Server, tanto en la resolución de problemas de rendimiento y escalabilidad en sistemas OLTP como la definición e implementación de entornos de alta disponibilidad confiables, en donde ha llevado con éxito más de 100 proyectos no solo en España, sino en diferentes países como EEUU, Holanda, México, Arabia Saudí, Austria... Es el arquitecto principal de las soluciones para SolidQ llamadas HealthCheck, SQL2Cloud, SCODA y del generador de SSIS de SolidQ. Ponente habitual del SolidQ SUMMIT, miembro y ponente en SQL PASS tanto en España como Iberoamérica ponente en varios SQLSaturday