Deep dive into Adaptive Query Processing
This page contains the description for my conference session “Deep dive into Adaptive Query Processing”, and links to the slide deck and demo code used in this presentation.
Description
| Target audience | Experienced database developers and DBAs. |
| Short description | An overview of the new “Adaptive Query Processing” features that were introduced in SQL Server 2017: What are they, where do they help you, and what are the risks? |
| Duration | The ideal length for this session is 60 – 75 minutes. |
| Full abstract | Until SQL Server 2016, the Query Optimizer and the Execution Engine were strictly separated. The Query Optimizer produces an execution plan that, based on statistics and estimates, should be fast. That execution plan is then faithfully executed by the Execution Engine, even if reality turns out to be different from expectations.
SQL Server 2017 changes this! Three new features now allow execution plans to adapt to reality. Memory Grant Feedback increases or decreases assigned memory based on past experience. The Adaptive Join operator allows the optimizer to create two alternative plans, the best of which will be decided at execution time. And with Interleaved Execution, parts of the plan are even completely recompiled mid-execution, with much better cardinality estimates. If you are more interested in how all this ACTUALLY works than in shiny marketing slides, come to this session. We will spend the full session knee-deep in execution plan internals! |
Resources
Click here to download a ZIP file containing the slide deck and demo code used in this presentation.
(Note: my presentations tend to evolve over time, so the version you find here may not be an exact match for the version you witnessed)