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.

  1. 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?
  2. Is it normal that you get a compilation event each time a trigger runs?

Repro script:

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;
up vote 17 down vote accepted

The XE event being used is leading you incorrectly to think the trigger is actually compiling every execution. There are two extended events query_pre_execution_showplan and query_post_compilation_showplan that have similar descriptions, but differ by one important word:

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.

The events aren't exactly the same in description and occur at different times from further testing using your repro. Using a much larger event session definition, it is easy to see where compilations actually are happening.

enter image description here

Here you can see the first compilation happening for the insert statements as prepared plans being auto-parameterized in the green box. The trigger is compiled in the red box and the plan is inserted into the cache as shown by the sp_cache_insert event. Then in the orange box the trigger execution gets a cache hit and reuses the trigger plan for the second INSERT statement in the batch, so it's not compiling every execution of the INSERT command and the plan does get reused as you can see with the sp_cache_hit event for the trigger.

If we run the two INSERT statements individually again after the first execution the trigger doesn't compile again as shown in the events below:

enter image description here

Here the first statement encounters a cache hit for the prepared auto-parameterized version of the statement in cache but a miss for the adhoc batch that was submitted. The trigger gets a cache hit and doesn't compile again as shown in the red block of events. The green block of events repeats this behavior for the second INSERT statement run as a separate batch. However, in every case you still see the query_pre_execution_showplan event firing which I can only attribute to the difference in being optimized vs compiled in the event description, but the trigger isn't compiling for every execution as shown by these series of events.

  • If you look at the first screenshot, the uncached sql_batch_statistics event is in the collection but only fires for the first execution of the sql batch when the cache is cleared and the auto-parameterized plan for the INSERTs is not in the plan cache. After that the uncached_sql_batch_statistics event doesn't fire again. – Jonathan Kehayias Jun 1 '17 at 19:35
  • The query_post_compilation_showplan showed just a few compilations on the triggers but not the massive amount we were seeing on the other event. We did find some interesting nuggets with query_post_compilation_showplan. Thanks for the information, Jonathan! – Tara Kizer Jun 1 '17 at 21:26

No. Triggers are not always recompiled. Simple query statements, however, do not get their plans cached and would therefore always be recompiled.

Triggers do get recompiled if the number of rows in inserted or deleted changes significantly. See: https://technet.microsoft.com/en-us/library/ms181055.aspx

I don't know if they have the same in XEvents, but in SQL Trace, a recompile has a event subclass that tells you why it was recompiled. That's explained in the same link above.

  • 1
    We were looking at compilations rather than recompilations. We are going to look at the server tomorrow and check if it's due to being a simple query statement or if it's due to the number of rows. Thanks! – Tara Kizer May 31 '17 at 19:22

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.