Home Applications Microsoft: SQL Server 2016 – No Performance Cliff

Microsoft: SQL Server 2016 – No Performance Cliff

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

Microsoft Founded in 1975, Microsoft (Nasdaq “MSFT”) is the worldwide leader in software, services, devices and solutions that help people and businesses realize their full potential.

Featured Resources:

Related Articles:


White Papers

    Application Lifecycle Management with Stratusphere UX – White Paper

    Enterprises today are faced with many challenges, and among those at the top of the list is the struggle surrounding the design, deployment, management and operations that support desktop applications. The demand for applications is increasing at an exponential rate, and organizations are being forced to consider platforms beyond physical, virtual and cloud-based environments. Users […]


      Download Commvault VM Backup and Recovery: end-to-end VM backup, recovery and cloud management

      Commvault’s ability to provide end-to-end VM backup, recovery and cloud management creates a significantly better way to build, protect and optimize VMs throughout their lifecycle. Our best-in-class software for VM backup, recovery and cloud management delivers a number of significant benefits, including: VM recovery with live recovery options; backup to and in the cloud; custom-fit […]

      On-Demand Webinars

        What’s Going on in EUC Printing – A Technical Deep Dive!

        The IGEL Community and ThinPrint invite you to watch the following technical deep dive webinar. The agenda is to technically bring you up to speed on what’s going on in the EUC Printing space today along with a deep dive into new methods, technologies, printing scenarios and a discussion on why printing still matters. You […]

        Latest Videos

          Views All IT News on DABCC.com
          Views All IT Videos on DABCC.com
          Win big $$, visit ITBaller.com for more info!

          Visit Our Sponsors