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

適用於: 是SQL Server 是Azure SQL Database 是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. 此時,就會引發效能統計資料 SQL 追蹤事件 (與 sys.dm_exec_query_stats 很相似)。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.

注意

Dm_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呼叫此,請使用dm_pdw_nodes_exec_procedure_stats的名稱。To 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 預存程序所在的資料庫識別碼。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) 這可以用來與從這個預存程式中執行的dm_exec_query_stats中的查詢相互關聯。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 dm_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. 若查詢的執行時間少於一毫秒,其值將會是 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 DatabaseSQL Database高階層級上, VIEW DATABASE STATE需要資料庫的許可權。On SQL DatabaseSQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. SQL DatabaseSQL Database [標準] 和 [基本] 層上,需要伺服器管理員Azure Active Directory 系統管理員帳戶。On SQL DatabaseSQL 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

下列範例會傳回平均經過時間所識別之前 10 項預存程序的相關資訊。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)
dm_exec_sql_text (Transact-sql) sys.dm_exec_sql_text (Transact-SQL)
dm_exec_query_plan (Transact-sql) sys.dm_exec_query_plan (Transact-SQL)
dm_exec_query_stats (Transact-sql) sys.dm_exec_query_stats (Transact-SQL)
dm_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)