sys.dm_db_index_operational_stats (Transact-SQL)

文章
07/05/2024

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

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

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

注意

sys.dm_db_index_operational_stats不會傳回記憶體優化索引的相關信息。如需記憶體優化索引使用的相關信息，請參閱 sys.dm_db_xtp_index_stats （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 資料庫中，這些值在單一資料庫或彈性集區內是唯一的，但在邏輯伺服器內則不是唯一的。
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、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	資料庫引擎在數據列鎖定上等候的累計次數。
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。

注意

SQL Server 文件通常會使用「B 型樹狀結構」一詞來指稱索引。在資料列存放區索引中，SQL Server 會實作 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_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許可權。

重要

當您使用 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)

分享方式：