主页 / dba / 问题 / 135063
Accepted
Henrik Staun Poulsen
Asked: 2016-04-13 05:12:34 +0800 CST

如何获得估计的子树成本？

  • 772

如果我有一个返回 query_plan 的查询，例如这样：

    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

然后我可以单击 query_plan 并将鼠标悬停在最左侧的图标上，其中提示文本将列出估计的子树成本。

有没有办法将它Estimated Subtree Cost作为我的查询的单独列？

我知道这个数字是无单位的，并且是指大约 20 年前的特定开发人员 PC。即便如此，我认为如果统计数据不是太远，它可能会告诉我查询需要多长时间。

我已经非常努力地向 Google 搜索此信息，但即使 dba.stackexchange.com 也是空的。

sql-server sql-server-2014

2 个回答

  1. Best Answer

    我相信您必须做一些 XML 查询工作才能获得估计的成本。

    看看这是不是你要找的：

       ;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);

    这将返回批处理中单个语句的成本。如果您需要整个批次的估计子树总成本，您可能需要在某些分组中工作。

    • 8

  2. 这是我如何使用我的问题的答案。

    我是 sp_whoisactive 的忠实粉丝。如果没有，请停止阅读；在这里下载。

    因此，我将其设置为每 10 分钟收集一次快照，如下所示：

    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

    我已经让它运行了一段时间（最多 3 天）。然后我像这样转换收集到的数据：

    /* 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

    最后我得到一个列表，显示与运行时相比，估计成本在哪里小，而工作根本没有被阻止

    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

    这是一个非常有趣的列表，但它带有一点噪音。首先，我选择忽略运行时间少于 10 分钟的快速小型作业，但该限制取决于您的情况。

    现在，找到计划不佳的工作要容易得多。
    非常感谢您的帮助。

    • 1

相关问题