Can You See Who Forced a Plan

I had an excellent group of people in Gothenburg Sweden when I taught there and I was asked: Can You See Who Forced a Plan?

I didn’t know the answer for certain, so I said what I always say: I don’t know, but I’ll see if I can find out.

Query Store System Views

One of the first places I’d look to see who forced a plan is the system views in Query Store. No, I don’t think it’ll be there, but it’s worth a look.

The obvious place it could be is sys.query_store_plan. After all, that’s where a plan will be marked as is_forced. But you look through that and there’s nothing about who forced a plan. And looking through the other views, there’s nothing showing that. So, this is a bust.

Extended Events to the rescue!

Query Store Extended Events

There is a HUGE list of Query Store specific Extended Events. 108 of ’em currently:

SELECT *
FROM sys.dm_xe_objects AS dxo
WHERE dxo.object_type = 'event'
      AND dxo.name LIKE '%query_store%';

Will one of these show me who forced a plan?

Yes.

The event query_store_plan_forcing_user_change fires when someone forces a plan. It only shows a very little information, but with a very limited set of data. This event was added in an update to SQL Server 2019. All you have to do see who is add some Actions for the nt_username or username and you’re done. It will also show when a user unforces a plan.

CREATE EVENT SESSION WhoForcedPlan
ON SERVER
    ADD EVENT qds.query_store_plan_forcing_user_change
    (ACTION
     (
         sqlserver.nt_username,
         sqlserver.username
     )
    );

The output looks something like this:

Nothing to it. The value for is_forced_plan will be True when forcing and False when un-forcing a plan.

Conclusion

Well, that was easy.

Yes and no. I had to do some testing & a bit of searching to find the event. I knew we could just look for anyone running sp_query_store_force_plan through rpc_completed (and yeah, that works even if the plan is forced through SSMS). I wanted a specific event. Happily, found one. This isn’t really well documented. There are a few blog posts out there showing it in use, but it’s clearly something we need to raise up a bit more for visibility. Hopefully this helps a bit.