sys.dm_exec_procedure_stats (Transact-SQL)sys.dm_exec_procedure_stats (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

返回缓存存储过程的聚合性能统计信息。Returns aggregate performance statistics for cached stored procedures. 该视图为每个缓存的存储过程计划都返回一行,行的生存期与存储过程保持缓存状态的时间一样长。The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. 在从缓存中删除存储过程时,也将从该视图中删除对应行。When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. 此时, 将引发类似于sys.databases的性能统计信息 SQL 跟踪事件。At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

Azure SQL DatabaseAzure SQL Database 中,动态管理视图不能公开将影响数据库包含的信息,也不能公开有关用户可以访问的其他数据库的信息。In Azure SQL DatabaseAzure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. 为了避免公开此信息, 每个包含不属于所连接的租户的数据的行都将被筛选掉。To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out.

备注

每次执行时, sys.databases _exec_procedure_stats的结果可能会有所不同, 因为数据只反映完成的查询, 而不是仍在进行中的查询。The results of sys.dm_exec_procedure_stats may vary with each execution as the data only reflects finished queries, and not ones still in-flight. 若要从Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data Warehouse调用此, 请使用名称 _pdw_nodes_exec_procedure_statsTo call this from Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) or 并行数据仓库Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_procedure_stats.

列名Column name 数据类型Data type 描述Description
database_iddatabase_id intint 存储过程所在的数据库 ID。Database ID in which the stored procedure resides.
object_idobject_id intint 存储过程的对象标识号。Object identification number of the stored procedure.
typetype char(2)char(2) 对象的类型:Type of the object:

P = SQL 存储过程P = SQL stored procedure

PC = 程序集 (CLR) 存储过程PC = Assembly (CLR) stored procedure

X = 扩展存储过程X = Extended stored procedure
type_desctype_desc nvarchar(60)nvarchar(60) 对对象类型的说明:Description of the object type:

SQL_STORED_PROCEDURESQL_STORED_PROCEDURE

CLR_STORED_PROCEDURECLR_STORED_PROCEDURE

EXTENDED_STORED_PROCEDUREEXTENDED_STORED_PROCEDURE
sql_handlesql_handle varbinary(64)varbinary(64) 这可以用来与从该存储过程中执行的sys.databases方式的查询相关联。This can be used to correlate with queries in sys.dm_exec_query_stats that were executed from within this stored procedure.
plan_handleplan_handle varbinary(64)varbinary(64) 内存中计划的标识符。Identifier for the in-memory plan. 该标识符是瞬态的,仅当计划保留在缓存中时,它才保持不变。This identifier is transient and remains constant only while the plan remains in the cache. 此值可用于sys.databases _exec_cached_plans动态管理视图。This value may be used with the sys.dm_exec_cached_plans dynamic management view.

当本机编译的存储过程查询内存优化的表时,此项将始终为 0x000。Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
cached_timecached_time datetimedatetime 存储过程添加到缓存的时间。Time at which the stored procedure was added to the cache.
last_execution_timelast_execution_time datetimedatetime 上次执行存储过程的时间。Last time at which the stored procedure was executed.
execution_countexecution_count bigintbigint 存储过程自上次编译以来所执行的次数。The number of times that the stored procedure has been executed since it was last compiled.
total_worker_timetotal_worker_time bigintbigint 此存储过程自编译以来执行所用的 CPU 时间总量 (微秒)。The total amount of CPU time, in microseconds, that was consumed by executions of this stored procedure since it was compiled.

对于本机编译的存储过程,如果许多执行所用的时间都不到 1 毫秒,则 total_worker_time 可能不精确。For natively compiled stored procedures, total_worker_time may not be accurate if many executions take less than 1 millisecond.
last_worker_timelast_worker_time bigintbigint 上次执行存储过程所用的 CPU 时间(微秒)。CPU time, in microseconds, that was consumed the last time the stored procedure was executed. 11
min_worker_timemin_worker_time bigintbigint 此存储过程在单次执行期间所用的最小 CPU 时间 (微秒)。The minimum CPU time, in microseconds, that this stored procedure has ever consumed during a single execution. 11
max_worker_timemax_worker_time bigintbigint 此存储过程在单次执行期间曾占用的最大 CPU 时间 (微秒)。The maximum CPU time, in microseconds, that this stored procedure has ever consumed during a single execution. 11
total_physical_readstotal_physical_reads bigintbigint 此存储过程自编译以来执行的物理读取总次数。The total number of physical reads performed by executions of this stored procedure since it was compiled.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
last_physical_readslast_physical_reads bigintbigint 上次执行存储过程时所执行的物理读取次数。The number of physical reads performed the last time the stored procedure was executed.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
min_physical_readsmin_physical_reads bigintbigint 此存储过程在单次执行期间所执行的最少物理读取次数。The minimum number of physical reads that this stored procedure has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
max_physical_readsmax_physical_reads bigintbigint 此存储过程在单次执行期间所执行的最大物理读取次数。The maximum number of physical reads that this stored procedure has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
total_logical_writestotal_logical_writes bigintbigint 此存储过程自编译以来执行的逻辑写入总次数。The total number of logical writes performed by executions of this stored procedure since it was compiled.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
last_logical_writeslast_logical_writes bigintbigint 上次执行计划时更新的缓冲池页数。The number of buffer pool pages dirtied the last time the plan was executed. 如果页已变脏(已修改),则不计入写次数。If a page is already dirty (modified) no writes are counted.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
min_logical_writesmin_logical_writes bigintbigint 此存储过程在单次执行期间所执行的最少逻辑写入次数。The minimum number of logical writes that this stored procedure has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
max_logical_writesmax_logical_writes bigintbigint 此存储过程在单次执行期间所执行的最大逻辑写入次数。The maximum number of logical writes that this stored procedure has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
total_logical_readstotal_logical_reads bigintbigint 此存储过程自编译以来执行的逻辑读取总次数。The total number of logical reads performed by executions of this stored procedure since it was compiled.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
last_logical_readslast_logical_reads bigintbigint 上次执行存储过程时所执行的逻辑读取次数。The number of logical reads performed the last time the stored procedure was executed.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
min_logical_readsmin_logical_reads bigintbigint 此存储过程在单次执行期间所执行的最少逻辑读取次数。The minimum number of logical reads that this stored procedure has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
max_logical_readsmax_logical_reads bigintbigint 此存储过程在单次执行期间所执行的最大逻辑读取次数。The maximum number of logical reads that this stored procedure has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
total_elapsed_timetotal_elapsed_time bigintbigint 完成此存储过程的执行所用的总时间 (微秒)。The total elapsed time, in microseconds, for completed executions of this stored procedure.
last_elapsed_timelast_elapsed_time bigintbigint 最近一次完成此存储过程的执行所用的时间 (微秒)。The elapsed time, in microseconds, for the most recently completed execution of this stored procedure.
min_elapsed_timemin_elapsed_time bigintbigint 任何完成此存储过程的执行所用的最短时间 (微秒)。The minimum elapsed time, in microseconds, for any completed execution of this stored procedure.
max_elapsed_timemax_elapsed_time bigintbigint 任何完成此存储过程的执行所用的最长时间 (微秒)。The maximum elapsed time, in microseconds, for any completed execution of this stored procedure.
total_spillstotal_spills bigintbigint 此存储过程自编译以来的执行溢出的总页数。The total number of pages spilled by execution of this stored procedure since it was compiled.

适用对象SQL Server 2017 (14.x)SQL Server 2017 (14.x)从 CU3 开始Applies to: Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
last_spillslast_spills bigintbigint 上次执行存储过程时溢出的页数。The number of pages spilled the last time the stored procedure was executed.

适用对象SQL Server 2017 (14.x)SQL Server 2017 (14.x)从 CU3 开始Applies to: Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
min_spillsmin_spills bigintbigint 此存储过程在单次执行期间所溢出的最小页数。The minimum number of pages that this stored procedure has ever spilled during a single execution.

适用对象SQL Server 2017 (14.x)SQL Server 2017 (14.x)从 CU3 开始Applies to: Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
max_spillsmax_spills bigintbigint 此存储过程在单次执行期间所用的最大页数。The maximum number of pages that this stored procedure has ever spilled during a single execution.

适用对象SQL Server 2017 (14.x)SQL Server 2017 (14.x)从 CU3 开始Applies to: Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
pdw_node_idpdw_node_id intint 此分发所在的节点的标识符。The identifier for the node that this distribution is on.

适用于: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseApplies to: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW), 并行数据仓库Parallel Data Warehouse
total_page_server_readstotal_page_server_reads bigintbigint 此存储过程自编译以来执行的页服务器读取的总次数。The total number of page server reads performed by executions of this stored procedure since it was compiled.

适用对象:Azure SQL Database 超大规模Applies to: Azure SQL Database Hyperscale
last_page_server_readslast_page_server_reads bigintbigint 上次执行存储过程时所执行的页服务器读取次数。The number of page server reads performed the last time the stored procedure was executed.

适用对象:Azure SQL Database 超大规模Applies to: Azure SQL Database Hyperscale
min_page_server_readsmin_page_server_reads bigintbigint 此存储过程在单次执行期间所执行的最少页面服务器读取次数。The minimum number of page server reads that this stored procedure has ever performed during a single execution.

适用对象:Azure SQL Database 超大规模Applies to: Azure SQL Database Hyperscale
max_page_server_readsmax_page_server_reads bigintbigint 此存储过程在单次执行期间所执行的最大页服务器读取次数。The maximum number of page server reads that this stored procedure has ever performed during a single execution.

适用对象:Azure SQL Database 超大规模Applies to: Azure SQL Database Hyperscale

1对于启用统计信息收集时的本机编译的存储过程, 以毫秒为单位收集工作线程时间。1 For natively compiled stored procedures when statistics collection is enabled, worker time is collected in milliseconds. 如果查询执行不到 1 毫秒,则该值将为 0。If the query executes in less than a millisecond, the value will be 0.

权限Permissions

SQL ServerSQL Server上, VIEW SERVER STATE需要权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
SQL 数据库SQL Database高级层上, 需要VIEW DATABASE STATE具有数据库中的权限。On SQL 数据库SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. SQL 数据库SQL Database标准层和基本层上, 需要服务器管理员Azure Active Directory 管理员帐户。On SQL 数据库SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

备注Remarks

存储过程执行完成后,将更新该视图中的统计信息。Statistics in the view are updated when a stored procedure execution completes.

示例Examples

以下示例返回有关按平均占用时间衡量的前十个存储过程的信息。The following example returns information about the top ten stored procedures identified by average elapsed time.

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',   
    d.cached_time, d.last_execution_time, d.total_elapsed_time,  
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_procedure_stats AS d  
ORDER BY [total_worker_time] DESC;  

请参阅See Also

与执行相关的动态管理视图(和函数 transact-sql) Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL) sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL) sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL) sys.dm_exec_query_stats (Transact-SQL)
sys.databases _exec_trigger_stats (transact-sql) sys.dm_exec_trigger_stats (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)sys.dm_exec_cached_plans (Transact-SQL)