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.

But this is changing! SQL Server 2017 introduced three new features that allow execution plans to adapt to reality: Batch Mode Memory Grant Feedback, Batch Mode Adaptive Join, and Interleaved Execution. And even more of these adaptive features are already available as preview in Azure SQL Database.

In this session we will look at these new features. We will skip the shiny marketing slides, and instead look at how all of this ACTUALLY works. If you are happy to spend your time knee-deep in execution plan internals, come to this session!

You will learn:

• Understand how batch mode adaptive join works, and what its pros and cons are
• Understand how batch mode memory grant feedback works, and what its pros and cons are
• Understand how interleaved execution works, and what its pros and cons are