监视本机编译的存储过程的执行Monitoring Performance of Natively Compiled Stored Procedures

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本文介绍如何监视本机编译的存储过程和其他本机编译的 T-SQL 模块的性能。This article discusses how you can monitor the performance of natively compiled stored procedures and other natively compiled T-SQL modules.

使用扩展事件Using Extended Events

使用 sp_statement_completed 扩展事件可以跟踪查询的执行情况。Use the sp_statement_completed extended event to trace execution of a query. 使用此事件或者可以选择使用针对某一特定本机编译的存储过程的 object_id 的筛选器创建一个扩展事件会话。Create an extended event session with this event, optionally with a filter on object_id for a particular natively compiled stored procedure. 在执行各种查询之后引发扩展事件。The extended event is raised after the execution of each query. 该扩展事件报告的 CPU 时间和持续时间指示该查询占用了多少 CPU 以及执行时间。The CPU time and duration reported by the extended event indicate how much CPU the query used and the execution time. 占用大量 CPU 时间的本机编译的存储过程可能具有性能问题。A natively compiled stored procedure that uses a lot of CPU time may have performance problems.

line_number,连同扩展事件中的 object_id 可用于调查该查询。line_number, along with the object_id in the extended event can be used to investigate the query. 可以使用以下查询检索过程定义。The following query can be used to retrieve the procedure definition. 可以使用行号标识该定义内的查询:The line number can be used to identify the query within the definition:

SELECT [definition]
    from sys.sql_modules
    where object_id=object_id;

使用数据管理视图和查询存储Using Data Management Views and Query Store

SQL ServerSQL Server SQL 数据库SQL Database 支持在过程级别和查询级别收集本机编译的存储过程的执行统计信息。and SQL 数据库SQL Database support collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. 由于对性能的影响,默认不启用收集执行统计信息。Collecting execution statistics is not enabled by default due to performance impact.

执行统计信息反映在系统视图 sys.dm_exec_procedure_statssys.dm_exec_query_stats 以及查询存储中。Execution statistics are reflected in the system views sys.dm_exec_procedure_stats and sys.dm_exec_query_stats, as well as in Query Store.

过程级别执行统计信息Procedure-Level Execution Statistics

SQL ServerSQL Server :可使用 sys.sp_xtp_control_proc_exec_stats (Transact-SQL) 对本机编译的存储过程启用或禁用过程级别统计信息收集。SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the procedure-level using sys.sp_xtp_control_proc_exec_stats (Transact-SQL). 以下语句针对当前实例上所有本机编译的 T-SQL 模块启用过程级别的执行统计信息收集:The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL DatabaseAzure SQL Database :可使用数据库范围配置选项 XTP_PROCEDURE_EXECUTION_STATISTICS 对本机编译的存储过程启用或禁用过程级别的统计信息收集。Azure SQL DatabaseAzure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the procedure level using the database-scoped configuration option XTP_PROCEDURE_EXECUTION_STATISTICS. 以下语句对当前数据库中的所有本地编译的 T-SQL 模块启用过程级别的执行统计信息收集:The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE
    SCOPED CONFIGURATION
    SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

查询级别执行统计信息Query-Level Execution Statistics

SQL ServerSQL Server :可使用 sys.sp_xtp_control_query_exec_stats (Transact-SQL) 对本机编译的存储过程启用或禁用查询级别的统计信息收集。SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the query-level using sys.sp_xtp_control_query_exec_stats (Transact-SQL). 以下语句对当前实例的所有本机编译的 T-SQL 模块启用查询级别的执行统计信息收集:The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL DatabaseAzure SQL Database :可使用数据库范围配置选项 XTP_QUERY_EXECUTION_STATISTICS 对本机编译的存储过程启用或禁用语句级别统计信息收集。Azure SQL DatabaseAzure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the statement level using the database-scoped configuration option XTP_QUERY_EXECUTION_STATISTICS. 以下语句对当前数据库中的所有本机编译的 T-SQL 模块启用查询级别的执行统计信息收集:The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE
    SCOPED CONFIGURATION
    SET XTP_QUERY_EXECUTION_STATISTICS = ON;

示例查询Sample Queries

在你收集统计信息后,可以使用 sys.dm_exec_procedure_stats (Transact-SQL) 针对某一过程查询本机编译的存储过程的执行统计信息,以及使用 sys.dm_exec_query_stats (Transact-SQL) 针对查询来查询本机编译的存储过程的执行统计信息。After you collect statistics, the execution statistics for natively compiled stored procedures can be queried for a procedure with sys.dm_exec_procedure_stats (Transact-SQL), and for queries with sys.dm_exec_query_stats (Transact-SQL).

下面的查询在统计信息收集后返回当前数据库中本机编译的存储过程的过程名称和执行统计信息:The following query returns the procedure names and execution statistics for natively compiled stored procedures in the current database, after statistics collection:

SELECT
        object_id,
        object_name(object_id) as 'object name',
        cached_time,
        last_execution_time,  execution_count,
        total_worker_time,    last_worker_time,
        min_worker_time,      max_worker_time,
        total_elapsed_time,   last_elapsed_time,
        min_elapsed_time,     max_elapsed_time
    from
        sys.dm_exec_procedure_stats
    where
        database_id = db_id()
        and
        object_id in
            (
            SELECT object_id
                from sys.sql_modules
                where uses_native_compilation=1
            )
    order by
        total_worker_time desc;

下面的查询按总工作线程时间的降序返回当前数据库中已为其收集了统计信息的本机编译的存储过程中所有查询的查询文本以及执行统计信息:The following query returns the query text as well as execution statistics for all queries in natively compiled stored procedures in the current database for which statistics have been collected, ordered by total worker time, in descending order:

SELECT
        st.objectid,
        object_name(st.objectid) as 'object name',
        SUBSTRING()
            st.text,
            (qs.statement_start_offset/2) + 1,
            ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
            ) as 'query text',
        qs.creation_time,
        qs.last_execution_time,   qs.execution_count,
        qs.total_worker_time,     qs.last_worker_time,
        qs.min_worker_time,       qs.max_worker_time,
        qs.total_elapsed_time,    qs.last_elapsed_time,
        qs.min_elapsed_time,      qs.max_elapsed_time
    FROM
                    sys.dm_exec_query_stats qs
        cross apply sys.dm_exec_sql_text(sql_handle) st
    WHERE
        st.dbid = db_id()
        and
        st.objectid in
            (SELECT object_id
                from sys.sql_modules
                where uses_native_compilation=1
            )
    ORDER BY
        qs.total_worker_time desc;

查询执行计划Query Execution Plans

本机编译的存储过程支持 SHOWPLAN_XML(估计的执行计划)。Natively compiled stored procedures support SHOWPLAN_XML (estimated execution plan). 可以使用该估计的执行计划检查查询计划,以便找到任何错误计划问题。The estimated execution plan can be used to inspect the query plan, to find any bad plan issues. 错误计划的常见原因是:Common reasons for bad plans are:

  • 在创建过程前未更新统计信息。Stats were not updated before the procedure was created.

  • 缺失索引Missing indexes

通过执行以下 Transact-SQLTransact-SQL获取 Showplan XML:Showplan XML is obtained by executing the following Transact-SQLTransact-SQL:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO  

或者,在 SQL Server Management StudioSQL Server Management Studio中,选择过程名称并且单击 “显示估计的执行计划”Alternatively, in SQL Server Management StudioSQL Server Management Studio, select the procedure name and click Display Estimated Execution Plan.

本机编译的存储过程的估计的执行计划显示过程中查询的查询运算符和表达式。The estimated execution plan for natively compiled stored procedures shows the query operators and expressions for the queries in the procedure. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 对于本机编译的存储过程,并不支持所有 SHOWPLAN_XML 属性。does not support all SHOWPLAN_XML attributes for natively compiled stored procedures. 例如,与查询优化器开销相关的属性不是针对过程的 SHOWPLAN_XML 的一部分。For example, attributes related to query optimizer costing are not part of the SHOWPLAN_XML for the procedure.

另请参阅See Also

本机编译的存储过程Natively Compiled Stored Procedures