SQL Server Auto-Plan Correction

Current Location: MSDN > Documentation > SQL Server > Features > Intelligent Query Processing > Auto-Plan Correction

SQL Server's Auto-Plan Correction feature is a powerful component of Intelligent Query Processing (IQP) designed to automatically identify and mitigate performance regressions caused by suboptimal query execution plans.

Auto-Plan Correction proactively addresses query plan degradation, ensuring consistent application performance without manual intervention.

What is Query Plan Degradation?

Over time, the performance of a query can degrade. This can happen due to various factors such as changes in data distribution, increased data volume, modifications to database schema, or server configuration changes. When these factors affect the statistics used by the query optimizer, it might generate an inefficient execution plan, leading to slower query execution.

How Auto-Plan Correction Works

When enabled, Auto-Plan Correction monitors query execution. If it detects that a query's performance has significantly worsened compared to its historical performance, it can trigger the following actions:

Identify Regression: The system continuously monitors key performance indicators for queries.
Store Baseline Plan: A high-quality execution plan is captured and stored as a baseline.
Detect Performance Drop: If a query starts performing significantly worse than its baseline, this is flagged as a regression.
Force Baseline Plan: Auto-Plan Correction can automatically force the previously stored, efficient baseline plan for the regressed query.
Event Logging: Events related to plan correction are logged for auditing and analysis.

Benefits of Auto-Plan Correction

Improved Performance Stability: Ensures that application performance remains consistent by preventing performance regressions.
Reduced Operational Overhead: Minimizes the need for manual monitoring and tuning of query plans.
Proactive Problem Solving: Addresses performance issues before they impact end-users.
Enhanced User Experience: Leads to a smoother and more responsive application experience.

Enabling and Configuring Auto-Plan Correction

Auto-Plan Correction is enabled at the database level. You can manage this feature using T-SQL commands.

Enabling Auto-Plan Correction:

To enable Auto-Plan Correction for a specific database, use the following command:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Disabling Auto-Plan Correction:

To disable Auto-Plan Correction:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF);

Checking the Status:

You can check the current status of automatic tuning features:

SELECT DATABASE_NAME,
       FORCE_LAST_GOOD_PLAN_STATUS,
       AUTO_EXECUTE_SCALESTATS_STATUS,
       AUTO_CREATE_INDEX_STATUS
FROM sys.dm_db_tuning_options
WHERE DATABASE_ID = DB_ID();

Considerations and Best Practices

Ensure that the database has sufficient disk space for storing execution plans.
Regularly review the query performance history and any logged events related to Auto-Plan Correction.
While Auto-Plan Correction is powerful, it's not a substitute for thorough query tuning and optimization.
Test the feature in a non-production environment before enabling it on critical databases.