Início / dba / Perguntas / 29742
Accepted
peter
Asked: 2012-12-04 18:45:26 +0800 CST

Plano de consulta estimado versus real com chamadas de função

  • 772

Eu tenho esta consulta no servidor SQL, uma consulta de replicação de mesclagem:

SELECT DISTINCT
    b.tablenick,
    b.rowguid,
    c.generation,
    sys.fn_MSgeneration_downloadonly
    (
        c.generation,
        c.tablenick
    )
FROM #belong b
LEFT OUTER JOIN dbo.MSmerge_contents c ON 
    c.tablenick = b.tablenick
    AND c.rowguid = b.rowguid;

O plano de consulta estimado inclui informações sobre 3 consultas:

  1. A consulta acima
  2. A chamada de função para fn_MSgeneration_downloadonly
  3. A chamada de função para fn_MSArticle_has_downloadonly_property

O plano de consulta real inclui apenas estas informações:

  1. A consulta acima

Nada sobre as funções. Por que as informações da função estão faltando no plano real?

Eu tentei essas opções:

SET STATISTICS PROFILE ON
SET STATISTICS XML ON

O que criou um plano real, mas faltavam as partes 2 e 3, como quando usei a opção de plano de consulta real no Management Studio.

Se, por exemplo, eu fosse usar o Profiler para capturar as informações sobre as chamadas de função, quais eventos eu selecionaria?

Não encontrei uma resposta especificamente relacionada aos planos de consulta, mas fiz o perfil SP:StmtStarting e SP:StmtCompleted e mostrou as chamadas de função.

sql-server merge-replication

1 respostas

  1. Best Answer

    E nada sobre as funções. Por que as informações da função estão faltando no plano real?

    Isso ocorre por design, por motivos de desempenho.

    As funções que contêm BEGINe ENDna definição criam um novo quadro de pilha T-SQL para cada linha de entrada. Em outras palavras, o corpo da função é executado separadamente para cada linha de entrada . Esse único fato explica a maioria dos problemas de desempenho associados às funções escalares e multi-instrução do T-SQL (observe que as funções com valor de tabela em linha não usam a BEGIN...ENDsintaxe).

    No contexto da sua pergunta, isso resultaria em uma SHOWPLANsaída completa para cada linha. A saída do plano XML é bastante detalhada e cara de produzir, portanto, produzir uma saída completa para cada linha seria uma má ideia em termos gerais.

    Exemplo

    Considere a função escalar T-SQL abaixo, criada no banco de dados de exemplo AdventureWorks , que retorna o nome de um produto dado seu ID:

    CREATE FUNCTION dbo.DumbNameLookup
(
    @ProductID integer
)
RETURNS dbo.Name
AS
BEGIN
    RETURN
    (
        SELECT
            p.Name
        FROM Production.Product AS p
        WHERE
            p.ProductID = @ProductID
    );
END;

    Plano de pré-execução

    Um plano de pré-execução (plano estimado no SSMS) mostra informações do plano para a instrução pai e chamadas de função aninhadas:

    -- Pre-execution plan shows main query and nested function call
SET SHOWPLAN_XML ON;
GO
SELECT dbo.DumbNameLookup(1);
GO
SET SHOWPLAN_XML OFF;

    Saída SSMS:

    Plano de pré-execução do SSMS

    O mesmo XML visualizado no SQL Sentry Plan Explorer mostra a natureza aninhada das chamadas com mais clareza:

    Plano de pré-execução de PE

    Saída pós-execução

    O SSMS mostra detalhes apenas para a consulta principal quando a saída do plano de pós-execução é solicitada:

    -- Post-execution plan shows main query only
SET STATISTICS XML ON;
SELECT dbo.DumbNameLookup(1);
SET STATISTICS XML OFF;

    Pós-execução do SSMS

    O impacto no desempenho de fazer o contrário pode ser mostrado usando a classe de evento Showplan XML Statistics Profile no SQL Server Profiler, usando uma consulta que chama a função várias vezes (uma vez por linha de entrada):

    SELECT TOP (5)
    p.ProductID,
    dbo.DumbNameLookup(p.ProductID)
FROM Production.Product AS p;

    Saída do criador de perfil:

    Saída de rastreamento

    Existem cinco planos separados de pós-execução para as execuções de função e um para a consulta pai. Os cinco planos de função se parecem com isso no painel inferior do criador de perfil:

    Planos de função

    O plano de consulta pai é:

    plano pai

    A execução da consulta sem a TOP (5)cláusula resulta em um plano de execução completo para cada uma das 504 linhas da tabela Produto. Você provavelmente pode ver como isso ficaria rapidamente fora de controle com tabelas maiores.

    A situação para gatilhos é inversa. Eles não mostram nenhuma informação do plano de pré-execução, mas incluem um plano de pós-execução. Isso reflete a natureza baseada em conjuntos de acionadores; cada um é acionado uma vez para todas as linhas afetadas, em vez de uma vez por linha.

    • 17

relate perguntas