Esat Erkec
Compare the execution plans

SQL Server Trace Flags and how they impact the SQL Server Query Optimizer behavior

December 2, 2021 by

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.

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.

IO statistics of a query plan

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.

Reason for Early Termination Of Statement Optimization attribute

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.

  • 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
  • Meaning of the Good enough plan found  in a query optimizer

    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.

    Using IO statistics for the query performance

    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.

    Compare the execution plans

    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.

    Trace flag 8690 and query performance

    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.

    Trace flag 8690

    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.

    IO statistics and query performance

    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.

    Trace flag 8690 usage details

    Starting from SQL Server 2016, we can use NO_PERFORMANCE_SPOOL hint instead of the trace flag 8690.

    NO_PERFORMANCE_SPOOL query hint

    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.

    Hash Aggregate

    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.

    Stream Aggregate

    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.

    MAXDOP query hint

    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.

    Trace flag 8649

    Adding the trace flag 8649 forces the query optimizer to generate a parallel plan for the same query.

    ENABLE_PARALLEL_PLAN_PREFERENCE query hint

    At the same time, we can use the ENABLE_PARALLEL_PLAN_PREFERENCE query hint instead of this trace flag.

    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.

    Esat Erkec
872 Views