sys.dm_exec_trigger_stats (Transact-SQL)sys.dm_exec_trigger_stats (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

傳回快取觸發程序的彙總效能統計資料。Returns aggregate performance statistics for cached triggers. 此檢視會針對每個觸發程序包含一個資料列,而且資料列的存留期間與觸發程序維持快取狀態的時間一樣長。The view contains one row per trigger, and the lifetime of the row is as long as the trigger remains cached. 從快取中移除觸發程序時,對應的資料列也會從這個檢視中刪除。When a trigger 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.

資料行名稱Column name 資料類型Data type 描述Description
database_iddatabase_id intint 觸發程序所在的資料庫識別碼。Database ID in which the trigger resides.
object_idobject_id intint 觸發程序的物件識別碼。Object identification number of the trigger.
typetype char (2)char(2) 物件的類型:Type of the object:

TA = 組件 (CLR) 觸發程序TA = Assembly (CLR) trigger

TR = SQL 觸發程序TR = SQL trigger
Type_descType_desc Nvarchar (60)nvarchar(60) 物件類型的描述:Description of the object type:

CLR_TRIGGERCLR_TRIGGER

SQL_TRIGGERSQL_TRIGGER
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 trigger.
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.
cached_timecached_time datetimedatetime 在快取中加入觸發程序的時間。Time at which the trigger was added to the cache.
last_execution_timelast_execution_time datetimedatetime 上次執行觸發程序的時間。Last time at which the trigger was executed.
execution_countexecution_count Bigintbigint 觸發程式從上次編譯以來執行的次數。The number of times that the trigger 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 trigger since it was compiled.
last_worker_timelast_worker_time Bigintbigint 觸發程序上次執行所耗用的 CPU 時間 (以微秒為單位)。CPU time, in microseconds, that was consumed the last time the trigger was executed.
min_worker_timemin_worker_time Bigintbigint 此觸發程式在單次執行期間曾耗用的最大 CPU 時間(以微秒為單位)。The maximum CPU time, in microseconds, that this trigger has ever consumed during a single execution.
max_worker_timemax_worker_time Bigintbigint 此觸發程式在單次執行期間曾耗用的最大 CPU 時間(以微秒為單位)。The maximum CPU time, in microseconds, that this trigger has ever consumed during a single execution.
total_physical_readstotal_physical_reads Bigintbigint 這個觸發程式在編譯以來執行所執行的實體讀取總數。The total number of physical reads performed by executions of this trigger since it was compiled.
last_physical_readslast_physical_reads Bigintbigint 上次執行觸發程式時所執行的實體讀取數。The number of physical reads performed the last time the trigger was executed.
min_physical_readsmin_physical_reads Bigintbigint 此觸發程式在單次執行期間曾執行的最小實體讀取數。The minimum number of physical reads that this trigger has ever performed during a single execution.
max_physical_readsmax_physical_reads Bigintbigint 此觸發程式在單次執行期間曾執行的最大實體讀取數。The maximum number of physical reads that this trigger has ever performed during a single execution.
total_logical_writestotal_logical_writes Bigintbigint 這個觸發程式在編譯以來執行所執行的邏輯寫入總數。The total number of logical writes performed by executions of this trigger since it was compiled.
last_logical_writeslast_logical_writes Bigintbigint 上次執行觸發程式時所執行的邏輯寫入數。The number of logical writes performed the last time the trigger was executed.
min_logical_writesmin_logical_writes Bigintbigint 此觸發程式在單次執行期間曾執行的最小邏輯寫入數。The minimum number of logical writes that this trigger has ever performed during a single execution.
max_logical_writesmax_logical_writes Bigintbigint 此觸發程式在單次執行期間曾執行的最大邏輯寫入數。The maximum number of logical writes that this trigger has ever performed during a single execution.
total_logical_readstotal_logical_reads Bigintbigint 這個觸發程式在編譯以來執行所執行的邏輯讀取總數。The total number of logical reads performed by executions of this trigger since it was compiled.
last_logical_readslast_logical_reads Bigintbigint 上次執行觸發程式時所執行的邏輯讀取數。The number of logical reads performed the last time the trigger was executed.
min_logical_readsmin_logical_reads Bigintbigint 此觸發程式在單次執行期間曾執行的最小邏輯讀取數。The minimum number of logical reads that this trigger has ever performed during a single execution.
max_logical_readsmax_logical_reads Bigintbigint 此觸發程式在單次執行期間曾執行的最大邏輯讀取數。The maximum number of logical reads that this trigger has ever performed during a single execution.
total_elapsed_timetotal_elapsed_time Bigintbigint 此觸發程式完成執行的總時間(以微秒為單位)。The total elapsed time, in microseconds, for completed executions of this trigger.
last_elapsed_timelast_elapsed_time Bigintbigint 這個觸發程序最近完成執行經歷的時間 (以微秒為單位)。Elapsed time, in microseconds, for the most recently completed execution of this trigger.
min_elapsed_timemin_elapsed_time Bigintbigint 此觸發程式完成執行所經歷的最小時間(以微秒為單位)。The minimum elapsed time, in microseconds, for any completed execution of this trigger.
max_elapsed_timemax_elapsed_time Bigintbigint 此觸發程式已完成執行的最大耗用時間(以微秒為單位)。The maximum elapsed time, in microseconds, for any completed execution of this trigger.
total_spillstotal_spills Bigintbigint 此觸發程式在編譯以來執行所溢出的總頁數。The total number of pages spilled by execution of this trigger 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 trigger 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 trigger 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 trigger 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
total_page_server_readstotal_page_server_reads Bigintbigint 這個觸發程式在編譯以來執行所執行的頁面伺服器讀取總數。The total number of page server reads performed by executions of this trigger 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 trigger 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 trigger 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 trigger has ever performed during a single execution.

適用于: Azure SQL Database 超大規模資料庫Applies to: Azure SQL Database Hyperscale

備註Remarks

SQL DatabaseSQL Database,動態管理檢視不可以公開可能會影響資料庫內含項目的資訊或公開有關使用者可存取之其他資料庫的資訊。In SQL DatabaseSQL 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.

完成查詢時,會更新檢視中的統計資料。Statistics in the view are updated when a query is completed.

權限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.

範例Examples

下列範例會傳回平均經過時間所識別之前五項觸發程序的相關資訊。The following example returns information about the top five triggers identified by average elapsed time.

SELECT TOP 5 d.object_id, d.database_id, DB_NAME(database_id) AS 'database_name',   
    OBJECT_NAME(object_id, database_id) AS 'trigger_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_trigger_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_stats (Transact-sql) sys.dm_exec_query_stats (Transact-SQL)
dm_exec_procedure_stats (Transact-sql) sys.dm_exec_procedure_stats (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)sys.dm_exec_cached_plans (Transact-SQL)