Série – #SQL2017 – Solucionando Regressão de Planos – Query Store e Automatic Tuning

Posted on julho 13, 2017

0


plan_sql_server

Introdução

A evolução do SQL Server vem impressionando a cada dia, em sua nova versão a 2017 (Preview), tivemos a release de diversas features e recursos para otimizar e melhorar nossas rotinas diárias como Administradores de Banco de Dados e Desenvolvedores, além de trazer diversas integrações, aqui você consegue ter uma idéia da quantidade de features que estão por vim – https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017 

Hoje irei falar sobre o Automatic Tuning no SQL Server 2017, com esse recurso agora será possível não somente identificar mas sim automatizar o processo de tuning, uma vez detectado o problema o SQL Server irá aplicar as correções e ajustes automaticamente por banco de dados quando habilitado.

Query Store

Antes de navegarmos no Automatic Tuning do SQL Server 2017, é de extrema importância que você saiba do nosso famoso Query Store. O Query Store provê insights e rastreabilidade de problemas de performance, desde a versão do SQL Server 2016 não precisamos mais nos preocupar com criação de scripts para captura de consultas custosas, ou tentar identificar consultas com planos ruins ou até mesmo tentar identificar o timeframe e periodicidade de uma consulta, com o Query Store você terá todas essas informações e muito mais. Ele irá automaticamente capturar o histórico das consultas, planos e estatísticas para que você tenha um overview completo do seu ambiente de banco de dados, seu processo de identificação de problemas será agora reduzido a segundos ao invés de horas.

Automatic Tuning

O Automatic Tuning  provê insights de potencias problemas identificados em seu banco de dados quando habilitado, além disso ele mesmo irá mostrar recomendações e automagicamente  [automagically] corrigi-los para você. Normalmente um dos erros mais comuns e que causam dores de cabeças para os Administradores de Banco de Dados é a regressão de um plano [plan regression]. Antes de entrarmos no recurso vamos entender porque o mesmo foi criado.

Regressão de Plano [Plan Regression]

Vamos navegar um pouco no problema aqui, plan regression pode acontecer por diversos fatores quando o Query Optimizer compila um plano de execução para uma consulta, as vezes para a mesma consulta um novo plano de execução é gerado e em alguns casos esse novo plano é pior do que o anterior, esse é o fenômeno que chamamos de plan regression.

Como dito anteriormente existem diversos fatores que fazem com que o Query Optimizer opte por criar um novo plano de execução como por exemplo – quantidade de registros retornados na consulta, adição ou remoção de índices ou até mudança do schema da tabela afetada na consulta.

Para profissionais que trabalham com SQL Server há um bom tempo, sabe que esse tipo de problema pode causar até indisponibilidade do sistema ou drenar todos os recursos do servidor, como caso real, imagine que alguma mudança foi realizada e o Query Optimizer decide criar um novo plano de execução para uma consulta que executa em torno de 300 x por segundo em seu ambiente de produção, sua consulta que não utilizava TempDB agora começa a fazer SPILL na TempDB acarretando latência de segundos no retorno, porém como ela é executada 300 x por segundo, usuários agora começam a ter um response-time maior do que anteriormente, isso devido a escolha de uma plano pior pelo QO.

Solucionando Regressão de Planos

Bem, agora que entendemos o Query Store, Automatic Tuning e o que é Plan Regression, podemos unir toda essa explicação para atacar esse problema em questão. Nesse caso iremos começar usando o Query Store para rastrear consultas que possam ter mais de uma plano de execução, porém antes vamos habilitar o Query Store no SQL Server 2016 para o banco de dados Wide World Importers.


ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;

ALTER DATABASE [WideWorldImporters]

SET QUERY_STORE

(

OPERATION_MODE = READ_WRITE,

CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

DATA_FLUSH_INTERVAL_SECONDS = 3000,

MAX_STORAGE_SIZE_MB = 1024,

INTERVAL_LENGTH_MINUTES = 15,

SIZE_BASED_CLEANUP_MODE = AUTO,

QUERY_CAPTURE_MODE = AUTO,

MAX_PLANS_PER_QUERY = 1000

);


Utilizei essa consulta em questão para simular o plan regression, aqui mudo o parâmetro de @CustomerID fazendo com que o SQL Server em tempo de execução ajuste o plano dependendo da quantidade de registros que são retornadas pela consulta, nesse caso ele irá criar dois planos para a mesma consulta.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

USE WideWorldImporters

go

EXECUTE sp_executesql

@stmt =

N’

SELECT SO.CustomerID,

        SO.CustomerPurchaseOrderNumber,

       SO.ExpectedDeliveryDate,

       SO.OrderDate,

       SO.SalespersonPersonID,

       SOL.Quantity,

       SOL.UnitPrice,

       SOL.TaxRate

FROM Sales.Orders AS SO

INNER JOIN Sales.OrderLines AS SOL

ON SO.OrderID = SOL.OrderID

WHERE SO.CustomerID = @CustomerID

‘,

@params = N’@CustomerID INT’, @CustomerID = 1060

EXECUTE sp_executesql

@stmt =

N’

go

SELECT SO.CustomerID,

       SO.CustomerPurchaseOrderNumber,

       SO.ExpectedDeliveryDate,

        SO.OrderDate,

       SO.SalespersonPersonID,

       SOL.Quantity,

       SOL.UnitPrice,

       SOL.TaxRate

FROM Sales.Orders AS SO

INNER JOIN Sales.OrderLines AS SOL

ON SO.OrderID = SOL.OrderID

WHERE SO.CustomerID = @CustomerID

‘,

@params = N’@CustomerID INT’, @CustomerID = 471


Agora verificamos o Query Store para identificar o problema.

plano_1
(Figura 1 – QueryID = 114, Query Store.)

plano_2
(Figura 2 – QueryID = 114, Plano de Execução 29 e 28.)

Como esperávamos, a mesma consulta gerou o plano de execução Plan Id = 28 e 29, com isso temos nosso problema destacado acima. Antes de vermos como é fácil realizar o ajuste dessa consulta em questão eu trouxe aqui um painel de evolução do SQL Server, veja como podemos realizar esse tipo de tuning em diferentes versões.

<= SQL Server 2008
Se quiséssemos forçar um plano no SQL Server anteriormente, o recurso correto seria o que chamamos de Plan Guide,  processo não muito fácil e que requer uma análise profundo do problema assim como entendimento da consulta, como não tínhamos o Query Store ainda, então esse trabalho se dividia em dois momentos, primeiro capturar e identificar as consultas com problema de plano de regressão e no segundo momento adicionar o plan guide nelas. 

plan_guide
(Figura 3 – Criação de um Plan Guide no SQL Server 2008.)

SQL Server 2016
Como ilustrado anteriormente, agora com o Query Store tudo fica mais fácil de identificar e sanar, saindo do trabalhoso plan guide e indo para a solução de 1-click no report do Query Store ou usando uma procedure chamada – SP_QUERY_STORE_FORCE_PLAN.

force_plan
(Figura 4 – Visualizando Problema no Report – Top Resource Consuming Queries do Query Store.)


1
(Figura 5 – Forçando um Plano de Execução para uma Consulta no Query Store.)


SQL Server 2017
E nesse momento chega o SQL Server 2017, até o 2016 você ainda precisava identificar e verificar qual o melhor plano para aquela consulta e assim força-lo a utilizar o plano em questão, porém agora no SQL 2017 o Automatic Tuning irá fazer isso de forma automatizada uma vez habilitado no banco de dados.
Agora no CTP 2.0 do SQL Server 2017 foi adicionado uma nova system view chamada sys.dm_db_tuning_recommendations que retorna todas as recomendações realizadas pelo Automatic Tuning - https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/24/how-to-find-query-plan-choice-regressions-with-sql-server-2017-ctp2/

SELECT name, 
                 reason, 
                 score,
                 JSON_VALUE(details, '$.implementationDetails.script') as script,
                details.* 
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(details, '$.planForceDetails')
WITH 
(  
    query_id int '$.queryId',
    regressed_plan_id int '$.regressedPlanId',
    last_good_plan_id int '$.forcedPlanId'
) 
AS details
WHERE JSON_VALUE(state, '$.currentValue') = 'Active'

rec
(Figura 6 – Recomendação Gerada pelo Automatic Tuning.)

Repare aqui que a recomendação é exatamente para o Query Id = 114 aonde o Last Good Plan Id = 28, se analisarmos o plano veremos que basicamente o 28 possui um Nested Loops ao invés de um Hash Match do plano 29. Sendo assim a recomendação que será aplicada automaticamente quando habilitado é de forçar o plano de id = 28.

plan_28
(Figura 7 – Plano = 28.)


plan_29
(Figura 8 – Plano = 29.)

Sendo assim iremos fazer com que esse banco de dados, agora automaticamente tome as decisões dos melhores planos [Automatic Tuning].

ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); 

force_plan_on
(Figura 9 – Automatic Tuning Habilitado no Banco de Dados - https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning.)


Conclusão
Agora conseguimos visualizar de fim-a-fim como o Automatic Tuning traz benefícios as novas rotinas diárias assim como a administração e gerenciamento dos bancos de dados no SQL Server 2017.