SQL Server: Find who forced a plan in Query Store with this new XEvent

The latest Cumulative Update for SQL Server 2019 has been released this week on Monday. It brings many bug fixes and some small improvements.
One of these improvements is the addition of an extended event to identify the users forcing or unforcing an execution plan via the Query Store.

In this blog post, I will test this new XEvent.

For details about the latest CU see: KB5011644 – Cumulative Update 16 for SQL Server 2019
There is a tiny KB dedicated for this new XEvent, see: KB5012964 – Improvement: Add an XEvent for tracking manual user plan forcing and unforcing

What this new event does is very simple, and it’s what is described in its KB:

An Extended Event (XEvent), query_store_plan_forcing_user_change, is added to optionally track when users manually force or unforce a plan for a particular query in the Query Store.

Query Store Extended Events

This new event is added to an already well-stocked list of extended events available around the Query Store.
The following query lists 85 XEvents.

SELECT o.name         AS [Object-Name]
	, o.description  AS [Object-Descr]
FROM sys.dm_xe_packages AS p
	JOIN  sys.dm_xe_objects AS o
		ON p.guid = o.package_guid
WHERE o.object_type = 'event'
  AND p.name = 'qds'
  AND o.name LIKE '%query_store%'

The new extended event, query_store_plan_forcing_user_change, comes with a few fields related to the plan being forced, the query, and most importantly the “is_forced_plan” field.
When a plan is forced it is set to True. It is set to false when the user unforce a plan.

Parameter Sensitive plan (parameter sniffing) demo

To test this extended event here is a simple demo of parameter sniffing using the AdventureWorks database.

Here is the preparation script if you want to follow along:

ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
go

use [AdventureWorks]
go

DROP PROC IF EXISTS dbo.GetAverageSalary;
DROP TABLE IF EXISTS dbo.Employees;
go
create table dbo.Employees (
	ID int not null,
	Number varchar(32) not null,
	Name varchar(100) not null,
	Salary money not null,
	Country varchar(64) not null,
	constraint PK_Employees	primary key clustered(ID)
);

;with N1(C) as (select 0 union all select 0) -- 2 rows
	,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
	,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
	,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
	,N5(C) as (select 0 from N4 as T1 cross join N4 as T2 ) -- 65,536 rows
	,Nums(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.Employees(ID, Number, Name, Salary, Country)
	select 
		Num, 
		convert(varchar(5),Num), 
		'USA Employee: ' + convert(varchar(5),Num), 
		40000,
		'USA'
	from Nums;

;with N1(C) as (select 0 union all select 0) -- 2 rows
	,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
	,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
	,Nums(Num) as (select row_number() over (order by (select null)) from N3)
insert into dbo.Employees(ID, Number, Name, Salary, Country)
	select 
		65536 + Num, 
		convert(varchar(5),65536 + Num), 
		'Switzerland Employee: ' + convert(varchar(5),Num), 
		40000,
		'Switzerland'
	from Nums;

create nonclustered index IDX_Employees_Country
on dbo.Employees(Country);
go

create proc dbo.GetAverageSalary @Country varchar(64)
as
	select Avg(Salary) as [Avg Salary]
	from dbo.Employees
	where Country = @Country;
go

This is a very basic employees table with a Salary column and a Country column.
To create a parameter sniffing scenario I have inserted way more employees in the USA than in Switzerland.

select Count(*) AS nbEmployees, Country
from dbo.Employees
group by Country;

So when executing the stored procedure alternating the two countries for the @Country parameter associated with a plan cache eviction (forced with the CLEAR PROCEDURE_CACHE command) we create a parameter sniffing scenario.

alter database scoped configuration clear procedure_cache
go
exec dbo.GetAverageSalary @Country='USA';
exec dbo.GetAverageSalary @Country='Switzerland';
go 50

alter database scoped configuration clear procedure_cache
go
exec dbo.GetAverageSalary @Country='Switzerland';
exec dbo.GetAverageSalary @Country='USA';
go 50

Looking at the Query Store we have 2 execution plans for the same query.
The first plan is an index Scan, it’s best for larger data sets like the USA parameter, and it’s fine for the Switzerland parameter.

The second plan is to use a Nest Loops, it’s the best plan for a small result set, so the Switzerland parameter, but it’s a disaster performance-wise for a larger number of rows like the USA parameter.

In such cases, one might want to force the first plan (with a Scan) for all parameters.

Forcing a plan

A plan can be forced with the following stored procedure or using the SSMS built-in report.

exec sp_query_store_force_plan 
	@query_id = 1
	, @plan_id = 1;

So I did force the plan manually.

The Extended Event

We can retrieve from the Extended Event the query_id, plan_id, and the is_forced_plan field.

We know who has changed forced a plan (hostname, username, etc.), what plan it is, and for what query.

Same thing for Unforcing a plan:

Is it working with automatic tuning?

I enabled the automatic tuning feature which forces the last known good plan automatically.

ALTER DATABASE AdventureWorks
	SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

It successfully force the best plan for that query but did not fire the XEvent. As the name and the description mention, it only applies to user-triggered changes.
I am not aware of another XEvent which can collect the fact a plan has been forced by the Automatic Tuning and I think this is something that should also be available.

To get this information I think we have no choice but to use the sys.query_store_plan DMV.

use AdventureWorks
go
select plan_id, query_id, query_plan_hash
	, is_forced_plan
	, plan_forcing_type_desc
from sys.query_store_plan

Querying the XE

The XEvent data can be queried in SQL to be joined with DMVs to retrieve the query text for example:

use AdventureWorks
go
;WITH cte AS (
	SELECT
		event_data.value(N'(event/@timestamp)[1]', N'datetime') AS EventDatetime
		, event_data.value('(/event/action[@name=''database_name'']/value)[1]','varchar(200)') AS [DatabaseName]
		, event_data.value('(/event/data[@name=''query_hash'']/value)[1]','varchar(200)') AS query_hash
		, event_data.value('(/event/data[@name=''plan_id'']/value)[1]','int') AS plan_id
		, event_data.value('(/event/data[@name=''query_id'']/value)[1]','int') AS query_id
		, event_data.value('(/event/data[@name=''is_forced_plan'']/value)[1]','varchar(max)') AS is_forced_plan
		, event_data.value('(/event/action[@name=''username'']/value)[1]','varchar(200)') AS username
		, event_data.value('(/event/action[@name=''client_hostname'']/value)[1]','varchar(200)') AS client_hostname
		, event_data.value('(/event/action[@name=''client_app_name'']/value)[1]','varchar(200)') AS client_app_name
	FROM (
		SELECT CAST(event_data as xml) AS event_data
		FROM sys.fn_xe_file_target_read_file('query_store_plan_forcing_user_change*.xel', null, null, null)
	) AS xe
)
SELECT
	cte.EventDatetime
	, cte.DatabaseName
	, cte.is_forced_plan
	, cte.username
	, cte.client_hostname
	, cte.client_app_name
	, t.query_sql_text
	, CAST(p.query_plan AS XML) AS query_plan
FROM cte
	JOIN sys.query_store_query AS q
		on cte.query_id = q.query_id
	JOIN sys.query_store_query_text AS t
		on t.query_text_id = q.query_text_id
	JOIN sys.query_store_plan AS p
		ON q.query_id = p.query_id
		AND cte.plan_id = p.plan_id
ORDER BY cte.EventDatetime DESC

Conclusion

Microsoft may periodically add enhancements to SQL Server without waiting for a major version change, through Cumulative Updates.
In this blog post, I tested an extended event that was just added with CU16 for SQL Server 2019.
This new XEvent could be used to monitor the forcing of plans on your databases and identify who made the change when several DBAs or Developers can perform this type of action in your environment.