原文链接：https://www.dbi-services.com/blog/sql-server-find-who-forced-a-plan-in-query-store-with-this-new-xevent/
作者：Steven Naudet
SQL Server 2019 的最新更新已发布。它带来了许多错误修复和一些小的改进。
其中一项改进是添加了一个扩展事件，以识别通过查询存储强制或取消强制执行计划的用户。
在这篇博文中，我将测试这个新的 XEvent。
有关最新 CU 的详细信息，请参阅：KB5011644 – SQL Server 2019 的累积更新 16
有一个专门用于这个新 XEvent 的小 KB，请参阅：KB5012964 - 改进：添加一个 XEvent 用于跟踪手动用户计划强制和取消强制
这个XEvent的作用非常简单，在它的知识库中是这样描述的：
添加扩展事件 (XEvent) query_store_plan_forcing_user_change 以选择性地跟踪用户何时手动强制或取消强制查询存储中特定查询的计划。
查询存储扩展事件
这个新事件被添加到查询存储中可用的扩展事件列表中。以下查询列出了 85 个 XEvent。
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%'
新的扩展事件 query_store_plan_forcing_user_change 带有一些与被强制执行的计划、查询相关的字段，最重要的是“is_forced_plan”字段。
当计划被强制执行时，它被设置为 True。当用户取消强制执行计划时，它设置为 false。
Parameter Sensitive plan（参数嗅探）demo
为了测试这个扩展事件，这里有一个使用 AdventureWorks 数据库的参数嗅探的简单演示。
如果您想跟随，这是准备脚本：
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
这是一个非常基本的员工表，其中有一个 Salary 列和一个 Country 列。
为了创建一个参数嗅探场景，我在美国插入了比在瑞士更多的员工。
select Count(*) AS nbEmployees, Country
from dbo.Employees
group by Country;
因此，当执行与计划缓存驱逐（使用 CLEAR PROCEDURE_CACHE 命令强制执行）相关的 @Country 参数交替两个国家/地区的存储过程时，我们创建了一个参数嗅探场景。
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
查看查询存储，我们有 2 个针对同一查询的执行计划。第一个计划是索引扫描，它最适合像 USA 参数这样的大型数据集，也适用于 Switzerland 参数。
第二个计划是使用 Nest Loops，它是小型结果集的最佳计划，因此是瑞士参数，但对于像 USA 参数这样的大量行，它在性能方面是灾难性的。
在这种情况下，您可能希望对所有参数强制执行第一个计划（使用扫描）。
强制执行计划
可以使用以下存储过程或使用 SSMS 内置报告来强制执行计划。
exec sp_query_store_force_plan
@query_id = 1
, @plan_id = 1;
所以我确实手动强制了这个计划。
扩展事件
我们可以从扩展事件中检索 query_id、plan_id 和 is_forced_plan 字段。
我们知道谁更改了强制计划（主机名、用户名等）、它是什么计划以及针对什么查询。
强制执行计划也是如此：
它与自动调整一起运行吗？我启用了自动调整功能，它会自动强制执行最后一个已知的好计划。
ALTER DATABASE AdventureWorks
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
它成功地为该查询强制执行最佳计划，但没有触发 XEvent。正如名称和描述所提到的，它仅适用于用户触发的更改。
我不知道另一个 XEvent 可以收集自动调整强制执行计划的事实，我认为这也应该可用。
为了获得这些信息，我认为我们别无选择，只能使用 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
查询 XE
可以在 SQL 中查询 XEvent 数据以与 DMV 连接以检索查询文本，例如：
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
结论
Microsoft 可能会定期向 SQL Server 添加增强功能，而无需等待主要版本更改。
在这篇博文中，我测试了一个刚刚添加到 SQL Server 2019 的 CU16 的扩展事件。
这个新的 XEvent 可用于监视对数据库的计划强制执行，并在多个 DBA 或开发人员可以在您的环境中执行此类操作时确定谁进行了更改。
