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;
The Extended Event
We can retrieve from the Extended Event the query_id, plan_id, and the is_forced_plan field.
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)', N'datetime') AS EventDatetime , event_data.value('(/event/action[@name=''database_name'']/value)','varchar(200)') AS [DatabaseName] , event_data.value('(/event/data[@name=''query_hash'']/value)','varchar(200)') AS query_hash , event_data.value('(/event/data[@name=''plan_id'']/value)','int') AS plan_id , event_data.value('(/event/data[@name=''query_id'']/value)','int') AS query_id , event_data.value('(/event/data[@name=''is_forced_plan'']/value)','varchar(max)') AS is_forced_plan , event_data.value('(/event/action[@name=''username'']/value)','varchar(200)') AS username , event_data.value('(/event/action[@name=''client_hostname'']/value)','varchar(200)') AS client_hostname , event_data.value('(/event/action[@name=''client_app_name'']/value)','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
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.