sys.dm_exec_query_stats (Transact-SQL)sys.dm_exec_query_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

傳回 SQL ServerSQL Server中之快取查詢計劃的彙總效能統計資料。Returns aggregate performance statistics for cached query plans in SQL ServerSQL Server. 在此檢視中,快取計畫內的每個查詢陳述式各包含一個資料列,而資料列的存留期取決於計畫本身。The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. 從快取移除計畫時,對應的資料列也會從這個檢視中刪除。When a plan is removed from the cache, the corresponding rows are eliminated from this view.

注意

  • Dm_exec_query_stats的結果可能會隨著每次執行而不同,因為資料只會反映完成的查詢,而不是仍在進行中的查詢。The results of sys.dm_exec_query_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_query_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_query_stats.
資料行名稱Column name 資料類型Data type 描述Description
sql_handlesql_handle Varbinary (64)varbinary(64) 這是一個標記,可唯一識別查詢所屬的批次或預存程式。Is a token that uniquely identifies the batch or stored procedure that the query is part of.

sql_handle,連同statement_start_offsetstatement_end_offset,可以藉由呼叫sys.databases dm_exec_sql_text動態管理函數,來抓取查詢的 sql 文字。sql_handle, together with statement_start_offset and statement_end_offset, can be used to retrieve the SQL text of the query by calling the sys.dm_exec_sql_text dynamic management function.
statement_start_offsetstatement_start_offset intint 表示資料列於其批次或保存物件的文字中所描述之查詢的起始位置 (由 0 開始並以位元組為單位)。Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offsetstatement_end_offset intint 表示資料列於其批次或保存物件的文字中所描述之查詢的結束位置 (由 0 開始並以位元組為單位)。Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. 在之前SQL Server 2014 (12.x)SQL Server 2014 (12.x)的版本中,-1 的值表示批次的結尾。For versions before SQL Server 2014 (12.x)SQL Server 2014 (12.x), a value of -1 indicates the end of the batch. 已不再包含尾端的註解。Trailing comments are no longer included.
plan_generation_numplan_generation_num Bigintbigint 可用於重新編譯之後區分計畫執行個體的序號。A sequence number that can be used to distinguish between instances of plans after a recompile.
plan_handleplan_handle Varbinary (64)varbinary(64) 這是一個標記,可唯一識別已執行之批次的查詢執行計畫,且其計畫位於計畫快取中,或目前正在執行。Is a token that uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache, or is currently executing. 此值可以傳遞至 sys.dm_exec_query_plan 動態管理函數,以取得查詢計畫。This value can be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.

當原生編譯的預存程序查詢記憶體最佳化的資料表時,一律為 0x000。Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
creation_timecreation_time datetimedatetime 計畫的編譯時間。Time at which the plan was compiled.
last_execution_timelast_execution_time datetimedatetime 上次開始執行計畫的時間。Last time at which the plan started executing.
execution_countexecution_count Bigintbigint 計畫從上次編譯以來被執行的次數。Number of times that the plan has been executed since it was last compiled.
total_worker_timetotal_worker_time Bigintbigint 這個計畫從編譯以來執行所耗用的 CPU 時間總量 (以毫秒為單位來報告,但是精確度只到毫秒)。Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan 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, reported in microseconds (but only accurate to milliseconds), that was consumed the last time the plan was executed. 11
min_worker_timemin_worker_time Bigintbigint 這個計畫在單次執行期間曾經耗用的最少 CPU 時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Minimum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution. 11
max_worker_timemax_worker_time Bigintbigint 這個計畫在單次執行期間曾經耗用的最多 CPU 時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Maximum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution. 11
total_physical_readstotal_physical_reads Bigintbigint 這個計畫在編譯以來執行所執行的實體讀取總數。Total number of physical reads performed by executions of this plan since it was compiled.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
last_physical_readslast_physical_reads Bigintbigint 計畫上次執行所執行的實體讀取數。Number of physical reads performed the last time the plan was executed.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
min_physical_readsmin_physical_reads Bigintbigint 這個計畫在單次期間曾執行的最小實體讀取數。Minimum number of physical reads that this plan has ever performed during a single execution.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
max_physical_readsmax_physical_reads Bigintbigint 這個計畫在單次執行期間曾執行的最大實體讀取數。Maximum number of physical reads that this plan has ever performed during a single execution.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
total_logical_writestotal_logical_writes Bigintbigint 這個計畫在編譯以來執行所執行的邏輯寫入總數。Total number of logical writes performed by executions of this plan since it was compiled.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
last_logical_writeslast_logical_writes Bigintbigint 最近一次完成計畫執行期間變動的緩衝集區頁面數。Number of buffer pool pages dirtied during the most recently completed execution of the plan.

讀取頁面之後,頁面只有在第一次修改時才會變成中途。After a page is read, the page becomes dirty only the first time it is modified. 當頁面變得中途時,這個數位就會遞增。When a page becomes dirty, this number is incremented. 後續修改已中途分頁並不會影響此數位。Subsequent modifications of an already dirty page do not affect this number.

查詢記憶體優化資料表時,這個數位一律為0。This number will always be 0 when querying a memory-optimized table.
min_logical_writesmin_logical_writes Bigintbigint 這個計畫在單次執行期間曾執行的最小邏輯寫入數。Minimum number of logical writes that this plan has ever performed during a single execution.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
max_logical_writesmax_logical_writes Bigintbigint 這個計畫在單次執行期間曾執行的最大邏輯寫入數。Maximum number of logical writes that this plan has ever performed during a single execution.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
total_logical_readstotal_logical_reads Bigintbigint 這個計畫在編譯以來執行所執行的邏輯讀取總數。Total number of logical reads performed by executions of this plan since it was compiled.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
last_logical_readslast_logical_reads Bigintbigint 計畫上次執行所執行的邏輯讀取數。Number of logical reads performed the last time the plan was executed.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
min_logical_readsmin_logical_reads Bigintbigint 這個計畫在單次執行期間曾執行的最小邏輯讀取數。Minimum number of logical reads that this plan has ever performed during a single execution.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
max_logical_readsmax_logical_reads Bigintbigint 這個計畫在單次執行期間曾經執行的最大邏輯讀取數。Maximum number of logical reads that this plan has ever performed during a single execution.

查詢記憶體最佳化的資料表時一律為 0。Will always be 0 querying a memory-optimized table.
total_clr_timetotal_clr_time Bigintbigint MicrosoftMicrosoft .NET Framework.NET Framework common language runtime (CLR)物件中的時間(以毫秒為單位來報告,但是精確度只到毫秒),這是在編譯後執行此計畫的方式。Time, reported in microseconds (but only accurate to milliseconds), consumed inside MicrosoftMicrosoft .NET Framework.NET Framework common language runtime (CLR) objects by executions of this plan since it was compiled. CLR 物件可以是預存程序、函數、觸發程序、類型和彙總。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
last_clr_timelast_clr_time Bigintbigint 這個計畫上一次執行期間,在 .NET Framework.NET Framework CLR 物件內部執行所耗用的時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Time, reported in microseconds (but only accurate to milliseconds) consumed by execution inside .NET Framework.NET Framework CLR objects during the last execution of this plan. CLR 物件可以是預存程序、函數、觸發程序、類型和彙總。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
min_clr_timemin_clr_time Bigintbigint 這個計畫在單次執行期間於 .NET Framework.NET Framework CLR 物件內部曾經耗用的最少時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Minimum time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed inside .NET Framework.NET Framework CLR objects during a single execution. CLR 物件可以是預存程序、函數、觸發程序、類型和彙總。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
max_clr_timemax_clr_time Bigintbigint 這個計畫在單次執行期間於 .NET Framework.NET Framework CLR 物件內部曾經耗用的最多時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Maximum time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed inside the .NET Framework.NET Framework CLR during a single execution. CLR 物件可以是預存程序、函數、觸發程序、類型和彙總。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
total_elapsed_timetotal_elapsed_time Bigintbigint 這個計畫完成執行經歷的總時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Total elapsed time, reported in microseconds (but only accurate to milliseconds), for completed executions of this plan.
last_elapsed_timelast_elapsed_time Bigintbigint 這個計畫最近完成所經歷的時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Elapsed time, reported in microseconds (but only accurate to milliseconds), for the most recently completed execution of this plan.
min_elapsed_timemin_elapsed_time Bigintbigint 這個計畫的任何一次完成執行所經歷的最少時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Minimum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.
max_elapsed_timemax_elapsed_time Bigintbigint 這個計畫的任何一次完成執行所經歷的最多時間 (以毫秒為單位來報告,但是精確度只到毫秒)。Maximum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.
query_hashquery_hash Binary (8)Binary(8) 針對查詢所計算的二進位雜湊值,可用來識別含有類似邏輯的查詢。Binary hash value calculated on the query and used to identify queries with similar logic. 您可以使用查詢雜湊判別只有常值不同之查詢的彙總資源使用狀況。You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
query_plan_hashquery_plan_hash binary (8)binary(8) 從查詢執行計畫計算所得的二進位雜湊值將用於識別類似的查詢執行計畫。Binary hash value calculated on the query execution plan and used to identify similar query execution plans. 您可以使用查詢計劃雜湊尋找具有類似執行計畫之查詢的累計成本。You can use query plan hash to find the cumulative cost of queries with similar execution plans.

當原生編譯的預存程序查詢記憶體最佳化的資料表時,一律為 0x000。Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
total_rowstotal_rows Bigintbigint 查詢傳回的資料列總數。Total number of rows returned by the query. 不可為 null。Cannot be null.

當原生編譯的預存程序查詢記憶體最佳化的資料表時,一律為 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
last_rowslast_rows Bigintbigint 上次執行查詢時所傳回的資料列數目。Number of rows returned by the last execution of the query. 不可為 null。Cannot be null.

當原生編譯的預存程序查詢記憶體最佳化的資料表時,一律為 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
min_rowsmin_rows Bigintbigint 在一次執行期間,查詢所傳回的最小資料列數目。Minimum number of rows ever returned by the query during one execution. 不可為 null。Cannot be null.

當原生編譯的預存程序查詢記憶體最佳化的資料表時,一律為 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
max_rowsmax_rows Bigintbigint 在一次執行期間,查詢所傳回的最大資料列數目。Maximum number of rows ever returned by the query during one execution. 不可為 null。Cannot be null.

當原生編譯的預存程序查詢記憶體最佳化的資料表時,一律為 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
statement_sql_handlestatement_sql_handle Varbinary (64)varbinary(64) 適用對象SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

只有在開啟查詢存放區,並收集該特定查詢的統計資料時,才會填入非 Null 值。Populated with non-NULL values only if Query Store is turned on and collecting the stats for that particular query.
statement_context_idstatement_context_id Bigintbigint 適用對象SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

只有在開啟查詢存放區,並收集該特定查詢的統計資料時,才會填入非 Null 值。Populated with non-NULL values only if Query Store is turned on and collecting the stats for that particular query.
total_doptotal_dop Bigintbigint 此計畫自編譯以來所使用的平行處理原則程度總計總和。The total sum of degree of parallelism this plan used since it was compiled. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_doplast_dop Bigintbigint 此計畫上一次執行時的平行處理原則程度。The degree of parallelism when this plan executed last time. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_dopmin_dop Bigintbigint 這個計畫在一次執行期間所使用的平行處理原則最小程度。The minimum degree of parallelism this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_dopmax_dop Bigintbigint 這個計畫在一次執行期間使用的平行處理原則最大程度。The maximum degree of parallelism this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_grant_kbtotal_grant_kb Bigintbigint 此計畫自編譯後所收到的保留記憶體授與總數(以 KB 為單位)。The total amount of reserved memory grant in KB this plan received since it was compiled. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_grant_kblast_grant_kb Bigintbigint 當此計畫上次執行時,保留的記憶體授與的數量(KB)。The amount of reserved memory grant in KB when this plan executed last time. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_grant_kbmin_grant_kb Bigintbigint 在一次執行期間,此計畫收到的保留記憶體授與的最小數量(以 KB 為單位)。The minimum amount of reserved memory grant in KB this plan ever received during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_grant_kbmax_grant_kb Bigintbigint 在一次執行期間,此計畫收到的保留記憶體授與的最大數量(以 KB 為單位)。The maximum amount of reserved memory grant in KB this plan ever received during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_used_grant_kbtotal_used_grant_kb Bigintbigint 此計畫自編譯後所用的保留記憶體的總數量(以 KB 為單位)。The total amount of reserved memory grant in KB this plan used since it was compiled. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_used_grant_kblast_used_grant_kb Bigintbigint 當此計畫上次執行時,已使用的記憶體授與的數量(KB)。The amount of used memory grant in KB when this plan executed last time. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_used_grant_kbmin_used_grant_kb Bigintbigint 這個計畫在一次執行期間所使用的最小記憶體授與量(KB)。The minimum amount of used memory grant in KB this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_used_grant_kbmax_used_grant_kb Bigintbigint 這個計畫在一次執行期間所使用的記憶體授與數量上限(以 KB 為單位)。The maximum amount of used memory grant in KB this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_ideal_grant_kbtotal_ideal_grant_kb Bigintbigint 此計畫在編譯以來估計的理想記憶體授與的總數量(以 KB 為單位)。The total amount of ideal memory grant in KB this plan estimated since it was compiled. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_ideal_grant_kblast_ideal_grant_kb Bigintbigint 當此計畫上次執行時,理想的記憶體授與量(以 KB 為單位)。The amount of ideal memory grant in KB when this plan executed last time. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_ideal_grant_kbmin_ideal_grant_kb Bigintbigint 這項計畫在一次執行期間預估的最小記憶體授與量(以 KB 為單位)。The minimum amount of ideal memory grant in KB this plan ever estimated during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_ideal_grant_kbmax_ideal_grant_kb Bigintbigint 理想記憶體授與的最大數量(以 KB 為單位),在一次執行期間估計此計畫。The maximum amount of ideal memory grant in KB this plan ever estimated during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_reserved_threadstotal_reserved_threads Bigintbigint 此計畫自編譯以來曾使用的保留平行線程總總和。The total sum of reserved parallel threads this plan ever used since it was compiled. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_reserved_threadslast_reserved_threads Bigintbigint 此計畫上次執行時的保留平行線程數目。The number of reserved parallel threads when this plan executed last time. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_reserved_threadsmin_reserved_threads Bigintbigint 這個計畫在一次執行期間所使用的保留平行線程數目下限。The minimum number of reserved parallel threads this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_reserved_threadsmax_reserved_threads Bigintbigint 這個計畫在一次執行期間使用的保留平行線程數目上限。The maximum number of reserved parallel threads this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_used_threadstotal_used_threads Bigintbigint 這個計畫自編譯以來曾使用過的已使用平行線程總總和。The total sum of used parallel threads this plan ever used since it was compiled. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_used_threadslast_used_threads Bigintbigint 此計畫上次執行時所使用的平行線程數目。The number of used parallel threads when this plan executed last time. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_used_threadsmin_used_threads Bigintbigint 這個計畫在一次執行期間使用的最小平行線程數目。The minimum number of used parallel threads this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_used_threadsmax_used_threads Bigintbigint 這個計畫在一次執行期間使用的最大平行線程數目。The maximum number of used parallel threads this plan ever used during one execution. 查詢記憶體優化資料表時,一律為0。It will always be 0 for querying a memory-optimized table.

適用對象SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_columnstore_segment_readstotal_columnstore_segment_reads Bigintbigint 查詢讀取的資料行存放區區段總計總和。The total sum of columnstore segments read by the query. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
last_columnstore_segment_readslast_columnstore_segment_reads Bigintbigint 上次執行查詢時所讀取的資料行存放區區段數目。The number of columnstore segments read by the last execution of the query. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
min_columnstore_segment_readsmin_columnstore_segment_reads Bigintbigint 查詢在一次執行期間所讀取的資料行存放區區段最小數目。The minimum number of columnstore segments ever read by the query during one execution. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
max_columnstore_segment_readsmax_columnstore_segment_reads Bigintbigint 查詢在一次執行期間所讀取的資料行存放區區段數目上限。The maximum number of columnstore segments ever read by the query during one execution. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
total_columnstore_segment_skipstotal_columnstore_segment_skips Bigintbigint 查詢略過的資料行存放區區段總計總和。The total sum of columnstore segments skipped by the query. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
last_columnstore_segment_skipslast_columnstore_segment_skips Bigintbigint 上次執行查詢時略過的資料行存放區區段數目。The number of columnstore segments skipped by the last execution of the query. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
min_columnstore_segment_skipsmin_columnstore_segment_skips Bigintbigint 查詢在一次執行期間曾略過的資料行存放區區段數目下限。The minimum number of columnstore segments ever skipped by the query during one execution. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
max_columnstore_segment_skipsmax_columnstore_segment_skips Bigintbigint 查詢在一次執行期間曾略過的資料行存放區區段數目上限。The maximum number of columnstore segments ever skipped by the query during one execution. 不可為 null。Cannot be null.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
total_spillstotal_spills Bigintbigint 自編譯以來執行此查詢所溢出的總頁數。The total number of pages spilled by execution of this query since it was compiled.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
last_spillslast_spills Bigintbigint 上次執行查詢時所溢出的頁面數目。The number of pages spilled the last time the query was executed.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
min_spillsmin_spills Bigintbigint 在單次執行期間,此查詢已溢出的最小頁面數目。The minimum number of pages that this query has ever spilled during a single execution.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
max_spillsmax_spills Bigintbigint 在單次執行期間,此查詢已溢出的最大頁面數目。The maximum number of pages that this query has ever spilled during a single execution.

適用于:從SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and 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 這個計畫在編譯以來執行所執行的遠端頁面伺服器讀取總數。Total number of remote page server reads performed by executions of this plan since it was compiled.

適用物件: Azure SQL DB 超大規模資料庫Applies to: Azure SQL DB Hyperscale
last_page_server_readslast_page_server_reads Bigintbigint 上次執行計畫時所執行的遠端頁面伺服器讀取數目。Number of remote page server reads performed the last time the plan was executed.

適用物件: Azure SQL DB 超大規模資料庫Applies To: Azure SQL DB Hyperscale
min_page_server_readsmin_page_server_reads Bigintbigint 這個計畫在單次執行期間曾執行的最小遠端頁面伺服器讀取數。Minimum number of remote page server reads that this plan has ever performed during a single execution.

適用物件: Azure SQL DB 超大規模資料庫Applies To: Azure SQL DB Hyperscale
max_page_server_readsmax_page_server_reads Bigintbigint 這個計畫在單次執行期間曾執行的最大遠端頁面伺服器讀取數。Maximum number of remote page server reads that this plan has ever performed during a single execution.

適用物件: Azure SQL DB 超大規模資料庫Applies To: Azure SQL DB 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 one 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 query is completed.

範例Examples

A.A. 尋找 TOP N 查詢Finding the TOP N queries

下列範例會傳回以平均 CPU 時間排名的前五個查詢的相關資訊。The following example returns information about the top five queries ranked by average CPU time. 這個範例會根據查詢雜湊彙總查詢,以便依據累計資源耗用量分組邏輯對等查詢。This example aggregates the queries according to their query hash so that logically equivalent queries are grouped by their cumulative resource consumption.

SELECT TOP 5 query_stats.query_hash AS "Query Hash",   
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",  
    MIN(query_stats.statement_text) AS "Statement Text"  
FROM   
    (SELECT QS.*,   
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1) AS statement_text  
     FROM sys.dm_exec_query_stats AS QS  
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats  
GROUP BY query_stats.query_hash  
ORDER BY 2 DESC;  

B.B. 傳回查詢的資料列計數彙總Returning row count aggregates for a query

下列範例會傳回查詢的資料列計數彙總資訊 (資料列總數、最小資料列數目、最大資料列數目及上次傳回的資料列數目)。The following example returns row count aggregate information (total rows, minimum rows, maximum rows and last rows) for queries.

SELECT qs.execution_count,  
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,   
                 (CASE WHEN qs.statement_end_offset = -1   
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2   
                       ELSE qs.statement_end_offset end -  
                            qs.statement_start_offset  
                 )/2  
             ) AS query_text,   
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,   
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows  
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt   
WHERE qt.text like '%SELECT%'   
ORDER BY qs.execution_count 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_procedure_stats (Transact-sql) sys.dm_exec_procedure_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)