We are troubleshooting a server that has high CPU utilization. After finding that the queries weren't really causing it, we started looking into compilations.
Performance Monitor is showing less than 50 Compilations/sec and less than 15 Recompilations/sec.
After running an XE session looking for compilations, we are seeing thousands of compilations per second.
This system is using triggers to audit changes. Most of the compilations are due to triggers. The triggers reference sys.dm_tran_active_transactions.
Our first thought was that maybe referencing a DMV in a trigger would cause it to compile each time, or maybe just this specific DMV would cause it. So I started testing that theory. It does compile each time, but I hadn't checked if a trigger compiles each time it is triggered when it doesn't reference the DMV and instead hardcodes a value. It was still compiling each time it got triggered. Dropping the trigger stops the compiles.
- We are using sqlserver.query_pre_execution_showplan in an XE session to track the compilations. Why is there a discrepancy between that and the PerfMon counter?
- Is it normal that you get a compilation event each time a trigger runs?
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;