Conhecendo o plan guide

Olá pessoal! Eu sou Caio Amante e quero iniciar meus posts técnicos já escrevendo de algo que em regras gerais, poucas pessoas conhecem e principalmente utilizam.

O plan guide, este nosso “amigo”, foi introduzido no SQL Server 2005 e tem como principal objetivo forçar um determinado comportamento para uma query, objeto e até mesmo a um conjunto de querys.

Eu sei que a partir do SQL Server 2016, temos um “cara” chamado QUERY STORE, que além de muito intuitivo, resolve muitos dos aspectos relacionados a melhorias em planos de execução, no entanto, acredito que ele não substitui por completo o plan guide, provando assim, sua utilidade.

Existem diversos casos onde o dba necessita refinar determinados processos e não possui acesso ao código-fonte da aplicação para efetuar as devidas alterações, isto é comum principalmente em ERP’s como: Protheus, SAP, RM e outros. É sabido por muitos que na maioria dos casos o otimizador de consulta escolhe os melhores caminhos para execução das querys, no entanto existem casos específicos que nós (dbas), realmente queremos influenciar no comportamento de determinado código SQL.

Apesar de introduzido SQL Server 2005, como já citado, o plan guide recebeu suas  principais melhorias na versão 2008 e basicamente tem sua funcionalidade em permitir que hints sejam forçadas em querys sem que seja necessária a mudança no código fonte da aplicação. A funcionalidade está disponível nas edições Standard, Developer e Enterprise.

Vamos entender melhor o que é e como funciona logo na sequência.

Existem três tipos de plan guide no SQL Server e iremos adentrar por cada um deles, mas antes iremos mostrar os principais parâmetros existentes e explicar suas funções.

 

@name: É o nome do plan guide

@stmt: A consulta a qual deve ser otimizada

@type: O tipo do plan guide, podendo ser um objeto, um SQL ou mesmo do tipo template.

@modulo_or_batch: O nome da procedure ou function, caso seja do tipo objeto, do contrário este campo ficará nulo.

@params: parâmetros definidos na sentença, usados para templates.

@hints: São hints de query e tabela que queremos forçar o sql.

 

SQL PLAN GUIDE

O primeiro tipo que iremos abordar aqui é o de uma sentença SQL sem estar encapsulada em nenhum objeto como procedure, trigger ou mesmo uma function, este é chamado de SQL plan guide.  Imagine um código SQL isolado (ad-hoc), talvez um statement enviado por um SQL Server CLR ou parte de uma query dinâmica chamada com EXEC (sql_string). Neste caso o @modulo_or_batch para a procedure sp_create_plan_guide precisa ser informado como NULL, desta forma o SQL SERVER assume que a batch e a sentença possuem o mesmo valor.

Vamos criar um cenário para deixar nosso aprendizado mais transparente ok?

Cenário:

Identificamos uma determinada query ativando paralelismo e isto está causando problemas em outros processos do banco de dados.

Abaixo a query:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Digamos que esta query seja executada o dia todo, que o paralelismo esteja sendo ativado e necessitamos de alguma forma diminuir o uso de paralelismo no servidor, principalmente nesta query, porém este código vem diretamente de uma aplicação onde o código fonte “se foi” junto com o programador, rs… quem nunca passou por isto? Teoricamente como poderíamos resolver esta situação? Query Store? Nãooo!!

 

O Plan guide se encaixa perfeitamente neste cenário, onde identificamos e precisamos controlar uma operação de um banco de dados, basicamente ele permite que sejam realizados ajustes finos em determinadas sentenças SQL. Neste caso, forçar esta query a utilizar apenas um processador.

 

EXEC sp_create_plan_guide

@name = N’plan_SalesOrderHeader_DOP1′,

@stmt = N’SELECT TOP 10 *

FROM Sales.SalesOrderHeader

ORDER BY OrderDate DESC’,

@type = N’SQL’,

@module_or_batch = NULL,

@params = NULL,

@hints = N’OPTION (MAXDOP 1)’;

 

Como é possível visualizar, executamos uma procedure chamada sp_create_plan_guide, onde informamos os parâmetros referentes ao processo que gostaríamos de mudar o comportamento do SQL Server. A partir de agora ao executarmos o mesmo comando processado inicialmente, teremos um plano de execução sem paralelismo, forçamos essa condição utilizando o plan guide.

 

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Este foi um exemplo simples utilizando uma query ad-hoc do dia-dia, no entanto, existem outros casos em que podemos utilizar o plan-guide para nos auxiliar.

OBJECT PLAN GUIDE

O tipo object de plan guide está relacionado a um statement_text, que está vinculado a uma procedure, uma function ou mesmo uma trigger dml. Ele é utilizado quando se torna necessária a mudança do comportamento de uma sentença, pertencente a um determinado objeto, desta forma na criação do plan guide, além de colocarmos a sentença, também colocamos o objeto que a mesmo pertence. Assim como anteriormente, iremos montar um cenário para entendermos melhor como funciona esta opção.

Cenário:

Identificamos que o plano de execução em uma determinada query tem variado muito e queremos forçar o plano baseando-se em um filtro (que em geral) traz uma performance adequada para a maioria das variações de parâmetro.

 

IF OBJECT_ID(N’Sales.GetSalesOrderByCountry’, N’P’) IS NOT NULL

DROP PROCEDURE Sales.GetSalesOrderByCountry;

GO

CREATE PROCEDURE Sales.GetSalesOrderByCountry

(@Country_region nvarchar(60))

AS

BEGIN

SELECT *

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID

INNER JOIN Sales.SalesTerritory AS t

ON c.TerritoryID = t.TerritoryID

WHERE t.CountryRegionCode = @Country_region;

END

GO

Esta procedure acima mostra a query que iremos implementar o plan guide, onde a mesma recebe um parâmetro de entrada, ou seja, este é o nosso objeto.

Vamos executar a procedure com o parâmetro “FR” para verificar como o SQL Server irá se comportar:

DBCC FREEPROCCACHE

EXEC [Sales].[GetSalesOrderByCountry]

@Country_region = ‘FR’

A execução com o filtro ‘FR’ utilizou o plano de execução demonstrado anteriormente.

Vamos verificar o plano para a mesma execução de procedure com outro parâmetro.

DBCC FREEPROCCACHE

EXEC [Sales].[GetSalesOrderByCountry]

@Country_region = ‘US’

 

No entanto, quando mudamos o filtro para ‘US”, é possível visualizar um comportamento do plano execução um pouco diferente.

Como nosso objetivo neste post não é efetuar uma análise de performance e sim demonstrar a funcionalidade do plan guide, vamos partir do princípio que através de uma análise recorrente e muitos testes realizados, ficou claramente identificado (ficticiamente) que o segundo plano se aplica melhor a todos os filtros desta procedure, no entanto, esta aplicação é de terceiros e não temos qualquer possibilidade de solicitar para que seja colocado a hint diretamente no código.

Neste ponto que mais uma vez entramos com o plan guide, iremos mostrar a seguir uma forma de se alterar o comportamento da execução de query através da criação de um guia de plano.

 

EXEC sp_create_plan_guide

@name =  N’Guide1′,

@stmt = N’SELECT *

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.Customer AS c

ON h.CustomerID = c.CustomerID

INNER JOIN Sales.SalesTerritory AS t

ON c.TerritoryID = t.TerritoryID

WHERE t.CountryRegionCode = @Country_region’,

@type = N’OBJECT’,

@module_or_batch = N’Sales.GetSalesOrderByCountry’,

@params = NULL,

@hints = N’OPTION (OPTIMIZE FOR (@Country_region = N”US”))’;

 

Criamos o plan guide referente a procedure que estávamos executando e podemos perceber que desta vez “setamos” o parâmetro @module_or_batch com o nome do objeto.

Neste caso estamos passando um hint para que todos os parâmetros utilizem o plano de execução igual ao do filtro ‘US’.

Ficou meio confuso certo? Explicarei melhor!

O hint “optimize for” tem como objetivo passar ao Query Optimizer a “dica” para que ele use o  mesmo plano para todas variações de filtro, utilizando como base o filtro neste caso de @country_region =’US’. Sendo assim, todos os filtros utilizarão o mesmo plano do filtro passado ao plan guide.

 

Desta forma se executarmos novamente as chamadas da procedure teremos os resultados abaixo:

DBCC FREEPROCCACHE

EXEC [Sales].[GetSalesOrderByCountry]

@Country_region = ‘FR’

DBCC FREEPROCCACHE

EXEC [Sales].[GetSalesOrderByCountry]

@Country_region = ‘US’

Podemos ver acima as duas chamadas da procedure utilizando exatamente o mesmo plano de execução. Neste ponto, acredito que alguns possam estar afirmando: “O query store faz isto e de maneira visual, minha análise é apenas através de um gráfico”. Eu concordaria plenamente,  mas o objetivo principal é observar que o plan guide permite que possamos refinar muito mais nossa análise e colocar a nossa regra sem necessidade de mexer com aplicação ou alteração de código.

 

TEMPLATE PLAN GUIDE

Este tipo de plan guide só pode ser usado para as hints PARAMETRIZATION FORCE ou PARAMETRIZATION SIMPLE.

Como o objetivo não é explicar como funciona a função PARAMETRIZATION aqui, segue os links para entendimento das opções.

https://technet.microsoft.com/en-us/library/ms175037(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms186219(v=sql.105).aspx

Para utilizarmos template plan guide, precisamos seguir alguns pré-requisitos, onde será necessário utilizarmos uma outra procedure chamada sp_get_query_template, além de “setarmos” o valor para @params, diferentemente dos outros dois tipos de plan guide, onde não existia necessidade de “setar” está variável e nem utilizar a procedure mencionada.

Cenário

Após uma análise efetuada, identificamos que determinada query poderia se beneficiar da utilização da opção PARAMETERIZATION = FORCED, no entanto, sabemos que se habilitarmos esta opção a nível de banco de dados, teríamos problemas em outras querys que estão em atividade neste mesmo banco de dados. Neste momento que os templates são úteis para nós DBAs. Através deles, podemos forçar o SQL Server utilizar apenas para uma determinada sentença a utilização da parametrização forçada. Saiba mais aqui.

 

DBCC FREEPROCCACHE;

GO

SELECT * FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.SalesOrderID = 45639;

GO

SELECT * FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.SalesOrderID = 45640;

 

 

Após limpar o cache e executarmos as duas consultas, podemos verificar que nenhuma delas foram parametrizadas, ou seja, estão seguindo o comportamento padrão do SQL Server (SIMPLE).

No entanto, sabemos que neste caso esta consulta se beneficiaria muito se a mesma fosse executada de maneira parametrizada, porém, mais uma vez apenas para fins  de aprendizado, “não temos qualquer possibilidade de alterar esta query” (afim que passe a ser parametrizada). A única forma seria forçando um comportamento do SQL Server, onde sabemos que não podemos mudar a nível de banco de dados, pois seria benéfico para este processo, porém atrapalharia muitos outros processos que estão em produção neste momento.

Para resolver este problema podemos criar um template, que irá utilizar a parametrização forçada apenas para a query passada na procedure.

Vamos ver como isto funciona:

DECLARE @sample_statement nvarchar(max);

DECLARE @paramlist nvarchar(max);

EXEC sp_get_query_template N’SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesOrderID = 45639;’, @sample_statement OUTPUT,

@paramlist OUTPUT

EXEC sp_create_plan_guide

@name = N’Template_Plan’,

@stmt = @sample_statement,

@type = N’TEMPLATE’,

@module_or_batch = NULL,

@params = @paramlist,

@hints = N’OPTION(PARAMETERIZATION FORCED)’;

 

Podemos notar que antes de criar o plan guide desta vez, passamos a query como parâmetro da procedure sp_get_query_template e nomeamos uma saída denominada @paramlist. Este citado é passado como parâmetro de entrada na criação do Plan guide, “setamos” o @type desta vez como Template, o @stmt também recebe a saída da sp_get_query_template e logicamente a hint é informada dentro do plan guide.

Vamos verificar como fica a cache após executarmos os passos acima.

DBCC FREEPROCCACHE;

GO

SELECT * FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.SalesOrderID = 45639;

GO

SELECT * FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.SalesOrderID = 45640;

Percebemos que agora foi criado um objeto do tipo prepared o qual parametrizou a consulta que passamos como template.

Abaixo segue a query na integra:

(@0 int)select * from Sales . SalesOrderHeader as h inner join Sales . SalesOrderDetail as d on h . SalesOrderID = d . SalesOrderID where h . SalesOrderID = @0

Com isso concluímos toda parte de implementação de plan guide.

Gerenciamento de Plan guide

Podemos gerenciar nossos guias de planos, para isto utilizamos a procedure chamada sp_control_plan_guide.

Temos seis valores obrigatórios possíveis  para executarmos este procedimento, são eles:

DISABLE, DISABLE ALL, ENABLE, ENABLE ALL, DROP e DROP ALL. Podemos colocar o nome do plan guide, no entanto este é opcional.

Exemplo:

Sp_control_plan_guide DISABLE,’Template_Plan’

Considerações sobre Plan guide

O SQL Server evoluiu muito e nas versões mais recentes possui features como o query store que estão chegando para auxiliar nós DBAs no troubleshooting de querys do nosso dia-dia. No entanto, acredito que o Plan guide ainda possui sua função, apesar de necessitar de muita análise e  refinamento, acredito que pode ser muito útil em casos onde sabemos que a utilização de determinadas hints são benéficas ao ambiente.

Todos os cenários foram criados, forçando um ambiente para se gerar a necessidade da utilização do Plan-guide, no entanto, todos foram cenários fictícios, em momento algum o objetivo foi criar ambientes focados em analises verdadeiras ou onde a leitura de páginas e IO fizessem a diferença. Reitero que todo cenário foi criado apenas para demonstrar a funcionalidade do Plan guide.

Muito obrigado a todos, quaisquer dúvidas fiquem à vontade para entrar em contato.

Caio Amante

caio.amante@dataside.com.br

https://www.facebook.com/caio.amante

Referencia: Microsoft SQL Server Internals 2012 – Kalen Delaney