If you use Automatic Plan Correction, and thus also Query Store, you may wonder how was a plan forced: manually or automatically with APC? The type of forced plan is tracked in sys.query_store_plan, and you can use this simple query to determine how a plan was forced:
SELECT p.is_forced_plan, p.plan_forcing_type, p.plan_forcing_type_desc FROM sys.query_store_plan p WHERE p.is_forced_plan = 1; GO
Now, this information is a good start, but we can do better if we join to sys.dm_db_tuning_recommendations and the other Query Store view. The query below is not pretty I know 🙂 but it does the job:
;WITH ForcedPlanInfo AS (SELECT [FP].[QueryId] [QueryID], [Reason], JSON_VALUE(state, '$.currentValue') [CurrentValue], [FP].[RegressedPlanId] [RegressedPlanID], [FP].[ForcedPlanID] [ForcedPlanID], [execute_action_start_time], JSON_VALUE(details, '$.implementationDetails.script') [Script] FROM [sys].[dm_db_tuning_recommendations] CROSS APPLY OPENJSON (Details, '$.planForceDetails') WITH ( [QueryId] INT '$.queryId', [RegressedPlanId] INT '$.regressedPlanId', [ForcedPlanID] INT '$.recommendedPlanId' ) AS FP ) SELECT qt.query_text_id, q.query_id, p.plan_id, p.is_forced_plan, p.plan_forcing_type, p.plan_forcing_type_desc, qt.query_sql_text, TRY_CAST(p.query_plan AS XML), tr.QueryID, tr.Reason, tr.CurrentValue, tr.RegressedPlanID, tr.ForcedPlanID, tr.execute_action_start_time, tr.Script FROM sys.query_store_plan p LEFT OUTER JOIN ForcedPlanInfo tr ON p.plan_id = tr.ForcedPlanID JOIN sys.query_store_query q ON p.query_id = q.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE p.is_forced_plan = 1; GO
As you can see, I broke the query output into two parts because it’s fairly wide, and for the plan that was forced manually, there are columns that are not relevant.
The only additional item I would want to display in this output is the user that forced the plan manually. Unfortunately, this information is not currently captured by SQL Server. You will not find it in an Extended Event or in the SQL Server ERRORLOG; hopefully that will be addressed soon.
Hope this helps those of you that are using Query Store and Automatic Plan Correction!