Intelligent Query Processing involves enhancements to the query engine to improve query performance. This family of features started with SQL Server 2017 and Adaptive Query Processing. Here’s an overview from Redgate of adaptive query processing in SQL Server 2017.
Features introduced in 2019 include:
Table Variable Deferred Compilation
Batch Mode On Rowstore
Scalar UDF Inlining
Approximate Query Processing
Table Variable Deferred Compilation:
Before SQL Server 2019, the query optimizer would assume that a table variable had one row. Obviously, this would result in a less than optimal query plan in most situations. The 2019 edition will use the actual number of rows in the table variable to create the plan.
Batch Mode On Rowstore:
Columnstore indexes allowed for two execution modes: Row and Batch. Batch mode processes an entire group of rows at a time, instead of row by row. Batch mode is useful for aggregations, for example. The 2019 edition brings Batch mode to a rowstore(where the data is stored by row, as opposed to columnstore).
Scalar UDF Inlining:
With a User Defined Function, the pre-2019 behavior is to process one row at a time while applying the function. By Inlining, the function statements can be integrated into the query, so that it can be a set based operation.
Approximate Query Processing:
A new function (APPROX_COUNT_DISTINCT) was added in 2019. This function will return an estimate of the count of rows, when speed is more important than an exact count.
Memory Grant Feedback (row mode):
The 2017 edition introduced features to adjust the amount of memory granted for a query in batch mode. If too much or too little memory is granted for a query, it can be adjusted for the next run of that plan.