SQL Server Query Optimizer performs a very good job in the generation of execution plans. However, due to query complexity, skewed data distribution, suboptimal database design and badly written code, it makes sometimes very bad cardinality estimations that lead to slow performing execution plans. Because of wrong estimations, it can choose inappropriate plan operators (for instance Nested Loop Join instead of Hash Join), or a query can get significantly more or less memory granted for the execution than it is required. Sometimes, it simply assumes a fixed cardinality of 100 or 1.

SQL Server 2017 introduces query processing improvements that will adapt optimization and address the aforementioned issues. These improvements break the pipeline between query optimization and execution. For the first time, SQL Server 2017 executes a part of the query during the optimization process (without the OPTION (RECOMPILE) clause) and replaces a part of the execution plan that is stored in cache. The improvements are included in the feature and consist of three parts:

• Interleaved execution
• Batch mode adaptive memory grant feedback
• Batch mode adaptive joins