我们正在排除CPU利用率高的服务器.在发现查询不是真正导致它之后,我们开始研究编译.
性能监视器显示少于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;
解决方法
正在使用的XE事件导致您错误地认为触发器实际上正在编译每次执行.有两个扩展事件query_pre_execution_showplan和query_post_compilation_showplan具有相似的描述,但有一个重要的不同之处:
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事件触发,我只能将其归因于在事件描述中优化与编译之间的差异,但触发器不会针对每个执行进行编译,如这些事件系列所示.