We are announcing the official public preview of compatibility level 140 in Azure SQL Database.
Compatibility level 140 enables the following query optimizer changes:
- A trivial plan referencing Columnstore indexes will be discarded in favor of a plan that is eligible for batch mode execution.
- The sp_execute_external_script UDX operator is eligible for batch mode execution.
- Three adaptive query processing features are being introduced:
- Batch mode memory grant feedback, which improves the performance of repeating queries that request too much or too little memory.
- Batch mode adaptive join, which is a new query operator type that allows dynamic selection of the most optimal join algorithm based on runtime row counts.
- Interleaved execution, which improves the performance of queries that reference multi-statement table valued functions by using the true row count of the function call for use during query optimization.
Read about it on the Azure blog.