共用方式為


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_idsmallint。有效的輸入為資料庫的識別碼、NULL、0 或 DEFAULT。預設值為 0;NULL、0 和 DEFAULT 是這個內容中的對等值。

    請指定 NULL 來傳回 SQL Server 執行個體中之所有資料庫的資訊。如果您對 database_id 指定 NULL,則您也必須對 object_idindex_idpartition_number 指定 NULL。

    可以指定內建函數 DB_ID。在不指定資料庫名稱的情況下使用 DB_ID 時,目前資料庫的相容性層級必須是 90。

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

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

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

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

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

  • partition_number | NULL | 0 | DEFAULT
    物件的資料分割編號。partition_numberint。有效輸入為索引或堆積的 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) 頁面累計計數。這些頁面包含儲存在 textntextimagevarchar(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_countpage_latch_wait_in_ms
    這些資料行會指出索引或堆積上是否有閂鎖爭用的情形,以及爭用的嚴重程度。
  • row_lock_countpage_lock_count
    這些資料行會指出 Database Engine 試圖取得資料列和頁面鎖定的次數。
  • row_lock_wait_in_mspage_lock_wait_in_ms
    這些資料行會指出索引或堆積上是否有鎖定爭用的情形,以及爭用的嚴重程度。

分析索引或資料分割上的實體 I/O 統計資料

  • page_io_latch_wait_countpage_io_latch_wait_in_ms
    這些資料行會指出是否有發出實體 I/O,將索引或堆積頁引進記憶體中,以及發出幾個 I/O。

資料行備註

lob_orphan_create_countlob_orphan_insert_count 中的值一律相同。

如果是含有一或多個 LOB 資料行作為內含資料行的非叢集索引,lob_fetch_in_pageslob_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_IDOBJECT_ID 來指定 database_idobject_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 權限。

ms174281.note(zh-tw,SQL.90).gif重要事項:
當您使用 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_idobject_id,partition_number 的語法中加入輸入值 0 和 DEFAULT。
  • index_id 的語法中加入輸入值 -1 和 DEFAULT。

2005 年 12 月 5 日

新增內容:
  • 新增「使用系統函數指定參數值」一節。
變更的內容:
  • 修改「權限」一節。