性能监视器显示少于50次Compilations / sec和少于15次Recompilations / sec.
在运行XE会话寻找编译之后,我们每秒看到数千个编译.
该系统使用触发器来审核变更.大多数汇编都是触发因素.触发器引用sys.dm_tran_active_transactions.
我们的第一个想法是,可能在触发器中引用DMV会导致它每次编译,或者可能只是这个特定的DMV会导致它.所以我开始测试这个理论.它每次都会编译,但我没有检查触发器是否在每次触发时都会编译,因为它没有引用DMV而是硬编码一个值.它每次被触发时仍在编译.删除触发器会停止编译.
>我们在XE会话中使用sqlserver.query_pre_execution_showplan来跟踪编译.为什么它与PerfMon计数器之间存在差异？
>每次触发器运行时都会收到编译事件是否正常？
Repro脚本：
CREATE TABLE t1 (transaction_id int,Column2 varchar(100));
CREATE TABLE t2 (Column1 varchar(max),Column2 varchar(100));
GO
CREATE TRIGGER t2_ins
ON t2
AFTER INSERT
AS
INSERT INTO t1
SELECT (SELECT TOP 1 transaction_id FROM sys.dm_tran_active_transactions),Column2
FROM inserted;
GO
--Both of these show compilation events
INSERT INTO t2 VALUES ('row1','value1');
INSERT INTO t2 VALUES ('row2','value2');
GO
ALTER TRIGGER t2_ins
ON t2
AFTER INSERT
AS
INSERT INTO t1
SELECT 1000,Column2
FROM inserted;
GO
--Both of these show compilation events
INSERT INTO t2 VALUES ('row3','value3');
INSERT INTO t2 VALUES ('row4','value4');
DROP TRIGGER t2_ins;
--These do not show compilation events
INSERT INTO t2 VALUES ('row5','value5');
INSERT INTO t2 VALUES ('row6','value6');
DROP TABLE t1,t2;
解决方法
query_pre_execution_showplan
Occurs after a sql statement is compiled. This event returns an XML
representation of the estimated query plan that is generated when the
query is optimized. Using this event can have a significant
performance overhead so it should only be used when troubleshooting or
monitoring specific problems for brief periods of time.
query_post_compilation_showplan
Occurs after a sql statement is compiled. This event returns an XML
representation of the estimated query plan that is generated when the
query is compiled. Using this event can have a significant
performance overhead so it should only be used when troubleshooting or
monitoring specific problems for brief periods of time.
事件在描述中并不完全相同,并且在使用您的repro进一步测试的不同时间发生.使用更大的事件会话定义,很容易看到实际发生的编译.
在这里,您可以看到插入语句的第一次编译,因为准备好的计划在绿色框中自动参数化.触发器在红色框中编译,并且计划将插入到缓存中,如sp_cache_insert事件所示.然后在橙色框中,触发器执行获得缓存命中并重新使用批处理中第二个INSERT语句的触发器计划,因此它不会编译INSERT命令的每次执行,并且计划会重新使用,因为您可以看到sp_cache_hit事件触发器.
如果我们在第一次执行后再次单独运行两个INSERT语句,则触发器不会再次编译,如下面的事件所示：
这里第一个语句遇到缓存命中的缓存命中,该缓存命中包括缓存中语句的自动参数化版本,但是提交的adhoc批处理未命中.触发器获得缓存命中,并且不会再次编译,如红色事件块中所示.对于作为单独批处理运行的第二个INSERT语句,绿色事件块重复此行为.但是,在每种情况下,您仍然会看到query_pre_execution_showplan事件触发,我只能将其归因于在事件描述中优化与编译之间的差异,但触发器不会针对每个执行进行编译,如这些事件系列所示.
