Microsoft: SQL Server 2016 – No Performance Cliff
SQL product team has made significant improvements in columnstore index functionality, supportability and performance during SQL Server 2016 based on the feedback from customers. This blog series focuses on the performance improvements done as part of SQL Server 2016. Customers will get these benefits automatically with no changes to the application when they upgrade the application to SQL Server 2016.
The examples in this blog series are based on AdventureWorksDW2016CTP3 database that you can download from here. For each example, I will run the query in SQL Server 2014 and then contrast that with SQL Server 2016 and to reinforce the point that you get performance improvements without requiring any changes to your query or the workload.
SQL Server 2014
In SQL Server 2014, BATCH mode Batch Mode execution mode was only supported when the query was executed with DOP > 1. The basic premise was that customer running big analytics query will run it on multi-core machines with high degree of parallelism. While this is all good, but imagine you are running your analytics workload on a Server with 16 cores and everything is running smoothly. Let us say the concurrent workload spikes, SQL Server can choose to decrease the DOP automatically. In the extreme case, it is possible that the queries get executed with DOP=1. As you can expect, running query single threaded will increase the query execution time proportionally. However, the impact on queries using columnstore index is a lot more severe because SQL Server 2014 will also switch to Row Mode execution. Its like double jeopardy – your query slowed down both because of single threaded execution as well as Row Mode execution. This is what I refer to as Performance Cliff, a sudden significant drop in the query performance.
Read the entire article here, SQL Server 2016 – No Performance Cliff – MSSQL Tiger Team
via the fine folks at Microsoft