Query tuning is an essential task for database developers and administrators to optimize the performance of SQL Server. With the release of SQL Server 2019, several new features have been introduced to help overcome performance issues without the need for code changes. In this article, we will explore these query tuning features and their impact on query performance.

Interpreting the Execution Plan

The execution plan provides valuable insights into the steps performed by the query engine during query execution. By analyzing the execution plan, we can identify problematic steps that may be causing performance issues. Let’s consider an example query and interpret its execution plan:

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

By analyzing the execution plan, we can answer questions such as why the query optimizer does not generate a parallel execution plan, what the warning sign on the sort operator indicates, and whether we should create the missing index recommendation.

User-Defined Scalar Functions and Black Box Problem

User-defined scalar functions can often cause performance problems in queries. The query optimizer lacks information about the scalar-valued function code, making it difficult to accurately estimate the query plan costs. This behavior can be likened to black boxes. Additionally, scalar functions prevent the generation of parallel execution plans. In our example query, the NonParallelPlanReason attribute indicates that the query optimizer does not generate a parallel execution plan due to the scalar function.

Sort Operator and Tempdb Spill

The query optimizer estimates the memory required for query execution based on the estimated number of rows. Sort and hash join operations impact this memory demand. Outdated statistics can lead to incorrect calculations, resulting in improper memory allocation and performance issues. The warning sign on the sort operator in the execution plan indicates a tempdb spill problem caused by outdated statistics.

Missing Index

SQL Server may recommend creating indexes for certain queries to improve performance. However, it’s important to consider the advantages and disadvantages before implementing these index suggestions. Creating recommended indexes may impact the performance of insert, update, and delete statements.

Query Tuning in SQL Server 2019

SQL Server 2019 introduces several features to enhance query performance:

Scalar UDF Inlining

Scalar UDF inlining addresses the performance issues caused by scalar functions. This feature automatically transforms scalar functions into sub-queries or scalar expressions, allowing the query optimizer to generate more accurate and consistent execution plans. It also enables the generation of parallel plans. By changing the compatibility level of the Adventureworks database to SQL Server 2019, we can observe the impact of this feature on the execution plan.

ALTER DATABASE AdventureWorks2017 SET COMPATIBILITY_LEVEL = 150

Batch Mode on Rowstore

The batch mode on rowstore feature allows SQL Server to process rows in batches, significantly improving query performance. The execution plan indicates the usage of batch mode on rowstore, and further details can be found in the properties of the clustered index scan operator.

Row Mode Memory Grant Feedback

Row mode memory grant feedback ensures sufficient memory allocation for queries. If memory allocation is insufficient, SQL Server assigns more memory in subsequent executions using pre-executed query execution plans. This feature eliminates the need for excessive tempdb usage and improves query performance.

Conclusion

SQL Server 2019 introduces powerful query tuning features that can significantly improve query performance without requiring code changes. Scalar UDF inlining, batch mode on rowstore, and row mode memory grant feedback are among the notable features that enhance query performance. By leveraging these features, database developers and administrators can overcome performance issues and optimize the performance of their SQL Server queries.