Esat Erkec
Columnstore Index Scan operation in execution plan

Using Automatic Plan Correction for Query Tuning

March 4, 2021 by

In this article, we will learn what is plan regression and how we can fix this issue with help of the Automatic Plan Correction feature.

Introduction

Sometimes, query tuning operations can get very challenging and complicated in SQL Server. With the SQL Server 2016, a new feature Query Store has been introduced and this feature captures and retains the executed queries execution plan and runtime statistics. In this way, we started to identify query performance issues more easily. In the next step, we have met a new feature in SQL Server 2017 and this feature’s name is Automatic Plan Correction. This feature identifies the query plan issues automatically and suggests some recommendations to fix these issues. It can also apply the suggested query tuning recommendations automatically.

Pre-Requirments

As we stated, we need to enable the Query Store feature to activate the Automatic Plan Correction. The following query will enable the Query Store for any particular database.

Firstly, we don’t enable the automatic plan correction feature for this reason, we will disable it through the following query.

What is query plan regression in SQL Server?

SQL Server query optimizer’s goal is to generate optimum execution plans for the queries. Statistics, indexes, cardinality estimator, and some other factors have an impact upon the generated execution plans. SQL Server stores the generated execution plans in the plan cache and reuses the cached plans for the next execution of the same queries. In this way, the query optimizer prevents the consumption of redundant time and CPU in order to generate a new execution plan when the same query is executed. However, for some reason (creating or dropping indexes, updating the statistics, etc.), SQL Server may give up using the cached query plan and compile a new query plan. The newly created query plan shows underperforms from the previous one as a result it influences the query performance negatively. The main symptom of this problem is that your query suddenly slows down in the database, although there is no remarkable change. This situation is called the query plan choice regression.

How does a query plan regression happen?

As we mentioned in the previous section, SQL Server stores the query plans in the cache so that, when the same query is executed for the different parameters it will use the same query plan. However, for some queries, the optimum plan can differ depends on the used parameters. Let’s illustrates this circumstance with an example.

Firstly, we will create a sample table like the one below.

This table includes two types of indexes, the first one is non-clustered columnstore index that is stored data in a column-wise fashion and the other one is the classical B-Tree type index. As a second step, we will populate the table with some non-homogeneous data.

After populating the data, we will update the indexes.

Now we enable the actual execution plan and run the query.

Columnstore Index Scan operation in execution plan

In this query plan, SQL Server query optimizer has decided to use columnstore index to access the one million rows and it is very logical for query tuning. Then the row counting operation is performed by the hash match aggregate operator. We come across the hash match aggregate when the storage engine is dealing with the unsorted huge amount of data. The compute scalar operator converts the result of the stream aggregate operator to an integer because the COUNT function returns the data type as an integer. Understanding the execution plan properly is very important for query tuning.

How we can determine Compute Scalar operator for query tuning

Now, we will execute the same query 500 times so that the query store can capture the required data.

Now, we will remove the previous query’s plan from the plan cache. To remove a specific execution plan from the plan cache, it is first necessary to identify the underlining query plan. To find out the query plan, we can use the following query.

Returning the cached query plans

We will remove the cached query plan.

Let’s execute the same query with a different parameter and analyze the execution plan.

Index seek operation in the execution plan and query tuning

As we can see in the actual execution plan of this query, it has used an index seek operator and it has performed a seek predicate. The reason is that Val columns data stored in a non-clustered index structure because of the IX_002_NCluster index definition so the SQL Server storage engine can access the filtered data directly via using the B-tree index structure. The stream aggregate operator performs aggregation functions ( SUM, COUNT, AGV, MIN, and MAX) in the queries for this query it has accomplished the counting operation.

When we execute the query with the first parameter, the query optimizer decides to use a different and inefficient execution plan for this query.

How the plan regression affects the query tuning

The select operator details can give some details about this situation. In the parameter list attribute of the select operator, we can find out some interesting details. The executed query is compiled for the different parameters and the query optimizer has chosen this query plan.

Analyzing the Parameter List attribute of the query plan

This issue is called plan choice regression or plan regression.

SQL Server Automatic Plan Correction Recommendations

The Automatic Plan Correction feature tracks the query plan regression issues that occur in the database. When it detects a regressed query, it automatically forces the query to use the efficient plan instead of using the worse one. Now we make a demonstration of this feature.

  1. We activate the Automatic Plan Correction feature and clear the Query Store data

  2. We clear all query plan cache that is compiled and stored by the query optimizer. This command can hurt your database performance, for this reason, don’t use it in the production databases

    • Tip: While writing this article, I experienced a problem with my query plan cache. SQL Server resets the query plan cache for a short time without any logical reason. In general, this problem is related to the Enable Lock Pages in Memory (LPIM) property. In SQL Server 2019, we can find out this property in the SQL Server Configuration Manager. Under the advanced tab of the SQL Server 2019, we can enable Lock Pages in Memory property as shown below:

      Enable Lock Pages

  3. We execute the following query 500 times so that the Query Store can capture the statistics of a query

  4. We clear the plan cache and execute the same query with a different parameter and then we execute it with the same parameter 500 times. So, the query starts to use a sub-optimal query plan

The sys.dm_db_tuning_recommendations dynamic management view returns the identified query tuning issues and the status of these recommendations.

How we can use dm_db_tuning_recommendations for query tuning

For our demonstration, SQL Server has been forced to use a query plan that includes a columnstore index and the data engine has started to use it instead of the sub-optimal plan. In the reason column, why this query tuning issue is applied for this query. The Verifying status identifies that this recommendation has been applied automatically by the automatic plan correction feature and it still waits for the verification process to compare the performance of the forced plan with the regressed plan. This situation can report using the Queries With Forced Plans.

Use Query Store reports for query tuning

Conclusion

In this article, we have discovered the Automatic Plan Correction feature and we have learned how it can help us in the query tuning operations. This feature can be very useful to overcome plan regression issues and may help to resolve parameter sniffing problems. On the other hand, we can disable this feature and apply the tuning recommendations manually.

Esat Erkec
1,248 Views