A few weeks ago I posted my first Friday Feedback about Query Store, and one of the responses referenced a stored procedure I hadn't used before: sp_configure_automatic_tuning. There was an assumption that this stored procedure wasn't documented and didn't work as expected. Knowing how much I love Query Store, Automatic Plan Correction and documentation, I went searching. If you aren’t familiar with the stored procedure, read on.
tl;dr
Good news! The sp_configure_automatic_tuning stored procedure is fully documented and supported.
What does sp_configure_automatic_tuning do?
If you’ve enabled Automatic Plan Correction (APC) for your database, sp_configure_automatic_tuning gives you granular control at the individual query level. Not sure if APC is enabled for your database? Run:
USE [dbname];
GO
SELECT *
FROM sys.database_automatic_tuning_options
WHERE name = 'FORCE_LAST_GOOD_PLAN'
AND actual_state = 1;
GO
With APC enabled, you can use sp_configure_automatic_tuning to:
- Exclude specific queries from APC monitoring
- Apply an extended, time-based plan regression check to specific queries
Why you might need either option
As much as we try to design a feature for every scenario, there are always unique workloads and edge cases that don’t fit exactly into problems that the feature is designed to solve.
FORCE_LAST_GOOD_PLAN OFF
You may have a complex query (or small set of them) that has enough variability across executions that causes APC to detect a regression and force a plan. Once forced, APC monitors the performance of that forced plan, and it may detect that the plan regresses (because of the variability across executions) and then un-force it. The act of repeated forcing and un-forcing may be more problematic than just letting the natural execution variance occur.
Separately, workloads often have complex, critical queries that you may want to exclude simply because you’re not comfortable with a plan being automatically forced. In either scenario, you can tell the engine to skip a query, based on its query_id, because it either doesn’t benefit from automatic plan forcing, or you don’t want forcing even attempted.
To disable APC for a given plan, once you have the query_id (e.g. 422), simply execute:
EXECUTE sys.sp_configure_automatic_tuning 'FORCE_LAST_GOOD_PLAN', 'QUERY', 422, 'OFF';
FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK ON
When a query’s plan changes, APC has a detection phase where it checks to see if the query’s performance has regressed. It monitors the query for a set number of executions, and if there is no regression for those executions, the detection phase is complete, and APC won’t look at the query again until the next plan change. However, there are scenarios where the initial executions of the query are acceptable, but concurrent or subsequent executions are slower, and may even timeout. In that case, we want APC to check the query’s performance again. Enabling FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK for a query means that APC will check the query again, five minutes after the initial plan change.
This scenario is described in more detail in Derek’s post from April, Query plan regressions got you down? Here's how Automatic Plan Correction can turn it around, and if you use APC, I recommend taking time to read it (maybe even twice, because it has such great info).
To extend the check for a given query (e.g. 537), run:
EXECUTE sys.sp_configure_automatic_tuning 'FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK', 'QUERY', 537, 'ON';
You can also enable this capability globally using trace flag 12656 (SQL 2022 CU4 and later).
Checking what queries have either option enabled
To see which queries have either option enabled, use sys.database_automatic_tuning_configurations:
SELECT * FROM sys.database_automatic_tuning_configurations;
Just in case you didn’t visit the documentation 😉 the sp_configure_automatic_tuning stored procedure is available in:
- SQL Server 2022 and later
- Azure SQL Database
- Azure SQL Managed Instance
- SQL database in Microsoft Fabric
The quest for feedback
If we haven’t met before 👋 I was previously the PM for SQL Server Management Studio (SSMS), and in that role I regularly reminded folks in blog posts to file feedback items for SSMS. While I’ve moved teams, it will come as no surprise that I’ll be asking folks to file feedback items for Query Store, Automatic Plan Correction, and anything in the Query Processing space on the SQL feedback site. If you filter for items in the Query Store group, you’ll see a few recent comments from me. Unfortunately, the feedback site doesn’t have a great way to tag those who originally posted, or who commented. I’m committed to figuring out a better solution (please be patient), but in the meantime, if you post something, please try and check back every so often in case there are follow up questions. And as always, feel free to make time to review existing requests and upvote those that resonate with you. We want to hear from you...about Query Store, APC, and whatever else is creating challenges in the SQL Engine space. Thanks for reading!
Microsoft