匯出 (0) 列印
全部展開
Expand Minimize
本主題尚未接受評分 - 為這個主題評分

sys.dm_db_index_physical_stats

更新日期: 2011年4月

Important重要事項
本主題並未進行維護。如需最新版本,請參閱 sys.dm_db_index_physical_stats

傳回指定資料表或檢視之資料和索引的大小和片段資訊。如果是索引,則針對每個資料分割中每個層級的 B 型樹狀目錄,各傳回一個資料列。如果是堆積,則針對每個資料分割中的 IN_ROW_DATA 配置單位,各傳回一個資料列。如果是大型物件 (LOB),則針對每個資料分割中的 LOB_DATA 配置單位,各傳回一個資料列。如果資料表中有資料列溢位資料,則針對每個資料分割中的 ROW_OVERFLOW_DATA 配置單位,各傳回一個資料列。不會傳回有關 xVelocity 記憶體最佳化資料行存放區索引的資訊。

Topic_Link Transact-SQL 語法慣例

sys.dm_db_index_physical_stats( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | 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 或 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。

mode | NULL | DEFAULT
這是模式的名稱。mode 可指定取得統計資料所用的掃描層級。modesysname。有效輸入為 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。預設值 (NULL) 是 LIMITED。

 

資料行名稱 資料類型 說明

database_id

smallint

資料表或檢視表的資料庫識別碼。

object_id

int

索引所在之資料表或檢視表的物件識別碼。

index_id

int

索引的索引識別碼。

0 = 堆積。

partition_number

int

在主控物件內,以 1 為基底的資料分割編號;資料表、檢視表或索引。

1 = 非資料分割的索引或堆積。

index_type_desc

nvarchar(60)

索引類型的描述:

HEAP

CLUSTERED INDEX

NONCLUSTERED INDEX

PRIMARY XML INDEX

SPATIAL INDEX

XML INDEX

alloc_unit_type_desc

nvarchar(60)

配置單位類型的描述:

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA

LOB_DATA 配置單位包含儲存在 textntextimagevarchar(max)nvarchar(max)varbinary(max)xml 等類型之資料行中的資料。如需詳細資訊,請參閱<Data Types (Transact-SQL)>。

ROW_OVERFLOW_DATA 配置單位包含儲存在 varchar(n)nvarchar(n)varbinary(n)sql_variant 等類型之資料行 (已被發送出資料列) 中的資料。

index_depth

tinyint

索引層級的數目。

1 = 堆積,或 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位。

index_level

tinyint

索引的目前層級。

如果是索引分葉層級、堆積和 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為 0。

如果是非分葉索引層級,則大於 0。在索引的根層級中,index_level 是最高的層級。

索引的非分葉層級只有在 mode = DETAILED 才會處理。

avg_fragmentation_in_percent

float

IN_ROW_DATA 配置單位中,索引的邏輯片段或是堆積的範圍片段。

其值以百分比表示,而且會考量多個檔案。如需邏輯和範圍片段的定義,請參閱<備註>一節。

如果是 LOB_DATA 和 ROW_OVERFLOW_DATA 配置單位,則為 0。

如果是 mode = SAMPLED 的堆積,則為 NULL。

fragment_count

bigint

IN_ROW_DATA 配置單位分葉層級中的片段數目。如需有關片段的詳細資訊,請參閱<備註>一節。

如果是索引的非分葉層級,以及 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為 NULL。

如果是 mode = SAMPLED 的堆積,則為 NULL。

avg_fragment_size_in_pages

float

IN_ROW_DATA 配置單位的分葉層級中,一個片段的平均頁數。

如果是索引的非分葉層級,以及 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為 NULL。

如果是 mode = SAMPLED 的堆積,則為 NULL。

page_count

bigint

索引或資料頁總數。

如果是索引,則為 IN_ROW_DATA 配置單位中,B 型樹狀目錄目前層級的總索引頁數。

如果是堆積,則為 IN_ROW_DATA 配置單位中的總資料頁數。

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為配置單位中的總頁數。

avg_page_space_used_in_percent

float

所有頁面所用之資料儲存空間的平均百分比。

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的平均數。

如果是堆積,則為 IN_ROW_DATA 配置單位中所有資料頁的平均數。

如果是 LOB_DATA 或 ROW_OVERFLOW DATA 配置單位,則為配置單位中所有頁面的平均數。

mode = LIMITED 時,則為 NULL。

record_count

bigint

總記錄數。

如果是索引,則為 IN_ROW_DATA 配置單位中,B 型樹狀目錄目前層級的總記錄數。

如果是堆積,則為 IN_ROW_DATA 配置單位中的總記錄數。

note附註
若是堆積,從此函數傳回的記錄數目可能與針對該堆積執行 SELECT COUNT(*) 時所傳回的資料列數目不符。這是因為一個資料列可能包含數筆記錄。例如,在某些更新情況下,單一的堆積資料列可能有一筆轉送記錄以及一筆當做更新作業結果的轉送記錄。同時,在 LOB_DATA 儲存體中,會將多數大型的 LOB 資料列分割為多筆記錄。

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的總記錄數。

mode = LIMITED 時,則為 NULL。

ghost_record_count

bigint

配置單位中,準刪除清除工作準備要移除的準刪除記錄數。

如果是 IN_ROW_DATA 配置單位中索引的非分葉層級,則為 0。

mode = LIMITED 時,則為 NULL。

version_ghost_record_count

bigint

配置單位中未完成之快照集隔離交易所保留的準刪除記錄數。

如果是 IN_ROW_DATA 配置單位中索引的非分葉層級,則為 0。

mode = LIMITED 時,則為 NULL。

min_record_size_in_bytes

int

記錄大小下限 (以位元組為單位)。

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的記錄大小下限。

如果是堆積,則為 IN_ROW_DATA 配置單位中的記錄大小下限。

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的記錄大小下限。

mode = LIMITED 時,則為 NULL。

max_record_size_in_bytes

int

記錄大小上限 (以位元組為單位)。

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的記錄大小上限。

如果是堆積,則為 IN_ROW_DATA 配置單位中的記錄大小上限。

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的記錄大小上限。

mode = LIMITED 時,則為 NULL。

avg_record_size_in_bytes

float

記錄大小平均值 (以位元組為單位)。

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的平均記錄大小。

如果是堆積,則為 IN_ROW_DATA 配置單位中的平均記錄大小。

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的平均記錄大小。

mode = LIMITED 時,則為 NULL。

forwarded_record_count

bigint

在堆積中,有指向另一個資料位置之轉送指標的記錄數目 (此狀態發生於更新期間,原始位置的空間不足以儲存新資料列時)。

如果是 IN_ROW_DATA 配置單位以外的任何堆積配置單位,則為 NULL。

如果是 mode = LIMITED 的堆積,則為 NULL。

compressed_page_count

bigint

壓縮的頁面數。

  • 如果是堆積,新配置的頁面不會使用 PAGE 壓縮方式。堆積會在兩個特殊情況下使用 PAGE 壓縮方式:大量匯入資料或是重建堆積時。造成頁面配置的一般 DML 作業將不會使用 PAGE 壓縮方式。當 compressed_page_count 值成長到大於您要的臨界值時,重建堆積。

  • 如果是具有叢集索引的資料表,compressed_page_count 值會指示 PAGE 壓縮的效能。

sys.dm_db_index_physical_stats 動態管理函數會取代 DBCC SHOWCONTIG 陳述式。這個動態管理函數不接受來自 CROSS APPLY 和 OUTER APPLY 中相互關聯的參數。

掃描模式

執行該函數的模式決定了取得該函數所用之統計資料的掃描層級。mode 指定為 LIMITED、SAMPLED 或 DETAILED。此函數會周遊構成資料表或索引之指定資料分割的配置單位頁面鏈結。sys.dm_db_index_physical_stats 無論在何種模式下執行,只需要一個意圖共用 (IS) 資料表鎖定即可。

LIMITED 模式是最快的模式,而且可以掃描最少的頁數。若是索引,僅會掃描 B 型樹狀目錄的父層級頁 (亦即分葉層級上面的頁面)。若是堆積,則會檢查相關聯的 PFS 和 IAM 頁面,並以 LIMITED 模式掃描堆積的資料頁面。

在 LIMITED 模式下,compressed_page_count 是 NULL,因為 只會掃描 B 型樹狀目錄的非分葉頁面以及堆積的 IAM 和 PFS 頁面。使用 SAMPLED 模式可取得 compressed_page_count 的預估值,而使用 DETAILED 模式可取得 compressed_page_count 的實際值。SAMPLED 模式會傳回統計資料,該統計資料是根據索引或堆積中所有頁面之百分之 1 的取樣。SAMPLED 模式的結果應該被視為近似。如果索引或堆積的頁面少於 10,000 頁,則改以 DETAILED 模式取代 SAMPLED。

DETAILED 模式會掃描所有的頁面,並且傳回所有的統計資料。

從 LIMITED 到 DETAILED 的模式會愈來愈慢,因為每個模式的工作愈來愈多。若要快速測量資料表或索引的大小或片段層級,請使用 LIMITED 模式。它是最快速的模式,而且不會針對索引的 IN_ROW_DATA 配置單位中每個非分葉層級,各傳回一個資料列。

偵測片段

片段是經由針對資料表建立的資料修改程序 (INSERT、UPDATE 和 DELETE 陳述式) 而產生,因此會產生到資料表上定義的索引。由於這些修改通常不會平均散發在資料表和索引的各個資料列上,因此,各頁面的飽和度可能會隨著時間而不同。如果查詢要掃描部分或全部的資料表索引,這類資料表片段可能會造成額外的頁面讀取。這會防礙資料的平行掃描。

索引或堆積的片段層級會顯示在 avg_fragmentation_in_percent 資料行中。如果是堆積,這個值代表該堆積的範圍片段。如果是索引,則這個值代表該索引的邏輯片段。與 DBCC SHOWCONTIG 不同的是,這兩種情況的片段計算演算法,都採用跨越多個檔案的儲存體,因此也較精確。

邏輯片段

這是索引分葉頁中,失序頁面的百分比。失序頁面是指配置給索引之下一個實體頁面的頁面,並不是目前分葉頁中下一頁指標所指向的頁面。

範圍片段

這是堆積分葉頁中,失序範圍的百分比。失序範圍是堆積目前頁面所在之範圍,實際上不是上一頁所在範圍之下一範圍的範圍。

avg_fragmentation_in_percent 的值愈接近零,其效能愈好。不過,百分之 0 到 10 之間的值都在接受範圍內。所有縮減片段的方法 (例如,重建、重新組織或重新建立) 都可以用來縮減這些值。如需有關如何分析索引之片段程度的詳細資訊,請參閱<Reorganizing and Rebuilding Indexes>。

縮減索引中的片段

如果對索引進行片段作業之後,片段會影響到查詢效能,有三個選擇可減少片段:

  • 卸除和重新建立叢集索引。

    重新建立叢集索引會轉散發資料,造成飽和的資料頁面。您可以在 CREATE INDEX 中使用 FILLFACTOR 選項來設定飽和度的層級。這個方法的缺點是在卸除和重建周期內索引是離線的,而且作業不可部分完成。如果中斷了索引建立,就不會重建索引。如需詳細資訊,請參閱<CREATE INDEX (Transact-SQL)>。

  • 以 ALTER INDEX REORGANIZE 代替 DBCC INDEXDEFRAG,依照邏輯順序來重新排列索引的分葉層級頁面。由於這是一項線上作業,因此當執行陳述式時,可以使用索引。這項作業即使被中斷,也不會遺失已經完成的工作。這個方法的缺點是,其資料重新組織作業不如索引重建作業來得好,而且也不能更新統計資料。

  • 以 ALTER INDEX REBUILD 代替 DBCC DBREINDEX,以線上或離線方式重建索引。如需詳細資訊,請參閱<ALTER INDEX (Transact-SQL)>。

單獨片段的這個理由,不足以重新組織或重建索引。片段的主要影響是降低掃描索引時的頁面讀取前輸送量。這樣會使得回應速度更慢。如果片段化的資料表或索引上的查詢工作負載不含掃描在內 (因為工作負載主要是單一查閱),移除片段不會有任何影響。如需詳細資訊,請造訪 Microsoft 網站

note附註
如果在壓縮作業時,部分或完全移動索引,則執行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 可能會導入片段。因此,即使一定要執行壓縮作業,應該在移除片段之前執行。

縮減堆積中的片段

若要縮減堆積的範圍片段,請在資料表上建立叢集索引,然後再卸除該索引。此舉可以轉散發資料,同時又可以建立叢集索引。在散發資料庫中的可用空間時,也可以達到最佳效果。當您卸除叢集索引來重新建立堆積時,這些資料仍然留在原地,不會移走。如需有關如何執行這些作業的詳細資訊,請參閱<CREATE INDEX>和<DROP INDEX>。

Caution注意
在資料表上建立和卸除叢集索引時,會重建該資料表上的所有非叢集索引兩次。

壓縮大型物件資料

根據預設,ALTER INDEX REORGANIZE 陳述式會壓縮含有大型物件 (LOB) 資料的頁面。由於 LOB 頁在空白時不會被取消配置,因此如果已經刪除許多 LOB 資料,或是已經卸除一個 LOB 資料行,可以壓縮這些資料來改善磁碟空間的使用。

重新組織指定的叢集索引會壓縮叢集索引所包含的所有 LOB 資料行。重新組織非叢集索引會壓縮索引中本身是非索引鍵資料行 (內含資料行) 的所有 LOB 資料行。當陳述式內指定 ALL 時,所有與指定之資料表或檢視表相關的索引都會重新組織。此外,所有與叢集索引、基礎資料表或附有內含資料行之非叢集索引相關聯的 LOB 資料行都會壓縮。

評估磁碟空間的使用情形

avg_page_space_used_in_percent 資料行會指出頁面是否已經飽和。若要妥善利用磁碟空間,對於沒有太多隨機插入的索引來說,這個值愈接近 100% 愈好。不過,如果索引有許多隨機插入和非常飽和的頁面,頁面分割數會增加。這會造成更多的片段。因此,為了減少頁面分割,這個值最好能夠少於 100%。指定 FILLFACTOR 選項來重建索引可以改變頁面飽和,讓它配合索引的查詢模式。如需有關填滿因數的詳細資訊,請參閱<Fill Factor>。同時,ALTER INDEX REORGANIZE 會盡量配合上次指定的 FILLFACTOR 來填滿頁面,以壓縮索引。這會增加 avg_space_used_in_percent 的值。請注意,ALTER INDEX REORGANIZE 無法縮減頁面飽和。您必須重建索引才行。

評估索引片段

片段是由配置單位之同一檔案中,實際連續的分葉頁所組成。一個索引至少有一個片段。一個索引最多能擁有的片段數相當於該索引之分葉層級中的頁數。片段較大表示若要讀取同樣數量的頁數,所需的磁碟 I/O 更少。因此,avg_fragment_size_in_pages 值愈大,範圍掃描效能愈佳。avg_fragment_size_in_pages 和 avg_fragmentation_in_percent 值彼此成反比。因此,重建或重新組織索引都會減少片段的數量,以及加大片段。

需要下列權限:

  • 對資料庫中的指定物件具備 CONTROL 權限。

  • VIEW DATABASE STATE 權限,利用物件萬用字元 @object_id=NULL,傳回指定之資料庫中所有物件的相關資訊。

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

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

如需詳細資訊,請參閱<Dynamic Management Objects>。

本文對您有任何幫助嗎?
(剩餘 1500 個字元)
感謝您提供意見

社群新增項目

新增
顯示:
© 2014 Microsoft. 著作權所有,並保留一切權利。