Estamos solucionando um servidor que tem alta utilização da CPU. Depois de descobrir que as consultas não estavam causando isso, começamos a procurar compilações.
O Monitor de desempenho está mostrando menos de 50 compilações/s e menos de 15 recompilações/s.
Depois de executar uma sessão XE procurando por compilações, estamos vendo milhares de compilações por segundo.
Este sistema está usando gatilhos para auditar mudanças. A maioria das compilações são devido a gatilhos. Os gatilhos fazem referência a sys.dm_tran_active_transactions.
Nosso primeiro pensamento foi que talvez referenciar um DMV em um gatilho faria com que ele compilasse todas as vezes, ou talvez apenas esse DMV específico o causasse. Então comecei a testar essa teoria. Ele compila toda vez, mas eu não verifiquei se um gatilho compila toda vez que é acionado quando não faz referência ao DMV e, em vez disso, codifica um valor. Ele ainda estava compilando cada vez que era acionado. Soltar o gatilho interrompe as compilações.
- Estamos usando sqlserver.query_pre_execution_showplan em uma sessão XE para rastrear as compilações. Por que há uma discrepância entre isso e o contador PerfMon?
- É normal que você receba um evento de compilação toda vez que um gatilho é executado?
Reproduzir 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;
O evento XE que está sendo usado está levando você a pensar incorretamente que o gatilho está compilando cada execução. Existem dois eventos estendidos query_pre_execution_showplan e query_post_compilation_showplan que possuem descrições semelhantes, mas diferem por uma palavra importante:
query_pre_execution_showplan
query_post_compilation_showplan
Os eventos não são exatamente os mesmos na descrição e ocorrem em momentos diferentes de outros testes usando sua reprodução. Usando uma definição de sessão de evento muito maior, é fácil ver onde as compilações realmente estão acontecendo.
Aqui você pode ver a primeira compilação acontecendo para as instruções de inserção como planos preparados sendo parametrizados automaticamente na caixa verde. O gatilho é compilado na caixa vermelha e o plano é inserido no cache conforme mostrado pelo evento sp_cache_insert. Em seguida, na caixa laranja, a execução do gatilho obtém um acerto de cache e reutiliza o plano de gatilho para a segunda instrução INSERT no lote, portanto, não está compilando todas as execuções do comando INSERT e o plano é reutilizado, como você pode ver com o evento sp_cache_hit para o gatilho.
Se executarmos as duas instruções INSERT individualmente novamente após a primeira execução, o gatilho não compilará novamente conforme mostrado nos eventos abaixo:
Aqui, a primeira instrução encontra uma ocorrência de cache para a versão parametrizada automaticamente preparada da instrução em cache, mas uma falha para o lote ad hoc que foi enviado. O gatilho recebe um acerto de cache e não compila novamente, conforme mostrado no bloco vermelho de eventos. O bloco verde de eventos repete esse comportamento para a segunda instrução INSERT executada como um lote separado. No entanto, em todos os casos, você ainda vê o disparo do evento query_pre_execution_showplan, que só posso atribuir à diferença de ser otimizado versus compilado na descrição do evento, mas o gatilho não está compilando para cada execução, conforme mostrado por essas séries de eventos.
Não. Os gatilhos nem sempre são recompilados. Instruções de consulta simples, no entanto, não têm seus planos armazenados em cache e, portanto, sempre seriam recompilados.
Os gatilhos são recompilados se o número de linhas inseridas ou excluídas mudar significativamente. Consulte: https://technet.microsoft.com/en-us/library/ms181055.aspx
Não sei se eles têm o mesmo em XEvents, mas no SQL Trace, uma recompilação tem uma subclasse de evento que informa por que foi recompilada. Isso está explicado no mesmo link acima.