Série – #SQL2017 – Monitoramento de Ações do Automatic Tuning com XE

Posted on julho 26, 2017

0


Introdução

No blog post anterior – Solucionando Regressão de Planos com o Query Store e Automatic Tuning falamos sobre os benefícios e melhores práticas de utilização do automatic tuning no seu ambiente de banco de dados SQL Server com o propósito de encontrar, analisar e resolver problemas de regressão de planos de execução.

Agora, iremos utilizar a força do extended events para nos mostrar informações e rastreabilidade do automatic tuning dentro do SQL Server 2017 RC1 disponível para download aqui. Iremos utilizar o XE para identificar quando um plano for marcado para regressão assim podemos ter maiores insights do que está acontecendo na engine do SQL Server.

Automatic Tuning com Extended Events

Image result for automatic icon

Normalmente o Automatic Tuning detecta a regressão de um plano mas não aplica nenhuma modificação quando a diferença entre o plano anterior e o atual não é alta o suficiente, porém a partir do SQL Server 2017 RC1 utilizando o EX é possível capturar esse tipo de informação e identificar assim a diferença entre os planos de execução.

plano_1

(Figura 1 – Quantidade de Planos de Execução para Query Id = 28.)

plano_2

(Figura 2 – Comparação dos Planos de Execução.)

Sendo assim, agora você pode utilizar 2 extended events para rastrear as sugestões e modificações do automatic tuning.

1. Planos Não Corrigidos

Nesse extended events você irá visualizar as recomendações geradas porém não realizadas por causa da pequena diferença de ganho.

CREATE EVENT SESSION [APC – plans that are not corrected] ON SERVER

ADD EVENT qds.automatic_tuning_plan_regression_detection_check_completed(
WHERE ((([is_regression_detected]=(1))
  AND ([is_regression_corrected]=(0)))
  AND ([option_id]=(1))))
ADD TARGET package0.event_file(SET filename=N’plans_that_are_not_corrected’)
WITH (STARTUP_STATE=ON);
GO

ALTER EVENT SESSION [APC – plans that are not corrected] ON SERVER STATE = start;
GO

2. Reversão da Correção

Nesse extended events você irá visualizar os planos que foram forçados pelo automatic tuning porém em algum momento após essa modificação ele resolveu reverter a mudança devido a não melhoria da consulta, isso quer dizer que mesmo após a implementação do plano o automatic tuning irá monitorar os planos que foram forçados de forma ativa e caso identifique que esse não é o melhor irá reverter ou adicionar um melhor plano.

CREATE EVENT SESSION [APC – reverted plan corrections] ON SERVER

ADD EVENT qds.automatic_tuning_plan_regression_verification_check_completed(
    WHERE ((([is_regression_detected]=(1))
      AND ([is_regression_corrected]=(1)))
      AND ([option_id]=(1))))
ADD TARGET package0.event_file(SET filename=N’reverted_plan_corrections’)
WITH (STARTUP_STATE=ON);
GO

ALTER EVENT SESSION [APC – reverted plan corrections] ON SERVER STATE = start;
GO

post de referência – https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/07/18/monitoring-automatic-tuning-actions-using-xevents/