Partilhar via


Criando guias de plano para consultas com parâmetros

Você pode criar um guia de plano em uma consulta com parâmetros. Uma consulta pode ser parametrizada por uma das seguintes razões:

  • A consulta é enviada usando sp_executesql.

  • Parametrização forçada está habilitada no banco de dados. Ela parametriza todas as consultas elegíveis.

  • Um guia de plano separado foi criado em uma classe de consultas a qual essa consulta pertence, especificando que elas foram parametrizadas.

Ao criar um guia de plano em uma consulta com parâmetros, você está criando essencialmente um guia de plano para todas as consultas que são parametrizadas ao mesmo formulário, só diferindo em seus valores literais constantes. Por exemplo, em um banco de dados no qual a parametrização forçada é habilitada, as duas consultas seguintes são parametrizadas ao mesmo formulário:

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 50;

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 100;

Para criar um guia de plano em consultas com parâmetros, crie um guia de plano do tipo SQL e especifique o formulário com parâmetros da consulta no procedimento armazenado sp_create_plan_guide.

Por exemplo, para obter o formulário com parâmetros de uma das consultas no exemplo anterior e criar um guia de plano nele para forçar o otimizador a usar uma junção de hash, siga estas etapas:

  1. Obtenha o formulário com parâmetros da consulta executando sp_get_query_template.

  2. Se a consulta já não estiver sendo parametrizada pelo SQL Server usando sp_executesql ou a opção SET do banco de dados PARAMETERIZATION FORCED, crie um guia de plano do tipo TEMPLATE para forçar a parametrização.

  3. Crie um guia de plano do tipo SQL na consulta com parâmetros.

O seguinte lote executa todas essas três etapas:

--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
    FROM Production.ProductModel pm 
    INNER JOIN Production.ProductInventory pi 
        ON pm.ProductModelID = pi.ProductID 
    WHERE pi.ProductID = 101 
    GROUP BY pi.ProductID, pi.Quantity 
    HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, 
@params OUTPUT;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

O guia de plano se aplicará agora a todas as consultas que são parametrizadas ao formulário especificado, mas contém valores literais constantes diferentes.