Início / dba / Perguntas / 130413
Accepted
Limonka
Asked: 2016-02-26 00:44:54 +0800 CST

Plano de consulta fixo para um procedimento armazenado

  • 772

Estou explorando o tópico de guias de plano. Parece fácil, desde que vejamos os exemplos na documentação.

O problema é que não consigo encontrar nenhum exemplo funcional (nem consegui descobrir sozinho) para definir um plano fixo para um procedimento armazenado ou uma instrução dentro do procedimento.

Eu li muitas postagens de blog, mas todas elas apenas copiam/colam exemplos de documentos da Microsoft.

Alguém mais criativo/experiente?

sql-server execution-plan

2 respostas

  1. Best Answer

    Aqui está um exemplo de como forçar um plano para uma instrução dentro de um procedimento usando um guia de plano:

    USE tempdb;
GO

CREATE PROCEDURE dbo.test
AS
BEGIN

    SET NOCOUNT ON;

    SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;

END
GO

-- Use Ctrl+M to capture the execution plan
-- You will see loop joins used
EXEC dbo.test;

    Agora gere o plano de execução para esta instrução com uma estratégia de junção forçada:

    SELECT svp.name AS login_name, dbp.name AS user_name
FROM sys.database_principals AS dbp
INNER JOIN sys.server_principals AS svp
    ON dbp.sid = svp.sid
OPTION (HASH JOIN);

    Copie o XML do plano de execução e cole-o na janela de consulta. Substitua qualquer ocorrência de '(aspas simples) por ''''( quatro aspas simples).

    Seu parâmetro final para o procedimento sp_create_plan_guide estará no formato:

    @xmlPlan = 'OPTION (USE PLAN N''your plan goes here'')'

    Agora você pode ver por que quatro aspas: escape uma vez para colocar o XML dentro de uma string literal, escape duas vezes para colocar o XML dentro de uma string literal dentro de outra string literal.

    Certifique-se de que o plano XML seja colado logo após OPTION (USE PLAN N'': qualquer espaço em branco ou nova linha o tornará inválido.

    DECLARE @planXML nvarchar(max) = 'OPTION (USE PLAN N''your modified XML plan here'')';


EXEC sp_create_plan_guide 
    @name = 'UseHash', 
    @stmt = 'SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;',
    @type = 'OBJECT',
    @module_or_batch = 'dbo.test',
    @hints = @planXML
GO

-- Use Ctrl+M to capture the execution plan
-- You will see hash joins used
EXEC dbo.test;
    • 3

  2. Tendo cavado um pouco mais, encontrei uma solução alternativa em Itzik Ben-Gans et.al. Livro de programação T-SQL . Sua solução usa sp_create_plan_guide_from_handle .

    IF OBJECT_ID('dbo.GetOrders', 'P') IS NOT NULL DROP PROC dbo.GetOrders;
GO

CREATE PROC dbo.GetOrders @odate AS DATETIME
AS
    SELECT SalesOrderID, CustomerID, OrderDate /* 33145f87-1109-4959-91d6 */
    FROM Sales.SalesOrderHeader
    WHERE OrderDate >= @odate;
GO

EXEC dbo.GetOrders '99991231';
GO

DECLARE @plan_handle VARBINARY(64), @offset INT, @rc INT;
SELECT @plan_handle = plan_handle, @offset = statement_start_offset
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1 )
    LIKE N'%SELECT SalesOrderID, CustomerID, OrderDate /* 33145f87-1109-4959-91d6 */%' AND st.text NOT LIKE '%sys%';

SET @rc = @@ROWCOUNT;

IF @rc = 1
    EXEC sp_create_plan_guide_from_handle
        @name = N'MySecondPlanFromProc',
        @plan_handle = @plan_handle,
        @statement_start_offset = @offset;
ELSE
    RAISERROR ('Number of matching plans should be 1 but is %d. Plan guide not created.', 16, 1, @rc);
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.GetOrders '99991231';
GO
SET SHOWPLAN_XML OFF;
GO
    • 0

relate perguntas