Início / dba / Perguntas / 54782
Accepted
db2
Asked: 2013-12-11 06:58:37 +0800 CST

Criando guia de plano para consulta chamada por sp_executesql

  • 772

Para encurtar a história, tenho uma visão chamada vwRelatives que usa recursão CTE para construir árvores genealógicas. Destina-se a ser consultado por uma única pessoa de cada vez.

Isso é executado em cerca de um quarto de segundo:

SELECT * FROM vwRelatives WHERE person_id = 5

Isso (a forma como a consulta é executada no aplicativo) leva mais de 4,5 segundos:

exec sp_executesql N'SELECT * FROM vwRelatives WHERE person_id = @P1',N'@P1 int',5

(Observe que simplifiquei um pouco a consulta. A coisa real tem uma lista de colunas explícita e um ORDER BY, mas a WHEREsemântica é a mesma. Obtenho os mesmos sintomas com qualquer uma das versões.)

Muito provavelmente o SQL Server consegue levar person_id = 5em conta na hora de criar um plano de execução para a primeira query, mas parametrizar isso está fazendo com que toda a view seja executada e depois filtrada por person_id.

Então pensei em criar um guia de plano. E agora tenho dois problemas.

Estes são os passos que estou dando, que parecem não surtir efeito.

Primeiro, execute a consulta 'boa' para colocá-la no cache do plano...

SELECT * FROM vwRelatives WHERE person_id = 5

...em seguida, execute as etapas padrão para transformá-lo em um guia de plano...

--Get the 'good' plan
SET @xml_showplan = (
    SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
        WHERE st.text LIKE N'SELECT * FROM vwRelatives WHERE person_id = 5'
)

--Apply a plan guide to the meat of the sp_executesql query
EXEC sp_create_plan_guide 
    @name = N'vwRelatives_Test_Plan_Guide', 
    @stmt = N'SELECT * FROM vwRelatives WHERE person_id = @P1', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = N'@P1 int', 
    @hints = @xml_showplan;

Isso é concluído com êxito, mas quando executo a instrução sp_executesql original novamente, ainda leva 4,5 segundos. Tenho o Profiler em execução e os eventos Plan Guide Success e Plan Guide Unsuccessful estão selecionados. Nenhum desses eventos aparece no rastreamento.

O que estou fazendo de errado que está impedindo o SQL Server de ver este guia de plano como uma correspondência para a consulta sp_executesql?

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Para aqueles que se deparam com essa questão tentando criar um guia de plano, a sintaxe que eu tinha originalmente está correta. O motivo pelo qual não estava funcionando (suspeito - não consigo encontrar nenhuma confirmação na documentação) é que a exibição usa um CTE para recursão. Evidentemente, isso o desqualifica do uso do guia de plano.

    Meu problema original era que a exibição recursiva tinha um desempenho ruim quando uma SELECTinstrução era emitida via sp_executesql com parâmetros (o cliente é um banco de dados do Access).

    Finalmente me deparei com esta pergunta mais antiga no Stack Overflow, onde alguém está tendo basicamente o mesmo problema:

    https://stackoverflow.com/questions/4226035/why-does-a-query-slow-down-drastically-if-in-the-where-clause-a-constant-is-repl

    Eu estava começando a me perguntar se precisaria enganar/ajudar o otimizador de consulta enviando a recursão para uma função definida pelo usuário, e isso confirmou a suspeita. Mudei todos os CTEs da exibição para um UDF embutido que usa o @person_idparâmetro diretamente na âncora da recursão e agora é agradavelmente rápido mesmo com sp_executesql.

    Portanto, não é a solução que originalmente pensei que precisaria, mas vou aceitar. (Provavelmente é mais direto dessa maneira também. Não preciso me preocupar em anexar um guia de plano a cada variação sutil da consulta que o Access pode construir.)

    • 5

relate perguntas