sys.dm_db_index_operational_stats

更新： 2006 年 7 月 17 日

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

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 | DEFAULT
  資料庫的識別碼。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。在不指定資料庫名稱的情況下使用 DB_ID 時，目前資料庫的相容性層級必須是 90。

• object_id | NULL | 0 | DEFAULT
  索引所在之資料表或檢視的物件識別碼。object_id 是 int。

  有效的輸入為資料表和檢視的識別碼、NULL、0 或 DEFAULT。預設值為 0；NULL、0 和 DEFAULT 是這個內容中的對等值。

  請指定 NULL 來傳回指定資料庫中所有資料表和檢視的相關資訊。如果您對 object_id 指定 NULL，則您也必須對 index_id 和 partition_number 指定 NULL。

• index_id | 0 | NULL | -1 | DEFAULT
  索引的識別碼。index_id 是 int。有效的輸入為索引的識別碼、0 (如果 object_id 是堆積)、NULL、-1 或 DEFAULT。預設值為 -1；NULL、-1 和 DEFAULT 是這個內容中的對等值。

  請指定 NULL 來傳回基底資料表或檢視的所有索引相關資訊。如果您對 index_id 指定 NULL，則您也必須對 partition_number 指定 NULL。

• partition_number | NULL | 0 | DEFAULT
  物件的資料分割編號。partition_number 是 int。有效輸入為索引或堆積的 partion_number、NULL、0 或 DEFAULT。預設值為 0；NULL、0 和 DEFAULT 是這個內容中的對等值。

  請指定 NULL 來傳回索引或堆積之所有資料分割的相關資訊。

  partition_number 是以 1 為基底。非資料分割的索引或堆積將 partition_number 設為 1。

傳回的資料表

資料行名稱	資料類型	描述
database_id	smallint	資料庫識別碼。
object_id	int	資料表或檢視的識別碼。
index_id	int	索引或堆積的識別碼。 0 = 堆積
partition_number	int	在索引或堆積內，以 1 為基底的資料分割編號。
leaf_insert_count	bigint	分葉層級插入的累計計數。
leaf_delete_count	bigint	分葉層級刪除的累計計數。
leaf_update_count	bigint	分葉層級更新的累計計數。
leaf_ghost_count	bigint	被標示為已經刪除、但尚未移除的分葉層級資料列累計計數。這些資料列會在設定間隔時，被清除執行緒移除。由於還有一個未完成的快照集隔離交易，因此這個值不包含保留的資料列。如需有關快照集隔離交易的詳細資訊，請參閱＜Database Engine 中資料列版本控制式的隔離等級＞。
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	分葉層級的頁面合併累計計數。
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 類型資料行中的資料。如需詳細資訊，請參閱＜超過 8 KB 的資料列溢位資料＞。如需有關配置單位的詳細資訊，請參閱＜資料表與索引組織＞。
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 閂鎖等候的累加毫秒數。

備註

這個動態管理物件不接受來自 CROSS APPLY 和 OUTER 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 and row_overflow_fetch_in_bytes 資料行中的值，也可能會大於 0。如需詳細資訊，請參閱＜超過 8 KB 的資料列溢位資料＞。

如何重設計數器

只要在代表堆積或索引的中繼資料快取物件可以使用時，sys.dm_db_index_operational_stats 所傳回的資料才存在。這項資料既不能保存，以交易來說也是不一致的。這表示您不能使用這些計數器來判定索引是否已經使用，或者索引上次是何時使用。如需詳細資訊，請參閱＜sys.dm_db_index_usage_stats＞。

每當堆積或索引的中繼資料被引進中繼資料快取時，每個資料行的值都會設為零，而且統計資料也會累計，直到快取物件從中繼資料快取移除為止。因此，使用中堆積或索引的中繼資料應該會一直存放在快取中，而且累計值也會反映自從 SQL Server 執行個體上次啟動以來的活動。比較不使用的堆積或索引中繼資料，則會在使用時移入和移出快取。因此它不見得會有可用的值。卸除索引會使對應的統計資料從記憶體移除，不會再被該函數報告。對索引進行的其他 DDL 作業，可能會使統計資料值重設為零。

使用系統函數指定參數值

您可以使用 Transact-SQL 函數 DB_ID 和 OBJECT_ID 來指定 database_id 和 object_id 參數的值。不過，傳遞對這些函數無效的值可能會造成意料之外的結果。使用 DB_ID 或 OBJECT_ID 時，請務必確定傳回的是有效的識別碼。如需詳細資訊，請參閱＜sys.dm_db_index_physical_stats＞的「備註」一節。

權限

需要下列權限：

• 對資料庫中的指定物件具備 CONTROL 權限
• VIEW DATABASE STATE 權限，利用物件萬用字元 @object_id =NULL，傳回指定資料庫中所有物件的相關資訊
• VIEW SERVER STATE 權限，利用資料庫萬用字元 @database_id = NULL，傳回所有資料庫的相關資訊

授與 VIEW DATABASE STATE 可以傳回資料庫中的所有物件，不論特定物件是否拒絕任何 CONTROL 權限。

拒絕 VIEW DATABASE STATE 會造成不允許傳回資料庫中的所有物件，不論是否授與特定物件任何 CONTROL 權限。此外，當指定資料庫萬用字元 @database_id=NULL 時，將會省略資料庫。

如需詳細資訊，請參閱＜動態管理檢視和函數＞。

範例

A. 傳回指定資料表的資訊

下列範例會傳回 AdventureWorks 資料庫中 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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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 

請參閱

參考

動態管理檢視和函數
索引相關動態管理檢視和函數
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_os_latch_stats
sys.dm_db_partition_stats
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)

其他資源

監視和微調效能
資料表與索引架構

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本	歷程記錄
2006 年 7 月 17 日	新增內容： 釐清在第一個參數中使用 DB_ID() 的資料庫相容性層級需求。
2006 年 4 月 14 日	變更的內容： 在 database_id、object_id, 和 partition_number 的語法中加入輸入值 0 和 DEFAULT。 在 index_id 的語法中加入輸入值 -1 和 DEFAULT。
2005 年 12 月 5 日	新增內容： 新增「使用系統函數指定參數值」一節。 變更的內容： 修改「權限」一節。