In this article, we will learn some query hints and trace flags that impact the query performance and also influence the SQL Server query optimizer’s default execution plan generation algorithm.
Introduction
We can describe the SQL Server query optimizer is a cost-based optimizer that generates multiple execution plan candidates and then decides to use the lowest costed plan. The query plans decided by the optimizer are called the optimum plan. It is possible that we can force the SQL Server query optimizer to change its default behavior with the help of the query hints, trace flags, and some other settings. Sometimes these trace flags and query hints can improve the performance of the queries but applying these changes globally is not a good idea because they may affect decrease the performance of the database negatively.
Trace flag 8780
As we stated, during the query processing phases, the SQL Server query optimizer generates multiple query plan candidates and then selects the query plan which has the lowest cost. However, the SQL Server query optimizer generates a reasonable number of candidate query plans in this optimization process and ends this step if it cannot find any optimum plans. Sometimes a scenario when an optimum query plan is not found can negatively affect query performance.
Now let’s demonstrate this scenario with an example. we enable the actual execution plan on the SQL Server Management Studio (SSMS) and then execute the following query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SET STATISTICS TIME ON; SET STATISTICS IO ON GO SELECT soh.[SalesPersonID], p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName], e.[JobTitle], st.[Name] AS [SalesTerritory], soh.[SubTotal], YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] FROM [Sales].[SalesPerson] AS sp INNER JOIN [Sales].[SalesOrderHeader] AS soh ON sp.[BusinessEntityID] = soh.[SalesPersonID] INNER JOIN [Sales].[SalesTerritory] AS st ON sp.[TerritoryID] = st.[TerritoryID] INNER JOIN [HumanResources].[Employee] AS e ON soh.[SalesPersonID] = e.[BusinessEntityID] INNER JOIN [Person].[Person] AS p ON p.[BusinessEntityID] = sp.[BusinessEntityID]; |
When we analyze the statistics details of the query through the Statistics Parser, we can notice that a huge amount of logical reads have been performed on the SalesOrderHeader table.
On the other hand, when we check the execution plan of the query, we can see an attribute named Reason for Early Termination Of Statement Optimization. This attribute indicates the Time Out value.
Actually, the Time Out value tells us, the SQL Server query optimizer could not find an optimum query plan for all its attempts during the query optimization phase and then stops the trying. In this case, the SQL Server query optimizer chooses one of the non-optimal plans it has tried and uses it. Trace flag 8780 gives more time to the query optimizer in this way SQL Server query optimizer can find a chance to find the optimum query plan. Now we will execute our sample query to add this trace flag and execute it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SET STATISTICS TIME ON; SET STATISTICS IO ON GO SELECT soh.[SalesPersonID], p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName], e.[JobTitle], st.[Name] AS [SalesTerritory], soh.[SubTotal], YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] FROM [Sales].[SalesPerson] AS sp INNER JOIN [Sales].[SalesOrderHeader] AS soh ON sp.[BusinessEntityID] = soh.[SalesPersonID] INNER JOIN [Sales].[SalesTerritory] AS st ON sp.[TerritoryID] = st.[TerritoryID] INNER JOIN [HumanResources].[Employee] AS e ON soh.[SalesPersonID] = e.[BusinessEntityID] INNER JOIN [Person].[Person] AS p ON p.[BusinessEntityID] = sp.[BusinessEntityID] OPTION ( QUERYTRACEON 8780) |
- Tip: SQL Server execution plan includes which trace flags have been used in the query and it also shows the usage scope of the trace flag
- 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
As shown above, trace flag 8780 ensured to find the optimal query plan by the SQL Server query optimizer so the query made less logical reads.
At the same time, the query plan was changed due to this trace flag usage. When we compare the two query plan, we can see the differences between these two query plans more clearly.
Finally, trace flag 8780 can improve query performance when the SQL Server query optimizer can not find a good query plan. However, before using trace flag 8780 we have to consider all CPU, memory, IO, and other details.
Trace flag 8690
The main purpose of spool operators is to save intermediate query results on the TempDB database, and then it can return copies of these rows during the execution of the query. So that, this approach improves the query performance. After enabling the IO statistics, we will execute the following query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SET STATISTICS IO ON GO SELECT TOP 100 soh.[SalesPersonID], soh.AccountNumber ,Total FROM [Sales].[SalesOrderHeader] soh CROSS APPLY ( SELECT SUM(SubTotal) FROM Sales.SalesOrderHeader SH WHERE SH.ModifiedDate = soh.ModifiedDate GROUP BY SH.AccountNumber ) AS TPL (Total) |
In the output of the statistics, you see Worktable and Workfile tables. SQL Server creates temporary tables in the TempDb during the execution of the query because of some logical operators. In addition, we see a Table Spool (Lazy Spool) operator in the execution plan of the query.
We can disable the spool operator on the inner side of the nested loop with trace flag 8690. When we enable the trace flag 8690, the query optimizer will discard the usage of the spool operator for this query and will find another alternative execution plan. Now, we enable the trace flag 8690 for our sample query and execute it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SET STATISTICS IO ON GO SELECT TOP 100 soh.[SalesPersonID], soh.AccountNumber ,Total FROM [Sales].[SalesOrderHeader] soh CROSS APPLY ( SELECT SUM(SubTotal) FROM Sales.SalesOrderHeader SH WHERE SH.ModifiedDate = soh.ModifiedDate GROUP BY SH.AccountNumber ) AS TPL (Total) OPTION (QUERYTRACEON 8690) |
Using the trace flag 8690 has changed the IO statistics of the query and it has also removed the table spool operator in the query plan.
Starting from SQL Server 2016, we can use NO_PERFORMANCE_SPOOL hint instead of the trace flag 8690.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT TOP 100 soh.[SalesPersonID], soh.AccountNumber ,Total FROM [Sales].[SalesOrderHeader] soh CROSS APPLY ( SELECT SUM(SubTotal) FROM Sales.SalesOrderHeader SH WHERE SH.ModifiedDate = soh.ModifiedDate GROUP BY SH.AccountNumber ) AS TPL (Total) OPTION (NO_PERFORMANCE_SPOOL) |
The main purpose of using the spool operator is to improve query performance. Therefore, If we are not sure that disabling the spool operator will improve query performance, we should not use it. In some cases, the spool operator may cause a workload on the TempDb, and in such cases, it makes sense to disable this operator.
Stream Aggregate and Hash Aggregate
The SQL Server query optimizer can decide to use two different aggregation operators to fulfill the aggregation functions request in the query.
|
1 2 3 |
SELECT soh.AccountNumber, SUM(soh.SubTotal) AS Subtotal FROM Sales.SalesOrderHeader AS soh GROUP BY soh.AccountNumber; |
For this query, the optimizer decided to use the hash match operator and it calculated the query cost is 1,31641. Now, we will add the HASH GROUP hint at the end of the query, and re-analyze the query plan.
|
1 2 3 4 |
SELECT soh.AccountNumber, SUM(soh.SubTotal) AS Subtotal FROM Sales.SalesOrderHeader AS soh GROUP BY soh.AccountNumber OPTION (ORDER GROUP) |
After forcing the query to use the stream aggregation operator, the query cost increased to 2,74227 and an extra sort operator has been added to the query plan. The sort operation adds extra cost to the query plan for this reason optimizer decided to use a hash aggregate operator for this query by default.
Force a query to run parallel
SQL Server storage engine has the ability to execute a query in a parallel manner when the optimizer generates a parallel query plan so it can improve the query performance. Mainly, two settings are pretty important for the parallelism in SQL Server:
The maximum degree of parallelism (MAXDOP) options help to determine the maximum number of processors.
The cost threshold for the parallelism option specifies a limit value and when a query estimated cost is over this threshold then the SQL Server query optimizer begins to consider the parallel plan alternatives along with single plans.
At the same time, we can use the MAXDOP query hint to set how many threads can run concurrently for a query. For example, the below query can only use 2 threads concurrently during the execution of the query.
|
1 2 3 4 5 6 7 8 |
SELECT soh.AccountNumber ,SUM(soh.SubTotal) as Subtotal FROM Sales.SalesOrderHeaderEnlarged soh GROUP BY soh.AccountNumber OPTION (MAXDOP 2) |
When we look at any parallel operator properties we can find out each thread workload but here is a point that we need to consider. We set MAXDOP to 2 but we see that 3 threads are assigned by SQL Server for this query. This thread is synchronizing thread and it is responsible to gather all other treads output data. Sometimes, we want to force the optimizer to generate a parallel plan. To do this we can use trace flag 8649 because it removes the cost threshold for the query. The below query does not generate a parallel plan because its plan cost is under the cost threshold for the parallelism.
Adding the trace flag 8649 forces the query optimizer to generate a parallel plan for the same query.
|
1 2 3 4 5 6 7 8 |
SELECT soh.AccountNumber ,AVG(soh.SubTotal) as Subtotal FROM Sales.SalesOrderHeader soh GROUP BY soh.AccountNumber OPTION(QUERYTRACEON 8649) |
At the same time, we can use the ENABLE_PARALLEL_PLAN_PREFERENCE query hint instead of this trace flag.
|
1 2 3 4 5 6 7 8 |
SELECT soh.AccountNumber ,AVG(soh.SubTotal) as Subtotal FROM Sales.SalesOrderHeader soh GROUP BY soh.AccountNumber OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) |
Conclusion
In this article, we have explored some trace flags that can affect the query performance of the queries. These trace flags influence the SQL Server query optimizer’s default behavior and force the optimizer to generate a different execution plan according to the flag instruction.
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