Início / dba / Perguntas / 135063
Accepted
Henrik Staun Poulsen
Asked: 2016-04-13 05:12:34 +0800 CST

como obter o custo estimado da subárvore?

  • 772

Se eu tiver uma consulta que retorne um query_plan, por exemplo, assim:

    SELECT TOP 1000 st.TEXT
    ,cp.size_in_bytes
    ,cp.plan_handle
    ,QP.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
WHERE cp.objtype = N'Adhoc'
    AND cp.usecounts = 1

Em seguida, posso clicar em um query_plan e passar o mouse sobre o ícone mais à esquerda, onde o texto da dica listará o custo estimado da subárvore.

Existe uma maneira de obter isso Estimated Subtree Costcomo uma coluna separada para minha consulta?

Entendo que o número não tem unidade e se refere a um determinado PC de desenvolvedor há cerca de 20 anos. Mesmo assim, acho que pode me dizer quanto tempo a consulta deve levar se as estatísticas não estiverem muito distantes.

Eu tentei muito pesquisar essas informações no Google, mas até mesmo dba.stackexchange.com está vazio.

sql-server sql-server-2014

2 respostas

  1. Best Answer

    Acredito que você terá que fazer algum trabalho de consulta XML para obter esse custo estimado.

    Veja se é isso que você procura:

       ;WITH XMLNAMESPACES  
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
    SELECT TOP 1000 st.text
        ,cp.size_in_bytes
        ,cp.plan_handle
        ,QP.query_plan
        ,n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
    WHERE cp.objtype = N'Adhoc'
        AND cp.usecounts = 1
    OPTION(RECOMPILE);

    Isso retornará o custo de extratos individuais dentro de um lote. Você pode precisar trabalhar em algum agrupamento se precisar do custo total estimado da subárvore para todo o lote.

    • 8

  2. Aqui está como eu usei a resposta à minha pergunta.

    Sou um grande fã de sp_whoisactive. Se você não tem isso, pare de ler; baixe aqui .

    Então, configurei para coletar um instantâneo a cada 10 minutos, assim:

    DROP TABLE dbo.HESPOmonitoring_output
DECLARE @s VARCHAR(MAX)
EXEC sp_WhoIsActive 
    @output_column_list = '[login_name][dd%][session_id][program%][sql_com%][sql_text][block%][reads][writes][physical_reads][query_plan][used_memory][tempdb%][wait%][start_time][collection_time][host%][additional%]', @get_outer_command=1, @get_additional_info=1,
    @return_schema = 1, @get_plans=1, 
    @schema = @s OUTPUT
SET @s = REPLACE(@s, '<table_name>', 'dbo.HESPOmonitoring_output')
EXEC(@s)
ALTER TABLE dbo.HESPOmonitoring_output ADD HESPOmonitoring_outputID BIGINT IDENTITY(1,1) NOT NULL
go
SET NOCOUNT ON 
DECLARE @Started DATETIME=DATEADD(DAY, 3, GETDATE())
WHILE 1 > 0 BEGIN 
    EXEC sp_WhoIsActive 
        @output_column_list = '[login_name][dd%][session_id][program%][sql_com%][sql_text][block%][reads][writes][physical_reads][query_plan][used_memory][tempdb%][wait%][start_time][collection_time][host%][additional%]', @get_outer_command=1, @get_additional_info=1,
         @get_plans=1, 
        @destination_table = 'dbo.HESPOmonitoring_output'
    WAITFOR DELAY '00:10:00'
    IF GETDATE() > @Started BREAK  
END

    Deixei rodar por um tempo (até 3 dias). Então eu converto os dados coletados assim:

    /* this query turns HESPOmonitoring_output in a table with one row per SQL statement */
Begin TRY
    DROP TABLE #hespo
END TRY
BEGIN CATCH
END CATCH
;WITH XMLNAMESPACES  
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
     SELECT top 10000 H.Start_Time, H.session_id, MAX(H.program_name) AS program_name, MAX(CAST(H.sql_command AS VARCHAR(max))) AS sql_command
    , MAX(CAST(H.sql_text AS VARCHAR(max))) AS sql_text
    , MAX(H.reads) AS reads
    , MAX(H.physical_reads) AS physical_reads
    , MAX(H.writes) AS writes
    , MAX(H.collection_time) AS collection_time
    , MAX(DATEDIFF(second, start_time, collection_time)) AS RunTime
    , MAX(HESPOmonitoring_outputID) AS MaxHESPOmonitoring_outputID
    , MAX(H.blocking_session_id) AS MaxBlocking_session_id
    , Min(H.blocking_session_id) AS MinBlocking_session_id
    , count_big(*) as RowCnt 
    , MAX(TRY_CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS DECIMAL(18,3))) AS StatementSubTreeCost
    INTO #hespo
    FROM dbo.HESPOmonitoring_output H
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
    GROUP BY H.Start_Time, H.session_id

    E, finalmente, obtenho uma lista que mostra onde o custo estimado é pequeno em comparação com o tempo de execução, sem que o trabalho seja bloqueado

    SELECT top 10000 
H.StatementSubTreeCost/NULLIF(H.RunTime, 0) AS Ratio, H.StatementSubTreeCost, H.RunTime, *
FROM #hespo H
WHERE H.MinBlocking_session_id IS NULL
AND H.RunTime>0
AND H.StatementSubTreeCost IS NOT NULL
ORDER BY 1

    É uma lista muito interessante , mas vem com um pouco de ruído. Para começar, optei por ignorar pequenos trabalhos rápidos que duram menos de 10 minutos, mas esse limite depende da sua situação.

    Agora é muito mais fácil encontrar trabalhos executados com um plano ruim.
    Muito obrigado pela sua ajuda.

    • 1

relate perguntas