In this article, we will talk about the query tuning features that were announced with SQL Server 2019.
Introduction
Sometimes, tuning the queries can be a tough task for database developers or administrators. The most significant point of optimizing a query is to be able to interpret the query plan accurately because it gives lots of information about the query, in this way we can overcome performance issues more easily. At the same time, using the new features of the SQL Server 2019 can help to defeat performance issues without any code changing. In the next sections, we will see how query performance changes in two different versions of SQL Server and we will examine which features of SQL Server 2019 lead to this performance improvement.
Pre-requirements
In this article, we will use the Adventureworks2017 sample database, but this database size is very small to simulate performance problems of the example query, for this reason, we will use the Create Enlarged AdventureWorks Tables script to obtain an enlarged version of the SalesOrder and SalesOrderDetail tables.
Interpreting the execution plan of a query
The execution plan shows which steps have been performed by the query engine during the execution of the query. So, we can figure out which step or steps of the query are more problematic and cause performance issues. Now, let’s execute the following query and interpret the actual execution plan.
|
1 2 3 4 5 6 7 8 |
SELECT p.ProductNumber , p.Name,s.CarrierTrackingNumber , h.AccountNumber, h.CreditCardApprovalCode, dbo.[ufnGetStock](p.ProductID) FROM Sales.SalesOrderDetailEnlarged s INNER JOIN Production.Product p ON s.ProductID = p.ProductID INNER JOIN Sales.SalesOrderHeaderEnlarged h ON h.SalesOrderID=s.SalesOrderID WHERE s.OrderQty > 2 AND LEN(CreditCardApprovalCode)>6 ORDER BY CONCAT(SUBSTRING(CarrierTrackingNumber,1,4), SUBSTRING(p.Class,1,4)) DESC |
Firstly, on the execution plan, we will right-click the select operator, and then we will check the completion time of the query. The elapsed time attribute indicates how long the query took to execute. This query was completed in 169 seconds.
After that, we can ask the following questions:
- Why the query optimizer does not generate a parallel execution plan?
- What does the warning sign indicate on the sort operator?
- Should I create the missing index recommendation?
Let’s look for answers to the questions together now.
User-defined scalar functions and black box problem
User-defined functions generally cause performance problems in the queries because the query optimizer does not have any idea about the scalar-valued function code therefore it cannot include them into the query plan costs accurately. This behavior of the scalar functions can be likened to the black boxes.
Another important issue with scalar functions is that they prevent creating a parallel execution plan. When we tackle the execution plan of the example query, the NonParallelPlanReason attribute identifies why the query optimizer does not generate a parallel execution plan but the CouldNotGenerateValidParallelPlan explanation does not give detailed information on why the query optimizer could not generate a parallel execution plan. Actually, the scalar function is the culprit because as we stated it prevents generating a parallel plan by the query optimizer.
- Question 1: Why the query optimizer does not generate a parallel execution plan?
- Answer: Because of the scalar-valued function
Sort operator and tempdb spill
The query optimizer estimates how much memory is required to execute a query using the estimated number of rows. At the same time, sort and hash join operations affect this memory demand. Outdated statistics cause the estimated number of rows to be calculated incorrectly so the required memory amount for the query to be calculated improperly. During the execution of the query, SQL Server cannot demand more memory dynamically. In this case, the query starts to use the tempdb database, and this issue causes performance problems. In our sample query execution plan, we can see a warning sign on the sort operator and it warns us about the tempdb spill problem.
When we hover the mouse over on the left arrow that is between the compute scalar operator and sort operator, we can see the dramatic difference between the actual number of rows and the estimated number of the rows. This case indicates outdated statistics.
- Question 2: What does the warning sign indicate on the sort operator?
- Answer: It indicates the tempdb spill problem because of the outdated statistics
Missing index
For some queries, SQL Server recommends creating an index or indexes. These indexes may help to improve the performance of the query. On the other hand, there are some risks involved in applying these index suggestions because when SQL Server makes these index suggestions for a particular query but it has no idea about the entire workload of the database engine. The indexes may lead to reduce the performance of the insert, update, and delete statements. As a result, we should consider the advantages and disadvantages before creating the recommended index.
- Question 3: Should I create the missing index recommendation?
- Answer: We need to consider the pros and cons before to create missing index suggestions
Query tuning in SQL Server 2019
SQL Server 2019 was announced some new features that help to improve the performance of the query and the outstanding ones are:
Scalar UDF Inlining
Scalar UDF inlining can solve the performance problems of the scalar function because this new feature automatically transforms scalar functions into sub-queries or scalar expressions and it includes them in the query. So, the query optimizer generates more accurate and consistent execution plans and it can also generate parallel plans. Now we change the Adventureworks compatibility level to SQL Server 2019 and observe the effection of this feature on the execution plan.
|
1 |
ALTER DATABASE AdventureWorks2017 SET COMPATIBILITY_LEVEL =150 |
- Tip: We can also change the database compatibility level on the SQL Server Management Studio (SSMS). At first, right-click on the database and select the Properties menu. As a second step, click the Options tab and change the Compatibility level
After changing the compatibility level of the database, we will execute the same example query and re-analyze the execution plan.
|
1 2 3 4 5 6 7 8 |
SELECT p.ProductNumber , p.Name,s.CarrierTrackingNumber , h.AccountNumber , h.CreditCardApprovalCode, dbo.[ufnGetStock](p.ProductID) FROM Sales.SalesOrderDetailEnlarged s INNER JOIN Production.Product p ON s.ProductID = p.ProductID INNER JOIN Sales.SalesOrderHeaderEnlarged h ON h.SalesOrderID=s.SalesOrderID WHERE s.OrderQty > 2 AND LEN(CreditCardApprovalCode)>6 ORDER BY CONCAT(SUBSTRING(CarrierTrackingNumber,1,4), SUBSTRING(p.Class,1,4)) DESC |
As we can see in the execution, the query optimizer generated a parallel execution plan and transformed the scalar function into a subquery.
Batch Mode on Rowstore
With the help of the batch mode on the rowstore feature, SQL Server can process the rows with batches. So, the query performance will improve enormously. In the execution plan, the BatchModeOnRowStoreUsed attribute indicates that the SQL Server process the rows in the batch mode.
On the clustered index scan operator properties, we can find out more details about the batch mode processing.
The above image shows that each thread has proceeded a bunch of rows through the batch mode on the rowstore feature. When we make a very simple calculation we can find out that the database engine proceeded ~ 900 rows at a time rather than row by row.
Proceeded Row Number = Actual Number of Rows/Actual Number of Rows
So far, we realized that the SQL Server 2019 features can be very helpful to boost the performance of queries without any code changing. The following chart illustrates the dramatic elapsed time difference between the two SQL Server versions.
Row Mode Memory Grant Feedback
If memory allocation is insufficient for a query, in the next execution of the same query, SQL Server will assign more memory for it using the pre-executed query execution plans. This feature is called memory grant feedback. When we execute the sample query 3 or 4 times, we will see that the warning sign will disappear which was placed on the sort operator. When we re-examine the select operator properties, we will see the IsMemoryGrantFeedbackAdjusted attribute indicates YesStable value. It means that the memory grant feedback option has been applied to this query and grant memory is now stable.
Conclusion
In this article, we have learned the query tuning feature details that were announced with SQL Server 2019 and we examined the details on an example. Particularly Scalar UDF Inlining and Batch Mode on Rowstore can improve the performance of the query without any code changes and this point is an impressive part of these features.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023