主页 / dba / 问题 / 175073
Accepted
Tara Kizer
Asked: 2017-06-01 10:45:33 +0800 CST

触发器每次都编译吗？

  • 772

我们正在对 CPU 利用率高的服务器进行故障排除。在发现查询并没有真正导致它之后，我们开始研究编译。

性能监视器显示低于 50 次编译/秒和低于 15 次重新编译/秒。

在运行 XE 会话以查找编译后，我们看到每秒有数千次编译。

该系统使用触发器来审核更改。大多数编译是由于触发器。触发器引用 sys.dm_tran_active_transactions。

我们的第一个想法是，也许在触发器中引用 DMV 会导致它每次都编译，或者可能只是这个特定的 DMV 会导致它。所以我开始测试这个理论。它每次都编译，但我没有检查触发器是否在每次触发时编译，当它不引用 DMV 而是硬编码一个值时。每次触发时它仍在编译。删除触发器会停止编译。

  1. 我们在 XE 会话中使用 sqlserver.query_pre_execution_showplan 来跟踪编译。为什么这与 PerfMon 计数器之间存在差异？
  2. 每次触发器运行时都会收到编译事件是否正常？

复制脚本：

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;
sql-server trigger

2 个回答

  1. Best Answer

    正在使用的 XE 事件导致您错误地认为触发器实际上正在编译每个执行。有两个扩展事件 query_pre_execution_showplan 和 query_post_compilation_showplan 具有相似的描述，但有一个重要的词不同：

    query_pre_execution_showplan

    在编译 SQL 语句后发生。此事件返回优化查询时生成的估计查询计划的 XML 表示。使用此事件可能会产生显着的性能开销，因此仅应在对特定问题进行故障排除或在短时间内监视特定问题时使用。

    query_post_compilation_showplan

    在编译 SQL 语句后发生。此事件返回编译查询时生成的估计查询计划的 XML 表示。使用此事件可能会产生显着的性能开销，因此仅应在对特定问题进行故障排除或在短时间内监视特定问题时使用。

    这些事件在描述上并不完全相同，并且发生在使用您的复制品进行进一步测试的不同时间。使用更大的事件会话定义，很容易看到编译实际发生的位置。

    在此处输入图像描述

    在这里，您可以看到插入语句的第一次编译，因为准备好的计划在绿色框中自动参数化。触发器在红框中编译，计划被插入到缓存中，如 sp_cache_insert 事件所示。然后在橙色框中，触发器执行获得缓存命中并为批处理中的第二个 INSERT 语句重用触发器计划，因此它不会编译 INSERT 命令的每次执行，并且该计划确实被重用，正如您在 sp_cache_hit 事件中看到的那样为触发器。

    如果我们在第一次执行后再次单独运行两个 INSERT 语句，则触发器不会再次编译，如下面的事件所示：

    在此处输入图像描述

    在这里，第一个语句遇到缓存中准备好的自动参数化语​​句版本的缓存命中，但提交的临时批处理未命中。触发器获得缓存命中并且不会再次编译，如红色事件块所示。绿色的事件块对作为单独批处理运行的第二个 INSERT 语句重复此行为。但是，在每种情况下，您仍然会看到 query_pre_execution_showplan 事件触发，我只能将其归因于事件描述中优化与编译的差异，但触发器并未针对这些事件系列所示的每次执行进行编译。

    • 20

  2. 不，触发器并不总是重新编译。然而，简单的查询语句不会缓存它们的计划，因此总是会重新编译。

    如果插入或删除的行数发生显着变化，触发器会重新编译。请参阅：https ://technet.microsoft.com/en-us/library/ms181055.aspx

    我不知道它们在 XEvents 中是否相同，但在 SQL Trace 中，重新编译有一个事件子类，它告诉您为什么要重新编译它。这在上面的同一链接中进行了解释。

    • 13

相关问题