SQL Server Database Scoped Configurations and Automatic Plan Correction

In this article, we will examine Database Scoped Configurations and SQL Server 2017 Automatic Plan Correction. Microsoft added new features to SQL Server 2017 that improved the query performance.

SQL Server query performance is related to quality and accuracy of the execution plan. When we run a query, the query optimizer analyzes a lot of execution plans and then decides on the optimal query execution plan.

Legacy Cardinality Estimation: The Cardinality Estimator predicts how many rows the query will return, as well as determines the memory allocation of the query.

In SQL Server 2017, the default Cardinality Estimation Model Version is 14.0, but if you want to use the older version 7.0 of the Cardinality Estimator, you can do this by changing the Legacy Cardinality Estimation option in the Database Scoped Configurations section.

The default value of the Legacy Cardinality Estimation is OFF. Thus if you want to use the older version, you have to switch it to ON.

Alternatively, you can change this property in T-SQL.

However, if you enable this setting, it will affect all the queries. As a result, this may damage the query performance. To prevent this, you can use the FORCE_LEGACY_CARDINALITY_ESTIMATION hint.

When we run this query in the WideWorldImporters database, it will automatically use a new version of the cardinality estimation.

When we add FORCE_LEGACY_CARDINALITY_ESTIMATION to the query, the query optimizer will use the previous or oldest version of the cardinality estimation.

MAXDOP: we can set the maximum degree of parallelism for an individual database. Before this feature had been created, we could only configure the MAXDOP server level.

The MAXDOP query hint allows us to run queries in parallel.

Parameter Sniffing: When a query execution time changes dramatically and this time change is related to the query parameter, it is called a parameter sniffing.

Now, we will create a stored procedure on the AdventureWorks database. We will send different parameters and compare execution plans.

As shown in the below image, SQL Server generates a different execution plan for the same query. The Query Mars execution plan recommends an index. The query parameter changes the optimal execution plan.

Execute this query and look at the execution plans.

The Query Venus execution plan is the same as the Query Mars execution plan. This is the parameter sniffing because the cached execution plan is compiled for the Query Mars execution plan. For this reason, Query Venus uses the same execution plan.

Now, we will disable parameter sniffing and run the same queries.

Let’s examine:

The SQL Server Query optimizer generated the optimal execution plan for Query Venus and Query Mars. This approach provides the optimum performance to the query.

There are some options to avoid this issue:


Automatic Plan Correction

SQL Server 2017 includes a new feature called Automatic Plan Correction. When we execute a query, the query optimizer creates an execution plan. For some reasons, the query optimizer chooses wrong execution plans. Some of the reasons are as follows:

  • A query that does not meet performance criteria
  • Out of date statistics
  • Unsuitable indexes

When the SQL Server query optimizer decides to change the execution plan and this execution plan damages the performance, the query performance is called the plan regression. A new feature comes with SQL Server 2016. This tool helps in monitoring and troubleshooting query performance and at the same time stores performance metrics and counters of the query execution.

We can enable these options under database properties.

Now, we are going to make a demo of this feature. First of all, clear the procedure cache and create a stored procedure.

At this step, we will execute this procedure with different parameters and find the execution time difference.

As you can see, the first query was completed in 12 sec, while the second one was done in 33 sec. The reason for this dramatic difference is that the query optimizer chooses an unsuitable execution plan for Query Beta.

Let’s compare the execution plans of Query Alpha and Query Beta.

Execution plan of Query Alpha

Execution plan of Query Beta

In the images above, the query optimizer creates different execution plans for the same query. When we look at Top Resources Consuming Queries, we can see that Query Beta consumes more resources than Query Alpha.

The below query will return detailed information on the tuning recommendations.

The reason column shows why we have to apply this recommendation.

Now, we will re-run Query Alpha and Query Beta with the automatic plan correction enabled.

After this demo, the Query Alpha execution plan is applied to the Query Beta. Additionally, the Query Alpha and Query Beta execution times are close to each other. The below query will return the automatic plan correction status.

Also, we can find some graphical information in Queries with Forced Plans. This graph defines the forced query plans and query.


Automatic plan correction in SQL Server 2017

Cardinality Estimation

Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç