sys.dm_db_index_operational_stats (Transact-SQL)
適用於:SQL ServerAzure SQL DatabaseAzure 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 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。 |
注意
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
這些資料行指出 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 STATE
VIEW 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)
意見反映
https://aka.ms/ContentUserFeedback。
即將推出:我們會在 2024 年淘汰 GitHub 問題,並以全新的意見反應系統取代並作為內容意見反應的渠道。 如需更多資訊,請參閱:提交及檢視以下的意見反映: