Lessons Learned #541:Automatic Plan Correction vs External Tables: A Practical Lesson from the Field

Automatic Plan Correction is an incredibly handy feature in Azure SQL Database, especially when you’re tackling plan regressions. This tool utilises the Query Store to spot when a query starts using a less efficient execution plan, and, when needed, it can enforce the last known good plan.

During a recent troubleshooting session, I realised that not every query behaves the same way. Specifically, queries that pull from external tables can act quite differently than fully local queries since part of their process involves fetching data from remote locations.

If you set Query Store to capture all queries, you can then identify those that reference external tables. This allows you to assess whether certain query IDs should be included in the FORCE_LAST_GOOD_PLAN.

From a practical viewpoint, queries involving external tables might not always be suited for Automatic Plan Correction—especially if the advantages of automatic plan enforcement aren’t easily seen. Thus, the purpose of this article is straightforward: we’ll pinpoint queries referencing external tables and, when necessary, exclude specific query IDs from Automatic Plan Correction.

Let’s take a look at the execution plan for this query:

DECLARE @Region nvarchar(50) = N'EMEA' 
SELECT CustomerId, CustomerName, Region 
FROM dbo.ExternalCustomers 
WHERE Region = @Region;

When we examine it, we notice the presence of a Remote Query operator. This indicates that the query isn’t solely relying on local data; part of its execution depends on accessing data from an external source.

For these types of queries, Automatic Plan Correction may not yield the same clear advantages as it does for entirely local queries. The performance can hinge not just on the execution plan at hand, but also on factors such as the remote database, external data sources, network latency, and the volume of data being fetched from the remote end.

As a result, queries that reference external tables should undergo a thorough review before being included in FORCE_LAST_GOOD_PLAN.

In this case, the first task was to pinpoint the Query Store query_id tied to the query that involves the external table. Since the query text is available in Query Store, we searched for the external table name in sys.query_store_query_text.

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.plan_forcing_type_desc,
    p.force_failure_count,
    p.last_force_failure_reason_desc,
    p.last_execution_time,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
WHERE qt.query_sql_text LIKE N'%ExternalCustomers%'
ORDER BY
    p.last_execution_time DESC;

Once you have identified the query_id, the next step is to exclude that specific query from Automatic Plan Correction. You can do this by setting FORCE_LAST_GOOD_PLAN to OFF for that query_id:

EXECUTE sys.sp_configure_automatic_tuning @option = 'FORCE_LAST_GOOD_PLAN', @type="QUERY", @type_value = N'', @option_value="OFF";

For instance:

EXECUTE sys.sp_configure_automatic_tuning @option = 'FORCE_LAST_GOOD_PLAN', @type="QUERY", @type_value = N'1574', @option_value="OFF";

This action doesn’t disable Automatic Plan Correction for the entire database. It simply instructs Automatic Plan Correction to overlook this particular Query Store query ID regarding FORCE_LAST_GOOD_PLAN.

By taking this approach, you enable Automatic Plan Correction to stay active for the majority of the database workload, while selectively reviewing and excluding queries that rely on external or remote data access when automatic plan enforcement isn’t expected to yield significant benefits.

Frequently Asked Questions

What is Automatic Plan Correction?

It’s a feature in Azure SQL Database that helps manage and improve query performance by forcing the last known good execution plan if a query starts to regress.

When should I exclude a query from Automatic Plan Correction?

If a query references external tables or remote data sources, it may not benefit from Automatic Plan Correction due to additional variables affecting its performance.

How do I find the query_id in Query Store?

You can query the sys.query_store_query_text and related tables to locate the query_id associated with a specific SQL statement.