Adaptive Query Processing – SQL 2017 and Above

Before SQL Server 2017, the behavior of the SQL Server query processing engine was to analyze the query first, create the plan and then execute it. If the plan was somehow not appropriate, the query-processing engine was not able to change it while executing the query or even after it. Sometimes the query execution plans made by SQL Server are not
appropriate.

During query processing and optimization, the cardinality estimation (CE) process is generally responsible for approximating the number of rows processed at each step in an execution plan. Inaccurate estimations can result in slow query response time, unnecessary resource utilization (memory, CPU, IO), and reduced throughput and concurrency. There are number of reasons behind poorly designed execution plan.

• Lack of appropriate indexes
• Outdated statistics
• In-appropriate query execution plans cached with outdated values are stored
• Poorly written codes

Above reasons can mislead SQL Server optimizer to choose inefficient physical joins which will eventually create a bad execution plan.

There are two ways to fix these problems.

• Provide more relevant and accurate information about the query
• Re-write the code so that it may perform in a better way.

The Adaptive Query Processing was added in SQL Server 2017 as an alternative to the above traditional Query Processing.

Adaptive Query Processing

To improve the Cardinality Estimator process, SQL Server offers a feature family called Adaptive Query Processing (AQP) in SQL Server 2017 and above. AQP makes SQL Server significantly faster at processing workloads by allowing the query processor to adjust query plan choices based on run-time characteristics. AQP breaks the barrier between query plan and actual execution. Optimization can be done while the query is executing or even after execution is complete, which benefits subsequent query executions. AQP offers three techniques for adapting to application workload characteristics:

• Batch mode memory grant feedback.
• Batch mode adaptive joins.
• Interleaved execution for multi-statement table-valued functions.

Batch Mode Memory Grant Feedback

For a query’s post-execution plan, SQL Server takes a cardinality estimate for a given SQL batch and estimates the minimum memory grant needed for execution as well as the ideal memory grant needed to hold all rows of the batch in memory. If there are problems with the CE, performance suffers and available memory is constrained. Excessive memory grants result in wasted memory and reduced concurrency. Insufficient memory grants cause expensive spills to disk.

With batch mode memory grant feedback, SQL Server recalculates the actual memory required for a query and then updates the grant value for the cached plan. When an identical query statement is executed, the query uses the revised memory grant size. Performance is improved because there are fewer spills to tempdb and, because memory grants to batches are more accurate, additional memory can be provided to the batches that need it most.

This feature basically enables the SQL Server Query Processing engine to learn that if the memory grants are not sufficient then the engine will change the cached execution plan and update the memory grants so that the later executions should benefit with the new grants.

Batch Mode Adaptive Joins

SQL Server typically chooses among three types of physical join operators: nested loop joins, merge joins, and hash joins. Each type of join has strengths and weaknesses, depending on the characteristics of the data and query patterns. Which algorithm is best to use in each query depends on the cardinality estimates of the join inputs. Inaccurate input CE can result in the selection of an inappropriate join algorithm.

With the batch mode adaptive joins feature, SQL Server enables you to defer the selection of a hash join or nested loop join method until after the first input has been scanned. The adaptive join operator defines a threshold that is used to decide when to switch to a nested loop plan. Consequently, a plan can dynamically switch to a better join strategy during execution.

Interleaved execution for multi-statement table-valued functions

Multi-statement table-valued functions (MSTVFs) are popular among developers although their initial execution can cause performance slowdowns. With the help of AQP, SQL Server resolves this issue through the interleaved execution for MSTVFs feature. This feature changes the unidirectional boundary between the optimization and execution phases for a single-query execution, and it enables plans to adapt based on the revised CE. With interleaved execution, the actual row counts from the MSTVF are used to make plan optimizations downstream from the MSTVF references.

SQL Server Query Optimizer learns that if the estimates are way off than the actual ones, it adjusts the execution plan by actually executing a part of the query execution plan first and re-design the Query Execution Plan based on the actual amount of the rows. This  reads to a much better plan, which is created and adjusted while the query is executing.

The result is a better-informed plan based on actual workload characteristics and, ultimately, better query performance. When an MSTVF is encountered, the query optimizer will take the following actions:

• Pause optimization.
• Execute the MSTVF subtree to get an accurate CE.
• Continue processing subsequent operations with an accurate set of assumptions.

Based on the execution results (estimated number of rows), the query optimizer can consider a better plan and execute the query with the modified plan.In general, the higher the skew between the estimated and actual number of rows—coupled with the number of downstream plan operations—the greater the performance impact. Interleaved execution benefits queries where both of the following are true:

• There is a large skew between the estimated and actual number of rows for the intermediate result set (in this case, the MSTVF).
• The overall query is sensitive to a change in the size of the intermediate result. This typically happens when there is a complex tree above the subtree in the query plan. A simple “SELECT *” from an MSTVF will not benefit from interleaved execution.

 

Please leave your comment if you have any query or feedback.

 

Advertisement