分享方式:


sys.dm_db_index_operational_stats (Transact-SQL)

適用於:SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

針對資料庫中數據表或索引的每個分割區,傳回目前的較低層級 I/O、鎖定、閂鎖和存取方法活動。

記憶體優化索引不會出現在此 DMV 中。

注意

sys.dm_db_index_operational_stats不會傳回記憶體優化索引的相關信息。 如需記憶體優化索引使用的相關信息,請參閱 sys.dm_db_xtp_index_stats (Transact-SQL)

Transact-SQL 語法慣例

語法

sys.dm_db_index_operational_stats (    
    { database_id | NULL | 0 | DEFAULT }    
  , { object_id | NULL | 0 | DEFAULT }    
  , { index_id | 0 | NULL | -1 | DEFAULT }    
  , { partition_number | NULL | 0 | DEFAULT }    
)    

引數

database_id |NULL |0 |違約

資料庫的標識碼。 database_id為 smallint。 有效的輸入是資料庫、NULL、0 或 DEFAULT 的標識碼。 預設值是 0。 NULL、0 和 DEFAULT 在此內容中是相等的值。

指定 NULL 以傳回 SQL Server 實例中所有資料庫的資訊。 如果您為 database_id 指定 NULL,也必須針對 object_idindex_idpartition_number指定 NULL。

可以指定內建函數 DB_ID

object_id |NULL |0 |違約

索引開啟之數據表或檢視表的物件標識碼。 object_id為 int

有效的輸入是數據表和檢視表、NULL、0 或 DEFAULT 的識別碼。 預設值是 0。 NULL、0 和 DEFAULT 在此內容中是相等的值。

指定 NULL 以傳回指定資料庫中所有數據表和檢視表的快取資訊。 如果您為 object_id 指定 NULL,也必須為 index_idpartition_number指定 NULL。

index_id | 0 |NULL |-1 |違約

索引的識別碼。 index_id為 int。有效的輸入是索引的標識碼,如果object_id是堆積、NULL、-1 或 DEFAULT,則為 0。 默認值為 -1、NULL、-1 和 DEFAULT 是此內容中的對等值。

指定 NULL 以傳回基表或檢視表之所有索引的快取資訊。 如果您為 index_id 指定 NULL,也必須為 partition_number指定 NULL。

partition_number |NULL |0 |違約

對象中的數據分割編號。 partition_number為 int。有效的輸入是索引或堆積、NULL、0 或 DEFAULT 的partion_number。 預設值是 0。 NULL、0 和 DEFAULT 在此內容中是相等的值。

指定 NULL 以傳回索引或堆積之所有分割區的快取資訊。

partition_number是以 1 為基礎。 非分割索引或堆積partition_number設定為 1。

傳回的資料表

資料行名稱 資料類型 描述
database_id smallint 資料庫識別碼。

在 Azure SQL 資料庫中,這些值在單一資料庫或彈性集區內是唯一的,但在邏輯伺服器內則不是唯一的。
object_id int 數據表或檢視表的標識碼。
index_id int 索引或堆積的標識碼。

0 = 堆積
partition_number int 索引或堆積內的1個分割區編號。
hobt_id bigint 適用於: SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫。

追蹤數據行存放區索引內部數據的數據堆積或 B 型樹狀結構數據列集識別碼。

NULL - 這不是內部資料行存放區數據列集。

如需詳細資訊,請參閱 sys.internal_partitions (Transact-SQL)
leaf_insert_count bigint 分葉層級插入的累計計數。
leaf_delete_count bigint 分葉層級刪除的累計計數。 leaf_delete_count只會針對未先標示為準刪除的記錄遞增。 對於先刪除的記錄, leaf_ghost_count 會改為遞增。
leaf_update_count bigint 分葉層級更新的累計計數。
leaf_ghost_count bigint 標記為已刪除但尚未移除的分葉層級數據列累計計數。 此計數不包含立即刪除且未標示為準刪除的記錄。 清除線程會在設定間隔移除這些數據列。 由於未處理的快照集隔離交易,這個值不包含保留的數據列。
nonleaf_insert_count bigint 在分葉層級上方的插入累計計數。

0 = 堆積或數據行存放區
nonleaf_delete_count bigint 分葉層級上方的刪除累計計數。

0 = 堆積或數據行存放區
nonleaf_update_count bigint 分葉層級以上更新的累計計數。

0 = 堆積或數據行存放區
leaf_allocation_count bigint 索引或堆積中分葉層級頁面配置的累計計數。

針對索引,頁面配置會對應至頁面分割。
nonleaf_allocation_count bigint 分葉層級上方頁面分割所造成的頁面配置累計計數。

0 = 堆積或數據行存放區
leaf_page_merge_count bigint 分葉層級的頁面合併累計計數。 數據行存放區索引一律為 0。
nonleaf_page_merge_count bigint 分葉層級上方的頁面合併累計計數。

0 = 堆積或數據行存放區
range_scan_count bigint 從索引或堆積開始的範圍和數據表掃描累計計數。
singleton_lookup_count bigint 從索引或堆積擷取單一數據列的累計計數。
forwarded_fetch_count bigint 透過轉送記錄擷取的數據列計數。

0 = 索引
lob_fetch_in_pages bigint 從LOB_DATA配置單位擷取的大型物件 (LOB) 頁面累計計數。 這些頁面包含儲存在 text、ntextimagevarchar(max)、nvarchar(max)varbinary(max)xml 類型的數據行中。 如需詳細資訊,請參閱 資料類型 (Transact-SQL)
lob_fetch_in_bytes bigint 擷取的LOB數據位元組累計計數。
lob_orphan_create_count bigint 針對大量作業建立的孤立LOB值的累計計數。

0 = 非叢集索引
lob_orphan_insert_count bigint 大量作業期間插入的孤立LOB值的累計計數。

0 = 非叢集索引
row_overflow_fetch_in_pages bigint 從ROW_OVERFLOW_DATA配置單位擷取的數據列溢位數據頁累計計數。

這些頁面包含儲存在 varchar(n)、nvarchar(n)varbinary(n)下推sql_variant數據行中的數據。
row_overflow_fetch_in_bytes bigint 擷取的數據列溢位數據位元組累計計數。
column_value_push_off_row_count bigint LOB 數據和數據列溢位數據的累計數據行值計數,該數據列會推送至非數據列,讓插入或更新的數據列符合頁面。
column_value_pull_in_row_count bigint 提取數據列內之 LOB 數據和數據列溢位數據的累計數據行值計數。 當更新作業釋放記錄中的空間,並提供從LOB_DATA或ROW_OVERFLOW_DATA配置單位提取到IN_ROW_DATA配置單位的一或多個離數據列值的機會時,就會發生這種情況。
row_lock_count bigint 所要求的數據列鎖定累計數目。
row_lock_wait_count bigint 資料庫引擎 在數據列鎖定上等候的累計次數。
row_lock_wait_in_ms bigint 資料庫引擎 在數據列鎖定上等候的總毫秒數。
page_lock_count bigint 所要求的頁面鎖定累計數目。
page_lock_wait_count bigint 資料庫引擎 在頁面鎖定上等候的累計次數。
page_lock_wait_in_ms bigint 資料庫引擎 在頁面鎖定上等候的總毫秒數。
index_lock_promotion_attempt_count bigint 資料庫引擎 嘗試呈報鎖定的累計次數。
index_lock_promotion_count bigint 資料庫引擎 擴大鎖定的累計次數。
page_latch_wait_count bigint 由於閂鎖爭用,資料庫引擎 等候的累計次數。
page_latch_wait_in_ms bigint 由於閂鎖爭用,資料庫引擎 等候的累計毫秒數。
page_io_latch_wait_count bigint 資料庫引擎 在 I/O 頁面閂鎖上等候的累計次數。
page_io_latch_wait_in_ms bigint 資料庫引擎 在頁面 I/O 閂鎖上等候的累計毫秒數。
tree_page_latch_wait_count bigint 只包含上層 B 型樹狀目錄頁面的page_latch_wait_count子集。 堆積或數據行存放區索引的一律為 0。
tree_page_latch_wait_in_ms bigint 只包含上層 B 型樹狀頁面的 page_latch_wait_in_ms 子集。 堆積或數據行存放區索引的一律為 0。
tree_page_io_latch_wait_count bigint 只包含上層 B 型樹狀頁面的 page_io_latch_wait_count 子集。 堆積或數據行存放區索引的一律為 0。
tree_page_io_latch_wait_in_ms bigint 只包含上層 B 型樹狀頁面page_io_latch_wait_in_ms子集。 堆積或數據行存放區索引的一律為 0。
page_compression_attempt_count bigint 針對數據表、索引或索引檢視表的特定分割區,評估頁面層級壓縮的頁面數目。 包含未壓縮的頁面,因為無法大幅節省成本。 數據行存放區索引一律為 0。
page_compression_success_count bigint 針對數據表、索引或索引檢視的特定分割區,使用PAGE壓縮所壓縮的數據頁數目。 數據行存放區索引一律為 0。

注意

檔使用一般參考索引的 B 型樹狀結構一詞。 在數據列存放區索引中,資料庫引擎 會實作 B+ 樹狀結構。 這不適用於記憶體優化數據表上的數據行存放區索引或索引。 如需詳細資訊,請參閱 SQL Server 和 Azure SQL 索引架構和設計指南

備註

這個動態管理物件不接受和OUTER APPLY的相互關聯參數CROSS APPLY

您可以使用 sys.dm_db_index_operational_stats 來追蹤用戶必須等候讀取或寫入數據表、索引或分割區的時間長度,並識別遇到重大 I/O 活動或熱點的數據表或索引。

使用下列數據行來識別爭用的區域。

若要分析資料表或索引分割區的一般存取模式,請使用這些資料行:

  • leaf_insert_count

  • leaf_delete_count

  • leaf_update_count

  • leaf_ghost_count

  • range_scan_count

  • singleton_lookup_count

若要識別閂鎖和鎖定爭用,請使用下列數據行:

  • page_latch_wait_count和page_latch_wait_in_ms

    這些數據行指出索引或堆積上是否有閂鎖競爭,以及爭用的意義。

  • row_lock_count和page_lock_count

    這些數據行指出 資料庫引擎 嘗試取得數據列和頁面鎖定的次數。

  • row_lock_wait_in_ms和page_lock_wait_in_ms

    這些數據行指出索引或堆積是否有鎖定爭用,以及爭用的意義。

分析索引或堆積分割區上實體 I/O 的統計數據

  • page_io_latch_wait_count和page_io_latch_wait_in_ms

    這些數據行指出是否已發出實體 I/O,以將索引或堆積頁面帶入記憶體,以及發出多少 I/O。

數據行備註

lob_orphan_create_count和lob_orphan_insert_count中的值應該一律相等。

對於包含一或多個 LOB 數據行的非叢集索引,lob_fetch_in_pages和lob_fetch_in_bytes中的數據行值可以大於零。 如需詳細資訊,請參閱 建立內含資料行的索引。 同樣地,如果索引包含可以下推數據列的數據行,則數據行 中的值row_overflow_fetch_in_pagesrow_overflow_fetch_in_bytes 可以大於0。

元數據快取中的計數器重設方式

只有代表堆積或索引的元數據快取物件可用,sys.dm_db_index_operational_stats回的數據才會存在。 此數據既不是持續性,也不是交易一致。 這表示您無法使用這些計數器來判斷索引是否已使用,或上次使用索引的時間。 如需相關信息,請參閱 sys.dm_db_index_usage_stats (Transact-SQL)

每當將堆積或索引的元數據帶入元數據快取,而且統計數據會累積到快取物件從元數據快取中移除之前,每個數據行的值都會設定為零。 因此,作用中的堆積或索引可能一律在快取中具有其元數據,而且累積計數可能會反映自上次啟動 SQL Server 實例以來的活動。 較不作用中堆積或索引的元數據會在使用時移入和移出快取。 因此,它可能或可能沒有可用的值。 卸除索引會導致從記憶體中移除對應的統計數據,且函式不再報告。 針對索引的其他 DDL 作業可能會導致統計數據的值重設為零。

使用系統函式來指定參數值

您可以使用 Transact-SQL 函式DB_IDOBJECT_ID來指定database_idobject_id參數的值。 不過,傳遞這些函式無效的值可能會導致非預期的結果。 當您使用 DB_ID 或 OBJECT_ID 時,請務必傳回有效的標識碼。 如需詳細資訊,請參閱一節。

權限

需要下列權限:

  • CONTROL 資料庫內指定對象的許可權

  • VIEW DATABASE STATEVIEW DATABASE PERFORMANCE STATE (SQL Server 2022) 許可權,使用物件通配符 @object_id = NULL 傳回指定資料庫內所有對象的相關信息

  • VIEW SERVER STATEVIEW SERVER PERFORMANCE STATE (SQL Server 2022) 許可權,使用資料庫通配符 @database_id = NULL 傳回所有資料庫的相關信息

VIEW DATABASE STATE 與允許傳回資料庫中的所有物件,不論特定物件上有任何 CONTROL 許可權遭到拒絕。

拒絕 VIEW DATABASE STATE 不允許傳回資料庫中的所有物件,而不論授與特定物件的任何 CONTROL 許可權為何。 此外,指定資料庫通配符 @database_id=NULL 時,會省略資料庫。

如需詳細資訊,請參閱動態管理檢視和函式(Transact-SQL)。

範例

A. 傳回指定數據表的資訊

下列範例會傳回 AdventureWorks2022 資料庫中數據表之所有索引和數據分割 Person.Address 的資訊。 執行此查詢至少需要數據表的 Person.Address CONTROL許可權。

重要

當您使用 Transact-SQL 函式DB_ID和OBJECT_ID傳回參數值時,請務必確保傳回有效的標識符。 如果找不到資料庫或物件名稱 (例如,因為不存在或是拼錯了),這兩個函數都會傳回 NULL。 sys.dm_db_index_operational_stats 函數會將 NULL 解譯為指定所有資料庫或物件的萬用字元值。 由於這不見得是刻意安排的作業,因此本節所舉的範例,只會示範決定資料庫和物件識別碼的安全方法。

DECLARE @db_id int;    
DECLARE @object_id int;    
SET @db_id = DB_ID(N'AdventureWorks2022');    
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');    
IF @db_id IS NULL     
  BEGIN;    
    PRINT N'Invalid database';    
  END;    
ELSE IF @object_id IS NULL    
  BEGIN;    
    PRINT N'Invalid object';    
  END;    
ELSE    
  BEGIN;    
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);    
  END;    
GO    

B. 傳回所有數據表和索引的資訊

下列範例會傳回 SQL Server 實例內所有數據表和索引的資訊。 執行此查詢需要 VIEW SERVER STATE 許可權。

SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);    
GO        

另請參閱

動態管理檢視和函數 (Transact-SQL)
索引相關的動態管理檢視和函式 (Transact-SQL)
效能的監視與微調
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)