Experienced SQL Server database administrators and developers spend years learning best practices within SQL Server and how to identify performance pitfalls in the query optimizer. Starting with SQL Server 2017, Microsoft introduced a family of features called “Intelligent Query Processing” to provide more consistent performance for your queries. Microsoft has been able to identify common performance faults using telemetry in Azure SQL Database and SQL Server, which allows them to focus development efforts on the main pain points for customers.
Adaptive Query Processing in SQL Server 2017
SQL Server introduced three major features in the intelligent query processing feature space in 2017:
- Adaptive Joins
- Interleaved Execution for Multi-Statement Table-Valued Functions
- Memory Grant Feedback
Let’s walk through each feature, so you can better understand them.
Adaptive Join
When you join two tables together on a column, the SQL Server query optimizer makes a join choice—hash join, nested loop, or merge join—based on the expected number of rows in the query. The optimizer can make a poor join decision if the statistics on the first column or index are out of date, or if a cached execution plan has a very skewed row count. With SQL Server 2017, if you have a query using batch execution mode—requiring a plan with columnstore index—the query optimizer will defer the join choice, choosing between hash and nested loop based on the number of input rows in the first input once it’s been scanned. Because SQL Server 2019 introduces batch mode on row store, the columnstore index is no longer required to take advantage of this feature.
Interleaved Execution
Table variables and multi-statement table-valued functions (TVFs) are popular among developers for their ease of use, but less popular with DBAs. This is because in the past, the query optimizer used a fixed estimate for table variables and multi-statement TVFs. We’ll explore table variables in more detail later. Multi-statement TVFs used a fixed row estimate of 100 rows, as shown here:
If you run this function on SQL Server compatibility level 130, you’ll see the fixed row estimate of 100 rows, as shown in Figure 1.
Figure 1: Results of function with SQL Server compatibility level 130
If you change your database’s compatibility level to 140 or 150—using SQL Server 2017 or later versions—you’ll see the behavior shown in Figure 2. With the behavior shown in Figure 2, SQL Server defers execution of the plan until it gathers the actual row count from the TVF, which in this case is 421 rows.
Figure 2: Results of function with SQL Server compatibility level at 140 or 150
While this may seem like a small number of rows, accurately counting the number of input rows can lead to significant performance gains because the query optimizer can allocate memory more accurately and make better join decisions.
Memory Grant Feedback
When you execute a query, the database optimizer allocates a certain amount of memory for join and sort operations based on the number of rows it expects to join. If you have data skew in one of your target tables, you may have an execution plan allocating either far too much or too little memory for your query. Too little memory results in the database engine spilling to TempDB, which incurs a significant I/O penalty slowing your query run. If too much memory is allocated, other queries may backlog—shown by the wait type RESOURCE_SEMAPHORE in SQL Server—limiting your overall concurrency. Memory grant feedback in SQL Server 2017 for queries in batch execution mode (in SQL Server 2019 this extends to row execution mode) dials up or down the amount of memory for a given execution plan. This is based on over or under allocation of memory in an initial query execution. While this process won’t help the first bad execution of a query, subsequent executions should result in significant improvements. For queries that experience substantial oscillation (where the memory required changes frequently), memory grant feedback may be disabled by the engine.
SQL Server 2019 Additions
Up to this point, the discussion has mostly centered on the features added to SQL Server 2017 and enhanced in SQL Server 2019. However, some new additions in SQL Server 2019 are important for DBAs and developers to know about:
- Table Variable Deferred Compilation
- T-SQL Scalar User-Defined Functions (UDFs) Inlining
- Approximate Count Distinct
- Batch Mode on Rowstore
You can read more about all four of these features at Microsoft docs, however, there are two features you’ll want to pay special attention to. First, table variable deferred compilation works similar to the behavior of the multi-statement TVFs discussed earlier. Instead of having a fixed row count of one row, the table variable is checked at query compile time and the rest of the execution plan is compiled with the row count, creating much better plans.
The second feature is the inlining of Scalar UDFs. Scalar UDFs have always been a performance pain point. They forced execution plans to be single-threaded and offered limited insight into what was actually happening in an execution plan, making them extremely hard to fine-tune. The new feature to inline the execution of these functions—which requires compatibility level 150—allows for much faster execution and shows the actual operations performed in the execution plan.
Monitoring Intelligent Performance
As you can see, recent editions of SQL Server offer several intelligent query processing options that allow your queries to execute more efficiently and consistently. Each of these features is instrumented, and it can be helpful to understand if your databases are taking advantage of these features.
When you start researching tools for monitoring SQL Server performance, look at SolarWinds SQL Sentry. SQL Sentry can provide the necessary visibility for your backup operations. Get started today with your free 14-day trial. See for yourself what it can do for you.
