An umbrella name for optimiser capabilities, among them adaptive joins, OPPO, interleaved execution and feedback on memory grants, that improve query plans automatically, usually without code changes. Raising the compatibility level of a database switches most of them on.
Also called IQP.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Intelligent query processing in context, with comparison tables and the common traps.
Terms in this definition
- OPPO
With compatibility level 170, the optimiser keeps more than one cached plan per query, chosen by whether an optional parameter is NULL, so either a seek or a scan can be used as suits.
- Interleaved execution
An intelligent query processing feature, available from compatibility level 140, that stops optimising part-way, executes a qualifying multi-statement table-valued function to learn how many rows it really returns, and then completes the plan.
- Compatibility level
Determines which Database Engine version a database's T-SQL and query processing imitate. Certain new functions,
REGEXP_LIKEamong them, won't work below 170. - Schema
The middle part of a Unity Catalog name (
catalog.schema.table), grouping tables, views, volumes, functions and models inside a catalog. A grant on it covers everything in it now and later, and nothing inside can be reached withoutUSE SCHEMA.
Related terms
- Scalar UDF inlining
From compatibility level 150, intelligent query processing can replace an eligible scalar user-defined function with an equivalent expression or subquery inside the query that calls it. The optimiser can then cost it properly and consider a parallel plan.