The next release of Microsoft SQL Server, announced as SQL Server 2017, is available for download in CTP2 at http://tinyurl.com/SQLvNextLinux and offers many cool new features. One set of features that I really like focus on adaptive query processing. These new features speed processing of workloads, especially those that have historically been the hardest to tune.
How It Used To Be
Prior to SQL Server 2017, query processing was a monolithic process. The SQL Server query optimizer would first construct a query plan for a batch of T-SQL code, a stored procedure, or function and then submit that plan for processing in a way that once started, it had to run to completion. (There are certain nuances that could cause a recompile before finishing, but that is a bit outside of the scope of this discussion).
The quality of that query plan is wholly dependent on the quality of the cardinality estimates (i.e., an estimate of the number of rows of data involved in an operation), the order of the operations, such as joins, and the physical algorithms used to process the query, such as whether SQL Server spills an intermediate worktable totempdb.
Due to a few factors, such as inaccurate cardinality estimates, the plan quality might be quite poor. A follow-on effect of poor plan quality is that SQL Server might grant far too much physical resources, like CPUs or memory, to the query, in turn meaning that other queries might not get as much of those resources as is optimal. Hence, overall performance suffers, in addition to the performance of that individual query with the poor plan quality.
How It Is Going To Be
SQL Server 2017 fixes the plan quality issues of previous releases via three new features: interleaved execution for multi-statement table-valued functions (TVFs), batch-mode execution adaptive joins, and batch-mode memory grant feedback.
Here’s what they do:
• Interleaved execution for multi-statement table-valued functions (TVFs): In older releases, SQL Server was notorious for having a hard time creating a high quality plan for code that included a TVF. SQL Server would make assumptions about the cardinality estimate of a TVF, frequently assuming wrongly. As a result, the entire query would perform poorly. This is an important problem because TVFs have widespread popularity among developers. Not so with DBAs! In SQL Server 2017, when the query optimizer encounters code with a multi-statement TVF, the query optimizer will pause optimization, execute the TVF subtree to get a very accurate cardinality estimate, and then continue processing of subsequent operations with an accurate set of assumptions.
This interleaved execution means that workload performance issues due to heavy use of TVFs is fixed for free by upgrading to SQL Server 2017. There are some restrictions in the first iteration of the feature. First, the TVF must be read-only. Second, the TVF cannot be used inside of a CROSS APPLY operation. Aside from those restrictions, running the code in compatibility level 140 (the default for SQL Server 2017) will automatically improve use interleaved execution where possible.
• Batch-mode execution adaptive joins: SQL Server typically chooses between three types of physical join operators: nested loop joins, merge joins, and hash joins. Each type of join has strengths and weaknesses. In earlier versions of SQL Server, after selecting a join algorithm, SQL Server had to process it even if a better alternative might be available.
In SQL Server 2017, the query optimizer can now sense a bad join choice in a plan and dynamically switch to a better join algorithm during the execution of the code. This addresses some common performance issues. Here’s an example. A parameter-depending query might need to use a nested loop join when the parameter value indicates a small number of rows are required, but a different parameter value might indicate that a hash join would be better because 100x more rows are required than with the other parameter. Now, SQL Server can dynamically switch between the two types of joins. Note that adaptive joins always choose between nested loop and hash joins. Merge joins are not currently part of the heuristics.
• Batch-mode memory grant feedback: Previously, SQL Server took a cardinality estimate for a given T-SQL batch and then estimated the minimum memory grant needed for execution as well as ideal memory grant needed to hold all rows of the batch in memory. It there were any problems with the cardinality estimate, performance suffers and available memory is constrained. Now, SQL Server 2017 recalculates the actual memory required by the batch, updating the grant value in the cached plan. When identical batches are later executed, they used the revised and better memory grant size. Performance speeds up because less there are fewer spills to tempdb and memory granted to batches are much more accurate, providing more memory to the batches that need it most.
The three features of adaptive query processing can make SQL Server 2017 significantly faster at processing your workload. Not only that, but they also provide significant improvements without needing to refactor your T-SQL code. I strongly encourage you to begin benchmarking your current workloads on the CTP today. I wouldn’t be surprised if you saw improvements from 15-25% in overall processing speeds on a busy SQL Server.
Read more about interleaved execution, adaptive joins, and batch-mode memory grants at https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04.
Have you benchmarked performance of your application against SQL Server 2017? If so, please share your findings with me!