sys.dm_db_index_operational_stats (Transact-SQL)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

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

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

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_id 、 index_id 和 partition_number 指定 Null。

可以指定內建函數 DB_ID。

object_id |Null |0 |預設

索引開啟之資料表或檢視表的物件識別碼。 object_id為 int 。

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

指定 Null 以傳回指定資料庫中所有資料表和檢視表的快取資訊。 如果您為 object_id 指定 Null，也必須為 index_id 和 partition_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 Database 中，這些值在單一資料庫或彈性集區內是唯一的，但不在邏輯伺服器內。
object_id	int	資料表或檢視表的識別碼。
index_id	int	索引或堆積的識別碼。 0 = 堆積
partition_number	int	索引或堆積內的 1 個分割區編號。
hobt_id	bigint	適用于： SQL Server 2016 （13.x） 和更新版本 Azure SQL Database。 追蹤資料行存放區索引內部資料的資料堆積或 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 、Ntext 、 image 、 Varchar（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	Database Engine 在資料列鎖定上等候的累計次數。
row_lock_wait_in_ms	bigint	Database Engine 在資料列鎖定上等候的總毫秒數。
page_lock_count	bigint	所要求的頁面鎖定累計數目。
page_lock_wait_count	bigint	Database Engine 在頁面鎖定上等候的累計次數。
page_lock_wait_in_ms	bigint	Database Engine 在頁面鎖定上等候的總毫秒數。
index_lock_promotion_attempt_count	bigint	Database Engine 嘗試呈報鎖定的累計次數。
index_lock_promotion_count	bigint	Database Engine 升級鎖定的累計次數。
page_latch_wait_count	bigint	由於閂鎖爭用，Database Engine 等候的累計次數。
page_latch_wait_in_ms	bigint	由於閂鎖爭用，Database Engine 等候的累計毫秒數。
page_io_latch_wait_count	bigint	Database Engine 在 I/O 頁面閂鎖上等候的累計次數。
page_io_latch_wait_in_ms	bigint	Database Engine 在頁面 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。

備註

這個動態管理物件不接受 和 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

  這些資料行指出 Database Engine 嘗試取得資料列和頁面鎖定的次數。

• 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_pages 和 row_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_ID 和 OBJECT_ID 來指定database_id 和 object_id 參數的值 。 不過，傳遞這些函式不正確值可能會導致非預期的結果。 當您使用 DB_ID 或 OBJECT_ID 時，請務必傳回有效的識別碼。 如需詳細資訊，請參閱 一節。

權限

需要下列許可權：

• CONTROL 資料庫內指定物件的許可權

• VIEW DATABASE STATE 或 VIEW 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 許可權。

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)