主页 / dba / 问题 / 24617
Accepted
AakashM
Asked: 2012-09-21 06:29:26 +0800 CST

我可以让 SSMS 在执行计划窗格中向我显示实际查询成本吗？

  • 772

我正在修复 SQL Server 中多语句存储过程的性能问题。我想知道我应该花时间在哪些部分。

我从如何阅读查询成本中了解到，它总是一个百分比吗？即使告诉 SSMS包括实际执行计划，“查询成本（相对于批次）”数字仍然基于成本估算，这可能与实际情况相去甚远

我从Measurement Query Performance : “Execution Plan Query Cost” vs “Time Taken”中了解到，我可以使用语句围绕存储过程的调用SET STATISTICS TIME，然后我将在Messages窗格中获得如下列表：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

[etc]

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 206 ms.

每个语句都有一个输出消息。

我可以“轻松”（虽然不方便）将时间统计输出与“执行计划”窗格中的逐语句执行计划相关联，方法是对它们进行计数：第四条SQL Server Execution Times消息输出对应Query 4于“执行计划”窗格中的输出，依此类推。

但是有更好的方法吗？

sql-server performance

3 个回答

  1. Best Answer

    我不知道在 Management Studio 的计划中执行此操作的方法，但这是免费的SentryOne 计划资源管理器在您从工具中生成实际计划时将为您做的许多事情之一 - 它包括所有每个语句的运行时指标。

    • 8

  2. 一种很好的方法是使用 Profiler。在开发或测试盒上设置问题过程的“再现”，即使用参数对过程进行示例调用。然后使用 Profiler，使用 TSQL_SPs 模板创建跟踪，或从空白模板添加 SP:StmtCompleted 事件。如果尚不可用，请添加 Duration、Reads、Writes 和 CPU 列。在您的 SPID 上的跟踪中添加一个过滤器（您应该从 Management Studio 中知道）。您还可以在 Duration 中添加一个过滤器（例如，大于 1000 = 大于 1 秒）。

    您可以在 Profiler 中运行跟踪，尽管存在开销（不要在生产机器上执行此操作）或导出定义并创建服务器端跟踪。Profiler 的开销在专用的开发或测试盒上并不是什么大问题。

    运行 proc 并让它完成。您也可以在此时收集实际执行计划。

    停止跟踪并打开文件，您应该会看到 proc 的逐行细分，包括每个步骤的时间安排。我发现这比识别瓶颈的计划更有用，尽管该计划在查看相关部分进行调整时会派上用场。

    高温高压

    • 5

  3. 您还可以使用sys.dm_exec_procedure_stats和sys.dm_exec_query_stats动态管理视图。其中第一个提供了有关整个程序的信息；第二个可用于分解过程中的每个查询。一个例子如下所示：

    USE AdventureWorks;
GO
CREATE PROCEDURE dbo.Test
    @NameLike nvarchar(50)
AS
BEGIN
    SELECT
        ProductCount = COUNT_BIG(*)
    FROM Production.Product AS p
    JOIN Production.TransactionHistory AS th ON
        th.ProductID = p.ProductID
    WHERE
        p.Name LIKE @NameLike;

    SELECT
        pc.Name,
        ProductCount = COUNT_BIG(*)
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS ps ON
        ps.ProductSubcategoryID = p.ProductSubcategoryID
    JOIN Production.ProductCategory AS pc ON
        pc.ProductCategoryID = ps.ProductCategoryID
    WHERE
        p.Name LIKE @NameLike
    GROUP BY
        pc.Name
    ORDER BY
        pc.Name;
END;
GO
EXECUTE dbo.Test @NameLike = N'A%';
EXECUTE dbo.Test @NameLike = N'F%';

    过程统计：

    SELECT
    deps.last_execution_time,
    deps.last_worker_time,
    deps.last_physical_reads,
    deps.last_logical_writes,
    deps.last_logical_reads,
    deps.last_elapsed_time
FROM sys.dm_exec_procedure_stats AS deps
WHERE
    deps.database_id = DB_ID()
    AND deps.[object_id] = OBJECT_ID(N'dbo.Test', N'P');

    过程中的查询：

    SELECT
    query.the_text,
    deqs.last_execution_time,
    deqs.last_worker_time,
    deqs.last_physical_reads,
    deqs.last_logical_writes,
    deqs.last_logical_reads,
    deqs.last_clr_time,
    deqs.last_elapsed_time,
    deqs.last_rows    -- note: Only present from 2008 R2 onwards
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.[sql_handle]) AS dest
CROSS APPLY
(
    VALUES 
    (
        SUBSTRING
        (
            dest.[text], 
            deqs.statement_start_offset / 2 + 1,
            (ISNULL(NULLIF(deqs.statement_end_offset, -1), DATALENGTH(dest.[text])) - deqs.statement_start_offset) / 2 + 1
        )
    )
) AS query (the_text)
WHERE
    deqs.[sql_handle] IN
    (
        SELECT
            deps.[sql_handle]
        FROM sys.dm_exec_procedure_stats AS deps
        WHERE
            deps.database_id = DB_ID()
            AND deps.[object_id] = OBJECT_ID(N'dbo.Test', N'P')
    );
    • 4

相关问题