How was a plan forced?

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!