Czy wyzwalacze kompilują się za każdym razem?

Rozwiązujemy problemy z serwerem o wysokim wykorzystaniu procesora. Po stwierdzeniu, że zapytania tak naprawdę nie były przyczyną, zaczęliśmy szukać kompilacji.

Monitor wydajności pokazuje mniej niż 50 kompilacji / sek. I mniej niż 15 rekompilacji / sek.

Po uruchomieniu sesji XE w poszukiwaniu kompilacji widzimy tysiące kompilacji na sekundę.

Ten system używa wyzwalaczy do kontrolowania zmian. Większość kompilacji wynika z wyzwalaczy. Wyzwalacze odwołują się do sys.dm_tran_active_transactions.

Naszą pierwszą myślą było, że może odwołanie się do DMV w wyzwalaczu spowoduje, że kompiluje się za każdym razem, a może właśnie ten konkretny DMV to spowoduje. Więc zacząłem testować tę teorię. Kompiluje się za każdym razem, ale nie sprawdziłem, czy wyzwalacz kompiluje się za każdym razem, gdy jest wyzwalany, gdy nie odwołuje się do DMV i zamiast tego zapisuje wartość na stałe. Wciąż się kompilował za każdym razem, gdy był uruchamiany. Upuszczenie wyzwalacza powoduje zatrzymanie kompilacji.

1. Używamy narzędzia sqlserver.query_pre_execution_showplan w sesji XE do śledzenia kompilacji. Dlaczego istnieje rozbieżność między tym a licznikiem PerfMon?
2. Czy to normalne, że zdarzenie kompilacji występuje za każdym razem, gdy uruchamiany jest wyzwalacz?

Skrypt 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;

Użyte zdarzenie XE prowadzi niepoprawnie do myślenia, że ​​wyzwalacz faktycznie kompiluje każde wykonanie. Istnieją dwa rozszerzone zdarzenia query_pre_execution_showplan i query_post_compilation_showplan, które mają podobne opisy, ale różnią się jednym ważnym słowem:

zapytanie_pre_execution_showplan

Występuje po skompilowaniu instrukcji SQL. To zdarzenie zwraca reprezentację XML szacunkowego planu zapytań, który jest generowany, gdy zapytanie jest zoptymalizowane . Korzystanie z tego zdarzenia może mieć znaczny wpływ na wydajność, dlatego należy go używać tylko podczas rozwiązywania problemów lub monitorowania określonych problemów przez krótki czas.

zapytanie_post_compilation_showplan

Występuje po skompilowaniu instrukcji SQL. To zdarzenie zwraca reprezentację XML szacunkowego planu zapytań, który jest generowany podczas kompilacji zapytania . Korzystanie z tego zdarzenia może mieć znaczny wpływ na wydajność, dlatego należy go używać tylko podczas rozwiązywania problemów lub monitorowania określonych problemów przez krótki czas.

Zdarzenia nie są dokładnie takie same w opisie i występują w różnych momentach od dalszych testów przy użyciu twojego repro. Korzystając ze znacznie większej definicji sesji zdarzeń, łatwo jest zobaczyć, gdzie faktycznie odbywają się kompilacje.

Tutaj możesz zobaczyć pierwszą kompilację instrukcji wstawiania, gdy przygotowane plany są automatycznie parametryzowane w zielonym polu. Wyzwalacz jest kompilowany w czerwonym polu, a plan jest wstawiany do pamięci podręcznej, jak pokazuje zdarzenie sp_cache_insert. Następnie w pomarańczowym polu wykonanie wyzwalacza dostaje trafienie do pamięci podręcznej i ponownie wykorzystuje plan wyzwalania dla drugiej instrukcji INSERT w partii, więc nie kompiluje się przy każdym wykonaniu polecenia INSERT, a plan jest ponownie wykorzystywany, jak widać ze zdarzeniem sp_cache_hit dla spustu.

Jeśli po pierwszym wykonaniu ponownie uruchomimy dwie instrukcje INSERT ponownie, wyzwalacz nie zostanie ponownie skompilowany, jak pokazano w poniższych zdarzeniach:

Tutaj pierwsza instrukcja napotyka trafienie w pamięć podręczną dla przygotowanej automatycznie sparametryzowanej wersji instrukcji w pamięci podręcznej, ale brakowało przesłanej partii ad hoc. Wyzwalacz dostaje trafienie do pamięci podręcznej i nie kompiluje się ponownie, jak pokazano w czerwonym bloku zdarzeń. Zielony blok zdarzeń powtarza to zachowanie dla drugiej instrukcji INSERT uruchamianej jako osobna partia. Jednak w każdym przypadku nadal widzisz uruchamianie zdarzenia query_pre_execution_showplan, które mogę przypisać tylko różnicę w optymalizacji i kompilacji w opisie zdarzenia, ale wyzwalacz nie jest kompilowany dla każdego wykonania, jak pokazuje ta seria zdarzeń.

Nie. Wyzwalacze nie zawsze są rekompilowane. Proste instrukcje zapytań nie pobierają jednak swoich planów i dlatego zawsze będą ponownie kompilowane.

Wyzwalacze są ponownie kompilowane, jeśli liczba wierszy we wstawionych lub usuniętych zmianach ulegnie znaczącej zmianie. Zobacz: https://technet.microsoft.com/en-us/library/ms181055.aspx

Nie wiem, czy mają to samo w XEventach, ale w SQL Trace rekompilacja ma podklasę zdarzeń, która mówi ci, dlaczego została zrekompilowana. Wyjaśnia to ten sam link powyżej.