PorCaio Amante
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.
Sumário
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’