The Intelligent Query Processing family of features builds on the performance tuning features of Adaptive Query Processing in SQL Server 2017. These include the expansion of memory grant feedback to row mode operations, table variable deferred compilation, the ability to run batch mode operations on rowstore data, T-SQL Scalar UDF Inlining, and the Approximate Count Distinct function.
- [Narrator] In SQL Server 2019, Microsoft introduces a new family of features called Intelligent Query Processing. These features bring immediate performance improvements to your existing query workloads, and require little to no effort by the database administrators to take advantage of. Intelligent Query Processing builds on a number of features that were previously introduced underneath the heading of Adaptive Query Processing, in SQL Server 2017. With the 2019 release, those features are expanded upon and integrated underneath the new feature family called Intelligent Query Processing. These include the expansion of memory grant feedback to row mode operations in addition to batch mode operations in query execution plans. It also includes table variable deferred compilation, the ability to run batch mode operations on row stored data, something called T-SQL Scaler UDF Inlining, and the Approximate Count Distinct Query Plan. Let's take a look at each of these new additions. Through a process called Memory Grant Feedback, SQL Server can dramatically improve the performance of repeated queries. It does this by identifying the ideal memory grant size required for a query to have all the rows fitted memory during the first run. This information is stored in the cache plan and it's used to help the query engine dilate an optimal amount of memory to allocate the next time it's executed. This helps to avoid the problem of not enough memory being granted on subsequent executions, causing operations to spill to much slower disk drives. It also avoids the opposite problem of too much memory being granted, and it just sits there unused and wasted, when it could be allocated to other tasks on the server. This feature was previously only applied to batch mode operations used in the execution plan involving tables with columnstore indexes. But in SQL Server 2019, it now applies to row mode operations as well. And speaking of batch mode operations, they've now been expanded to work on rowstore data in addition to tables with a columnstore index. With batch mode, query operations can process batches of rows at once, rather than working on a single row at a time. This makes better use of CPU processing capacity and benefits queries with multiple joins and aggregate functions. This update enables the same performance enhancements on tables where columnstore indexes would be too costly to implement. With Table Variable Deferred Compilation, queries that include table variables are optimized by first identifying actual table variable row counts, and then passing that value to downstream plan operations. This allows the execution plan to make better decisions and increases performance and quality when compared to the previous behavior of making a one row guess of the number of rows in a table variable. This deferred compilation behavior is now identical to the behavior of temporary tables. Scaler user-defined functions return a single value, and are used in queries as a way to achieve code reuse and modularity. The problem with them in the past has been that they act as a black box in the middle of the query, and they can't be optimized the same way relational operators are. The actual cost of computing a scaler user-defined function can be quite high and there's no way for the optimizer to know ahead of time what the cost will likely be. With the new inlining feature, user-defined functions are automatically transformed into scaler expressions or sub queries, which can be optimized during execution. This is because the optimizer is now aware of the full set of steps that must be executed, and in turn is able to generate a much better execution plan. Finally, this brings us to approximate query processing. This is a new feature family that promises to grow in future versions of SQL Server, and it'll include aggregate functions that could be used where responsiveness is more critical than absolute precision. For very large data sets, it's sometimes beneficial to quickly get an answer that's close enough to be actionable rather than to take all day to get an exact answer. For now, this new family simply contains a new aggregate function called Approximate Count Distinct, which returns the approximate number of unique non-null values in a group. You would use it in the same way that you currently use the count function, and it's designed to be applied to data sets containing over a million records, and guarantees up to a two percent error rate within a 97 percent probability. In order to take advantage of these new intelligent query processing features, you simply need to enable the new compatibility level on your databases. Here in SQL Server Management Studio, I'm going to use the Wide World Import of sample database. To see what the current compatibility level is, we're simply going to select the compatibility level column from S-Y-S dot databases. I can see that we're currently at 130. The new compatibility level for SQL Server 2019 is 150, and we can set that by using an alter database statement. Alter database Wide World Importers and set compatibility level equal to 150. When I execute it, I see that the command completed successfully, and now I can see that the new compatibility level for Wide World Importers is 150. And that's all you need to do to take advantage of the new intelligent query processing features of SQL Server 2019.
- Intelligent query processing
- Improvements to persistent memory
- Table virtualization with PolyBase
- Creating a unified data cluster
- Training and creating machine learning models
- Running SQL Server in containers
- Updates to the SQL Server Management Studio (SSMS)