SQL Server ‐ Automatic Query Tuning - shaysalomon12/Data-Engineer GitHub Wiki

References:

Steps:

1. Enable the query store option

USE [DB_NAME]
GO

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO

ALTER DATABASE CURRENT SET QUERY_STORE 
(
  OPERATION_MODE = READ_WRITE,
  DATA_FLUSH_INTERVAL_SECONDS = 600,
  MAX_STORAGE_SIZE_MB = 500,
  INTERVAL_LENGTH_MINUTES = 30
  );
GO

2. Clear the Procedure cache

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

3. Clear the Query Store

ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL;

4. Enable the Automatic Tuning option on the database

ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON ); 
GO

5. Verify the database settings and options

SELECT * FROM sys.database_automatic_tuning_options

6. After a while, show tuning recommendations

For each query_id we can see if a good plan was:

  • Successfully forced (state = Success)
  • Failed to force (state = Reverted)
  • Expired
SELECT 
	query_id = JSON_VALUE(details, '$.planForceDetails.queryId'),
	reason, 
	score,
	JSON_VALUE(state, '$.currentValue') state,
	JSON_VALUE(state, '$.reason') state_transition_reason,
	script_to_force_recommendedPlanId = JSON_VALUE(details, '$.implementationDetails.script'),
	current_plan_id = JSON_VALUE(details, '$.planForceDetails.regressedPlanId'),
	recommended_plan_id = JSON_VALUE(details, '$.planForceDetails.recommendedPlanId'),
	current_Plan_Execution_Count = JSON_VALUE(details, '$.planForceDetails.regressedPlanExecutionCount'),
	recommended_Plan_Execution_Count = JSON_VALUE(details, '$.planForceDetails.recommendedPlanExecutionCount'), 
	current_Plan_Cpu_Time_Average_microsec = cast(JSON_VALUE(details, '$.planForceDetails.regressedPlanCpuTimeAverage') as float),
	recommended_Plan_Cpu_Time_Average_microsec = cast(JSON_VALUE(details, '$.planForceDetails.recommendedPlanCpuTimeAverage') as float),
	current_Plan_Cpu_Time_Stddev_microsec = cast(JSON_VALUE(details, '$.planForceDetails.regressedPlanCpuTimeStddev') as float),
	recommended_Plan_Cpu_Time_Stddev_microsec = cast(JSON_VALUE(details, '$.planForceDetails.recommendedPlanCpuTimeStddev') as float),
	details
FROM sys.dm_db_tuning_recommendations;

7. List forced plans

select * from sys.query_store_plan where is_forced_plan=1;

8. List SQL Text for query_id

SELECT 
    qsq.query_id,
    qsq.last_execution_time,
    qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt
        ON qsq.query_text_id = qsqt.query_text_id
WHERE 
	qsq.query_id = <query_id>;

9. In case you want to revert from forced plan

List queries in the Query Store:

SELECT txt.query_text_id,
    txt.query_sql_text,
    pl.plan_id,
	pl.is_forced_plan,
    qry.*
FROM sys.query_store_plan AS pl
INNER JOIN sys.query_store_query AS qry
    ON pl.query_id = qry.query_id
INNER JOIN sys.query_store_query_text AS txt
    ON qry.query_text_id = txt.query_text_id
WHERE pl.is_forced_plan=1;

After you identify the query_id and plan_id that you want to unforce, use the following example to unforce the plan:

EXEC sp_query_store_unforce_plan @query_id = 3, @plan_id = 55
or
EXEC sp_query_store_unforce_plan 3, 55;
⚠️ **GitHub.com Fallback** ⚠️