sys.dm_db_index_physical_stats (Transact-SQL)sys.dm_db_index_physical_stats (Transact-SQL)

本主題適用於:是SQL Server (從 2008 開始)是Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

傳回 SQL ServerSQL Server 中指定資料表或檢視之資料和索引的大小和片段資訊。Returns size and fragmentation information for the data and indexes of the specified table or view in SQL ServerSQL Server. 如果是索引,則針對每個分割區中每個層級的 B 型樹狀目錄,各傳回一個資料列。For an index, one row is returned for each level of the B-tree in each partition. 如果是堆積,則針對每個分割區中的 IN_ROW_DATA 配置單位,各傳回一個資料列。For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. 如果是大型物件 (LOB) 資料,則針對每個分割區中的 LOB_DATA 配置單位,各傳回一個資料列。For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. 如果資料表中有資料列溢位資料,則針對每個分割區中的 ROW_OVERFLOW_DATA 配置單位,各傳回一個資料列。If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. 不傳回 xVelocity 記憶體最佳化資料行存放區索引的相關資訊。Does not return information about xVelocity memory optimized columnstore indexes.

重要

如果您查詢sys.dm_db_index_physical_stats裝載 Alwayson 的伺服器執行個體讀取的次要複本,您可能會遇到 REDO 封鎖問題。If you query sys.dm_db_index_physical_stats on a server instance that is hosting an Always On readable secondary replica, you might encounter a REDO blocking issue. 這是因為此動態管理檢視會取得指定使用者資料表或檢視表的 IS 鎖定,並因此而封鎖了對該使用者資料表或檢視表之 X 鎖定的 REDO 執行緒要求。This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.

sys.dm_db_index_physical_stats不會傳回記憶體最佳化索引的相關資訊。sys.dm_db_index_physical_stats does not return information about memory-optimized indexes. 記憶體最佳化索引用法的相關資訊,請參閱sys.dm_db_xtp_index_stats (TRANSACT-SQL)For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


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 }  
)  

引數Arguments

database_id |NULL |0 |預設值database_id | NULL | 0 | DEFAULT
資料庫的識別碼。Is the ID of the database. database_idsmallintdatabase_id is smallint. 有效的輸入為資料庫的識別碼、NULL、0 或 DEFAULT。Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. 預設值是 0。The default is 0. NULL、0 和 DEFAULT 是這個內容中的對等值。NULL, 0, and DEFAULT are equivalent values in this context.

請指定 NULL 來傳回 SQL ServerSQL Server 執行個體中之所有資料庫的資訊。Specify NULL to return information for all databases in the instance of SQL ServerSQL Server. 如果您指定 NULL database_id,您也必須指定為 NULL object_idindex_id,和。If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.

內建函式DB_ID可以指定。The built-in function DB_ID can be specified. 在不指定資料庫名稱的情況下使用 DB_ID 時,目前資料庫的相容性層級必須是 90 或 90 以上。When using DB_ID without specifying a database name, the compatibility level of the current database must be 90 or greater.

object_id |NULL |0 |預設值object_id | NULL | 0 | DEFAULT
這是索引所在之資料表或檢視表的物件識別碼。Is the object ID of the table or view the index is on. @object_idintobject_id is int.

有效的輸入為資料表和檢視表的識別碼、NULL、0 或 DEFAULT。Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. 預設值是 0。The default is 0. NULL、0 和 DEFAULT 是這個內容中的對等值。NULL, 0, and DEFAULT are equivalent values in this context. SQL Server 2016 (13.x)SQL Server 2016 (13.x),有效的輸入也會包含 service broker 佇列名稱或佇列內部的資料表名稱。As of SQL Server 2016 (13.x)SQL Server 2016 (13.x), valid inputs also include the service broker queue name or the queue internal table name. 預設參數會套用時 (也就是所有物件,所有的索引,依此類推),結果集中包含的所有佇列的片段資訊。When default parameters are applied (i.e. all objects, all indexes, etc), fragmentation information for all queues are included in the result set.

指定 NULL 來傳回指定之資料庫中的所有資料表和檢視表的資訊。Specify NULL to return information for all tables and views in the specified database. 如果您指定 NULL object_id,您也必須指定為 NULL index_id和。If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.

index_id | 0 |NULL |-1 |預設值index_id | 0 | NULL | -1 | DEFAULT
這是索引的識別碼。Is the ID of the index. index_idint。有效輸入如下的索引 0 的識別碼,如果object_id是堆積,NULL,-1 或預設值。index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. 預設值為-1。The default is -1. NULL、-1,且預設值是在此內容中的對等值。NULL, -1, and DEFAULT are equivalent values in this context.

請指定 NULL 來傳回基底資料表或檢視表的所有索引資訊。Specify NULL to return information for all indexes for a base table or view. 如果您指定 NULL index_id,您也必須指定為 NULL 。If you specify NULL for index_id, you must also specify NULL for partition_number.

|NULL |0 |預設值partition_number | NULL | 0 | DEFAULT
這是物件的分割區編號。Is the partition number in the object. int。有效輸入如下: partion_number的索引或堆積,NULL、 0 或 DEFAULT。partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. 預設值是 0。The default is 0. NULL、0 和 DEFAULT 是這個內容中的對等值。NULL, 0, and DEFAULT are equivalent values in this context.

請指定 NULL 來傳回主控物件之所有分割區的相關資訊。Specify NULL to return information for all partitions of the owning object.

是以 1 為基礎。partition_number is 1-based. 非資料分割索引或堆積將設為 1。A nonpartitioned index or heap has partition_number set to 1.

模式|NULL |預設值mode | NULL | DEFAULT
這是模式的名稱。Is the name of the mode. 模式指定用來取得統計資料的掃描層級。mode specifies the scan level that is used to obtain statistics. 模式sysnamemode is sysname. 有效輸入為 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. 預設值 (NULL) 是 LIMITED。The default (NULL) is LIMITED.

傳回的資料表Table Returned

資料行名稱Column name 資料類型Data type DescriptionDescription
database_iddatabase_id smallintsmallint 資料表或檢視表的資料庫識別碼。Database ID of the table or view.
object_idobject_id intint 索引所在之資料表或檢視表的物件識別碼。Object ID of the table or view that the index is on.
index_idindex_id intint 索引的索引識別碼。Index ID of an index.

0 = 堆積。0 = Heap.
partition_numberpartition_number intint 在主控物件內,以 1 為基底的分割區編號;資料表、檢視表或索引。1-based partition number within the owning object; a table, view, or index.

1 = 非分割區的索引或堆積。1 = Nonpartitioned index or heap.
index_type_descindex_type_desc nvarchar(60)nvarchar(60) 索引類型的描述:Description of the index type:

HEAPHEAP

CLUSTERED INDEXCLUSTERED INDEX

NONCLUSTERED INDEXNONCLUSTERED INDEX

PRIMARY XML INDEXPRIMARY XML INDEX

SPATIAL INDEXSPATIAL INDEX

XML INDEXXML INDEX

資料行存放區對應的索引 (內部)COLUMNSTORE MAPPING INDEX (internal)

資料行存放區 DELETEBUFFER 索引 (內部)COLUMNSTORE DELETEBUFFER INDEX (internal)

資料行存放區 DELETEBITMAP 索引 (內部)COLUMNSTORE DELETEBITMAP INDEX (internal)
hobt_idhobt_id bigintbigint 堆積或 B 型樹狀目錄的索引或資料分割的識別碼。Heap or B-Tree ID of the index or partition.

除了傳回的 hobt_id 的使用者定義的索引,這也會傳回內部的資料行存放區索引的 hobt_id。Besides returning the hobt_id of user-defined indexes, this also returns the hobt_id of the internal columnstore indexes.
alloc_unit_type_descalloc_unit_type_desc nvarchar(60)nvarchar(60) 配置單位類型的描述:Description of the allocation unit type:

IN_ROW_DATAIN_ROW_DATA

LOB_DATALOB_DATA

ROW_OVERFLOW_DATAROW_OVERFLOW_DATA

如果是 LOB_DATA 配置單位包含儲存在類型的資料行的資料文字ntext映像varchar (max)nvarchar (max)varbinary (max),和xmlThe LOB_DATA allocation unit contains the data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. 如需詳細資訊,請參閱資料類型 (Transact-SQL)For more information, see Data Types (Transact-SQL).

ROW_OVERFLOW_DATA 配置單位包含儲存在類型的資料行的資料varchar (n)nvarchar (n)varbinary,和sql_variant已被發送非資料列。The ROW_OVERFLOW_DATA allocation unit contains the data that is stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that have been pushed off-row.
index_depthindex_depth tinyinttinyint 索引層級的數目。Number of index levels.

1 = 堆積,或 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位。1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.
index_levelindex_level tinyinttinyint 索引的目前層級。Current level of the index.

如果是索引分葉層級、堆積和 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為 0。0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

如果是非分葉索引層級,則大於 0。Greater than 0 for nonleaf index levels. index_level是最高根層級的索引。index_level will be the highest at the root level of an index.

索引的非分葉層級只會處理時模式= DETAILED。The nonleaf levels of indexes are only processed when mode = DETAILED.
avg_fragmentation_in_percentavg_fragmentation_in_percent floatfloat IN_ROW_DATA 配置單位中,索引的邏輯片段或是堆積的範圍片段。Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

其值以百分比表示,而且會考量多個檔案。The value is measured as a percentage and takes into account multiple files. 如需邏輯和範圍片段的定義,請參閱<備註>一節。For definitions of logical and extent fragmentation, see Remarks.

如果是 LOB_DATA 和 ROW_OVERFLOW_DATA 配置單位,則為 0。0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL 堆積,則模式= SAMPLED。NULL for heaps when mode = SAMPLED.
fragment_countfragment_count bigintbigint IN_ROW_DATA 配置單位分葉層級中的片段數目。Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. 如需有關片段的詳細資訊,請參閱<備註>一節。For more information about fragments, see Remarks.

如果是索引的非分葉層級,以及 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為 NULL。NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL 堆積,則模式= SAMPLED。NULL for heaps when mode = SAMPLED.
avg_fragment_size_in_pagesavg_fragment_size_in_pages floatfloat IN_ROW_DATA 配置單位的分葉層級中,一個片段的平均頁數。Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.

如果是索引的非分葉層級,以及 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為 NULL。NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL 堆積,則模式= SAMPLED。NULL for heaps when mode = SAMPLED.
page_countpage_count bigintbigint 索引或資料頁總數。Total number of index or data pages.

如果是索引,則為 IN_ROW_DATA 配置單位中,B 型樹狀目錄目前層級的總索引頁數。For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

如果是堆積,則為 IN_ROW_DATA 配置單位中的總資料頁數。For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為配置單位中的總頁數。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.
avg_page_space_used_in_percentavg_page_space_used_in_percent floatfloat 所有頁面所用之可用資料儲存空間的平均百分比。Average percentage of available data storage space used in all pages.

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的平均數。For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

如果是堆積,則為 IN_ROW_DATA 配置單位中所有資料頁的平均數。For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

如果是 LOB_DATA 或 ROW_OVERFLOW DATA 配置單位,則為配置單位中所有頁面的平均數。For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

NULL模式= LIMITED。NULL when mode = LIMITED.
record_countrecord_count bigintbigint 總記錄數。Total number of records.

如果是索引,則為 IN_ROW_DATA 配置單位中,B 型樹狀目錄目前層級的總記錄數。For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

如果是堆積,則為 IN_ROW_DATA 配置單位中的總記錄數。For a heap, the total number of records in the IN_ROW_DATA allocation unit.

注意: 是堆積,從此函數傳回的記錄數目可能不符合執行選取的計數所傳回的資料列數目 (*) 針對該堆積。Note: For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. 這是因為一個資料列可能包含數筆記錄。This is because a row may contain multiple records. 例如,在某些更新情況下,單一的堆積資料列可能有一筆轉送記錄以及一筆當做更新作業結果的轉送記錄。For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. 同時,在 LOB_DATA 儲存體中,會將多數大型的 LOB 資料列分割為多筆記錄。Also, most large LOB rows are split into multiple records in LOB_DATA storage.

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的總記錄數。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

NULL模式= LIMITED。NULL when mode = LIMITED.
ghost_record_countghost_record_count bigintbigint 配置單位中,準刪除清除工作準備要移除的準刪除記錄數。Number of ghost records ready for removal by the ghost cleanup task in the allocation unit.

如果是 IN_ROW_DATA 配置單位中索引的非分葉層級,則為 0。0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL模式= LIMITED。NULL when mode = LIMITED.
version_ghost_record_countversion_ghost_record_count bigintbigint 配置單位中未完成之快照集隔離交易所保留的準刪除記錄數。Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

如果是 IN_ROW_DATA 配置單位中索引的非分葉層級,則為 0。0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL模式= LIMITED。NULL when mode = LIMITED.
min_record_size_in_bytesmin_record_size_in_bytes intint 記錄大小下限 (以位元組為單位)。Minimum record size in bytes.

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的記錄大小下限。For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

如果是堆積,則為 IN_ROW_DATA 配置單位中的記錄大小下限。For a heap, the minimum record size in the IN_ROW_DATA allocation unit.

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的記錄大小下限。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit.

NULL模式= LIMITED。NULL when mode = LIMITED.
max_record_size_in_bytesmax_record_size_in_bytes intint 記錄大小上限 (以位元組為單位)。Maximum record size in bytes.

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的記錄大小上限。For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

如果是堆積,則為 IN_ROW_DATA 配置單位中的記錄大小上限。For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的記錄大小上限。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

NULL模式= LIMITED。NULL when mode = LIMITED.
avg_record_size_in_bytesavg_record_size_in_bytes floatfloat 記錄大小平均值 (以位元組為單位)。Average record size in bytes.

如果是索引,則為 IN_ROW_DATA 配置單位中 B 型樹狀目錄目前層級的平均記錄大小。For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

如果是堆積,則為 IN_ROW_DATA 配置單位中的平均記錄大小。For a heap, the average record size in the IN_ROW_DATA allocation unit.

如果是 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位,則為完整配置單位中的平均記錄大小。For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit.

NULL模式= LIMITED。NULL when mode = LIMITED.
forwarded_record_countforwarded_record_count bigintbigint 在堆積中,有指向另一個資料位置之轉送指標的記錄數目Number of records in a heap that have forward pointers to another data location. (此狀態發生於更新期間,原始位置的空間不足以儲存新資料列時)。(This state occurs during an update, when there is not enough room to store the new row in the original location.)

如果是 IN_ROW_DATA 配置單位以外的任何堆積配置單位,則為 NULL。NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap.

NULL 堆積,則模式= LIMITED。NULL for heaps when mode = LIMITED.
compressed_page_countcompressed_page_count bigintbigint 壓縮的頁面數。The number of compressed pages.

如果是堆積,新配置的頁面不會使用 PAGE 壓縮方式。For heaps, newly allocated pages are not PAGE compressed. 堆積會在兩個特殊情況下使用 PAGE 壓縮方式:大量匯入資料或是重建堆積時。A heap is PAGE compressed under two special conditions: when data is bulk imported or when a heap is rebuilt. 造成頁面配置的一般 DML 作業將不會使用 PAGE 壓縮方式。Typical DML operations that cause page allocations will not be PAGE compressed. 當 compressed_page_count 值成長到大於您要的臨界值時,重建堆積。Rebuild a heap when the compressed_page_count value grows larger than the threshold you want.

如果是具有叢集索引的資料表,compressed_page_count 值會指示 PAGE 壓縮的效能。For tables that have a clustered index, the compressed_page_count value indicates the effectiveness of PAGE compression.
hobt_idhobt_id bigintbigint 適用於SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) 透過 目前版本)、 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Azure SQL DatabaseAzure SQL Database.

只有資料行存放區索引,這是內部的資料行存放區資料分割的資料會追蹤的資料列集的識別碼。For columnstore indexes only, this is the ID for a rowset that tracks internal columnstore data for a partition. 資料列集是儲存為堆積,則資料或二進位樹狀目錄。The rowsets are stored as data heaps or binary trees. 它們有相同的索引識別碼,做為父資料行存放區索引。They have the same index ID as the parent columnstore index. 如需詳細資訊,請參閱sys.internal_partitions (TRANSACT-SQL)For more information, see sys.internal_partitions (Transact-SQL).

如果為 NULLNULL if
column_store_delete_buffer_statecolumn_store_delete_buffer_state tinyinttinyint 適用於SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) 透過 目前版本)、 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Azure SQL DatabaseAzure SQL Database.

0 = NOT_APPLICABLE0 = NOT_APPLICABLE

1 = OPEN1 = OPEN

2 = 清空2 = DRAINING

3 = 排清3 = FLUSHING

4 = 淘汰4 = RETIRING

5 = 已備妥5 = READY
column_store_delete_buff_state_desccolumn_store_delete_buff_state_desc 適用於SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) 透過 目前版本)、 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Azure SQL DatabaseAzure SQL Database.

NOT_APPLICABLE – 父索引不是資料行存放區索引。NOT_APPLICABLE –the parent index is not a columnstore index.

OPEN – deleters 並用掃描器。OPEN – deleters and scanners use this.

水管 – 清空 deleters 但掃描器仍然使用它。DRAINING – deleters are draining out but scanners still use it.

排清 – 緩衝區已關閉,並刪除點陣圖正在寫入緩衝區中的資料列。FLUSHING – buffer is closed and rows in the buffer are being written to the delete bitmap.

淘汰 – 已關閉的刪除緩衝區中的資料列寫入為刪除的點陣圖,但因為掃描器仍在使用它緩衝區沒有被截斷。RETIRING – rows in the closed delete buffer have been written to the delete bitmap, but the buffer has not been truncated because scanners are still using it. 新的掃描程式不需要使用淘汰的緩衝區,因為開啟的緩衝區已足夠。New scanners don’t need to use the retiring buffer because the open buffer is enough.

已準備好 – 此刪除緩衝區可供使用。READY – This delete buffer is ready for use.

備註Remarks

sys.dm_db_index_physical_stats 動態管理函數會取代 DBCC SHOWCONTIG 陳述式。The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

掃描模式Scanning Modes

執行該函數的模式決定了取得該函數所用之統計資料所執行的掃描層級。The mode in which the function is executed determines the level of scanning performed to obtain the statistical data that is used by the function. 模式指定為 LIMITED、 SAMPLED 或 DETAILED。mode is specified as LIMITED, SAMPLED, or DETAILED. 此函數會周遊構成資料表或索引之指定分割區的配置單位頁面鏈結。The function traverses the page chains for the allocation units that make up the specified partitions of the table or index. sys.dm_db_index_physical_stats 只需要一個意圖共用 (IS) 資料表鎖定,不論它執行的模式。sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in.

LIMITED 模式是最快的模式,而且可以掃描最少的頁數。The LIMITED mode is the fastest mode and scans the smallest number of pages. 若是索引,僅會掃描 B 型樹狀目錄的父層級頁 (亦即分葉層級上面的頁面)。For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. 若是堆積,則會檢查相關聯的 PFS 和 IAM 頁面,並以 LIMITED 模式掃描堆積的資料頁面。For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

在 LIMITED 模式下,compressed_page_count 是 NULL,因為 Database EngineDatabase Engine 只會掃描 B 型樹狀目錄的非分葉頁面以及堆積的 IAM 和 PFS 頁面。With LIMITED mode, compressed_page_count is NULL because the Database EngineDatabase Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. 使用 SAMPLED 模式可取得 compressed_page_count 的預估的值,並使用 DETAILED 的模式可取得 compressed_page_count 的實際值。Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. SAMPLED 模式會傳回統計資料,該統計資料是根據索引或堆積中所有頁面之百分之 1 的取樣。The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. SAMPLED 模式的結果應該被視為近似。Results in SAMPLED mode should be regarded as approximate. 如果索引或堆積的頁面少於 10,000 頁,則改以 DETAILED 模式取代 SAMPLED。If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.

DETAILED 模式會掃描所有的頁面,並且傳回所有的統計資料。The DETAILED mode scans all pages and returns all statistics.

從 LIMITED 到 DETAILED 的模式會愈來愈慢,因為每個模式執行的工作愈來愈多。The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. 若要快速測量資料表或索引的大小或片段層級,請使用 LIMITED 模式。To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. 它是最快速的模式,而且不會針對索引的 IN_ROW_DATA 配置單位中每個非分葉層級,各傳回一個資料列。It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.

使用系統函數指定參數值Using System Functions to Specify Parameter Values

您可以使用 Transact-SQLTransact-SQL函式DB_IDOBJECT_ID至指定的值database_idobject_id參數。You can use the Transact-SQLTransact-SQL functions DB_ID and OBJECT_ID to specify a value for the database_id and object_id parameters. 不過,傳遞對這些函數無效的值可能會造成意料之外的結果。However, passing values that are not valid to these functions may cause unintended results. 例如,如果找不到資料庫或物件名稱 (因為不存在或是拼法不正確),這兩個函數都會傳回 NULL。For example, if the database or object name cannot be found because they do not exist or are spelled incorrectly, both functions will return NULL. sys.dm_db_index_physical_stats 函數會將 NULL 解譯為萬用字元值,指定所有的資料庫或物件。The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.

此外,OBJECT_ID 函數會處理之前在呼叫 sys.dm_db_index_physical_stats 函數,而且因此目前資料庫的內容中評估,不是資料庫中指定database_idAdditionally, the OBJECT_ID function is processed before the sys.dm_db_index_physical_stats function is called and is therefore evaluated in the context of the current database, not the database specified in database_id. 這個行為可能會讓 OBJECT_ID 函數傳回 NULL 值;如果物件名稱同時存在於目前的資料庫內容和指定的資料庫中,則可能會傳回錯誤訊息。This behavior may cause the OBJECT_ID function to return a NULL value; or, if the object name exists in both the current database context and the specified database, an error message may be returned. 下列範例示範這些意料之外的結果。The following examples demonstrate these unintended results.

USE master;  
GO  
-- In this example, OBJECT_ID is evaluated in the context of the master database.   
-- Because Person.Address does not exist in master, the function returns NULL.  
-- When NULL is specified as an object_id, all objects in the database are returned.  
-- The same results are returned when an object that is not valid is specified.  
SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
GO  
-- This example demonstrates the results of specifying a valid object name  
-- that exists in both the current database context and  
-- in the database specified in the database_id parameter of the   
-- sys.dm_db_index_physical_stats function.  
-- An error is returned because the ID value returned by OBJECT_ID does not  
-- match the ID value of the object in the specified database.  
CREATE DATABASE Test;  
GO  
USE Test;  
GO  
CREATE SCHEMA Person;  
GO  
CREATE Table Person.Address(c1 int);  
GO  
USE AdventureWorks2012;  
GO  
SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
GO  
-- Clean up temporary database.  
DROP DATABASE Test;  
GO  

最佳作法Best Practice

使用 DB_ID 或 OBJECT_ID 時,請務必確定傳回的是有效的識別碼。Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. 例如,當您使用 OBJECT_ID 時,例如指定三部分名稱OBJECT_ID(N'AdventureWorks2012.Person.Address'),或測試您用於 sys.dm_db_index_physical_stats 函數之前,函式所傳回的值。For example, when you use OBJECT_ID, specify a three-part name such as OBJECT_ID(N'AdventureWorks2012.Person.Address'), or test the value returned by the functions before you use them in the sys.dm_db_index_physical_stats function. 下面的範例 A 和 B 示範指定資料庫和物件識別碼的安全方法。Examples A and B that follow demonstrate a safe way to specify database and object IDs.

偵測片段Detecting Fragmentation

片段是經由針對資料表建立的資料修改程序 (INSERT、UPDATE 和 DELETE 陳述式) 而產生,因此會產生到資料表上定義的索引。Fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and, therefore, to the indexes defined on the table. 由於這些修改通常不會平均散發在資料表和索引的各個資料列上,因此,各頁面的飽和度可能會隨著時間而不同。Because these modifications are not ordinarily distributed equally among the rows of the table and indexes, the fullness of each page can vary over time. 如果查詢要掃描部分或全部的資料表索引,這類資料表片段可能會造成額外的頁面讀取。For queries that scan part or all of the indexes of a table, this kind of fragmentation can cause additional page reads. 這會防礙資料的平行掃描。This hinders parallel scanning of data.

索引或堆積的片段層級會顯示在 avg_fragmentation_in_percent 資料行中。The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. 如果是堆積,這個值代表該堆積的範圍片段。For heaps, the value represents the extent fragmentation of the heap. 如果是索引,則這個值代表該索引的邏輯片段。For indexes, the value represents the logical fragmentation of the index. 與 DBCC SHOWCONTIG 不同的是,這兩種情況的片段計算演算法,都採用跨越多個檔案的儲存體,因此也較精確。Unlike DBCC SHOWCONTIG, the fragmentation calculation algorithms in both cases consider storage that spans multiple files and, therefore, are accurate.

邏輯片段Logical Fragmentation

這是索引分葉頁中,失序頁面的百分比。This is the percentage of out-of-order pages in the leaf pages of an index. 失序頁面是指配置給索引之下一個實體頁面的頁面,而不是目前分葉頁中下一頁指標所指向的頁面。An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

範圍片段Extent Fragmentation

這是堆積分葉頁中,失序範圍的百分比。This is the percentage of out-of-order extents in the leaf pages of a heap. 失序範圍是指某個包含目前堆積頁面的範圍,實際上不是包含上一頁之範圍後面的下一個範圍。An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

avg_fragmentation_in_percent 的值愈接近零,其效能愈好。The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. 不過,百分之 0 到 10 之間的值都在接受範圍內。However, values from 0 percent through 10 percent may be acceptable. 所有縮減片段的方法 (例如,重建、重新組織或重新建立) 都可以用來縮減這些值。All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. 如需如何分析索引片段的程度的詳細資訊,請參閱Reorganize and Rebuild IndexesFor more information about how to analyze the degree of fragmentation in an index, see Reorganize and Rebuild Indexes.

縮減索引中的片段Reducing Fragmentation in an Index

如果對索引進行片段作業之後,片段會影響到查詢效能,有三個選擇可減少片段:When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

  • 卸除和重新建立叢集索引。Drop and re-create the clustered index.

    重新建立叢集索引會轉散發資料,造成飽和的資料頁面。Re-creating a clustered index redistributes the data and results in full data pages. 您可以在 CREATE INDEX 中使用 FILLFACTOR 選項來設定飽和度的層級。The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. 這個方法的缺點是在卸除和重建周期內索引是離線的,而且作業不可部分完成。The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. 如果中斷了索引建立,就不會重建索引。If the index creation is interrupted, the index is not re-created. 如需詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).

  • 以 ALTER INDEX REORGANIZE 代替 DBCC INDEXDEFRAG,依照邏輯順序來重新排列索引的分葉層級頁面。Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. 由於這是一項線上作業,因此當執行陳述式時,可以使用索引。Because this is an online operation, the index is available while the statement is running. 這項作業即使被中斷,也不會遺失已經完成的工作。The operation can also be interrupted without losing work already completed. 這個方法的缺點是,其資料重新組織作業不如索引重建作業來得好,而且也不能更新統計資料。The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.

  • 以 ALTER INDEX REBUILD 代替 DBCC DBREINDEX,以線上或離線方式重建索引。Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. 如需詳細資訊,請參閱 ALTER INDEX (Transact-SQL)For more information, see ALTER INDEX (Transact-SQL).

    單獨片段的這個理由,不足以重新組織或重建索引。Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. 片段的主要影響是降低掃描索引時的頁面預先讀取的輸送量。The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. 這樣會使得回應時間更慢。This causes slower response times. 如果片段化的資料表或索引上的查詢工作負載不含掃描在內 (因為工作負載主要是單一查閱),移除片段不會有任何影響。If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect. 如需詳細資訊,請參閱此Microsoft 寍鯚For more information, see this Microsoft Web site.

注意

如果在壓縮作業時,部分或完全移動索引,則執行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 可能會導入片段。Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is partly or completely moved during the shrink operation. 因此,即使一定要執行壓縮作業,應該在移除片段之前執行。Therefore, if a shrink operation must be performed, you should do it before fragmentation is removed.

縮減堆積中的片段Reducing Fragmentation in a Heap

若要縮減堆積的範圍片段,請在資料表上建立叢集索引,然後再卸除該索引。To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index. 此舉會在建立叢集索引的同時轉散發資料。This redistributes the data while the clustered index is created. 在散發資料庫中的可用空間時,也可以達到最佳效果。This also makes it as optimal as possible, considering the distribution of free space available in the database. 當您卸除叢集索引來重新建立堆積時,這些資料仍然以最理想的方式留在原地,不會移走。When the clustered index is then dropped to re-create the heap, the data is not moved and remains optimally in position. 如需如何執行這些作業的資訊,請參閱CREATE INDEXDROP INDEXFor information about how to perform these operations, see CREATE INDEX and DROP INDEX.

警告

在資料表上建立和卸除叢集索引時,會重建該資料表上的所有非叢集索引兩次。Creating and dropping a clustered index on a table, rebuilds all non-clustered indexes on that table twice.

壓縮大型物件資料Compacting Large Object Data

根據預設,ALTER INDEX REORGANIZE 陳述式會壓縮含有大型物件 (LOB) 資料的頁面。By default, the ALTER INDEX REORGANIZE statement compacts pages that contain large object (LOB) data. 由於 LOB 頁在空白時不會被取消配置,因此如果已經刪除許多 LOB 資料,或是已經卸除一個 LOB 資料行,可以壓縮這些資料來改善磁碟空間的使用。Because LOB pages are not deallocated when empty, compacting this data can improve disk space use if lots of LOB data have been deleted, or a LOB column is dropped.

重新組織指定的叢集索引會壓縮叢集索引所包含的所有 LOB 資料行。Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. 重新組織非叢集索引會壓縮索引中本身是非索引鍵資料行 (內含資料行) 的所有 LOB 資料行。Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. 當陳述式內指定 ALL 時,所有與指定之資料表或檢視表相關的索引都會重新組織。When ALL is specified in the statement, all indexes that are associated with the specified table or view are reorganized. 此外,所有與叢集索引、基礎資料表或附有內含資料行之非叢集索引相關聯的 LOB 資料行都會壓縮。Additionally, all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

評估磁碟空間的使用情形Evaluating Disk Space Use

avg_page_space_used_in_percent 資料行會指出頁面是否已經飽和。The avg_page_space_used_in_percent column indicates page fullness. 若要妥善利用磁碟空間,對於沒有太多隨機插入的索引來說,這個值愈接近 100% 愈好。To achieve optimal disk space use, this value should be close to 100 percent for an index that will not have many random inserts. 不過,如果索引有許多隨機插入和非常飽和的頁面,頁面分割數會增加。However, an index that has many random inserts and has very full pages will have an increased number of page splits. 這會造成更多的片段。This causes more fragmentation. 因此,為了減少頁面分割,這個值最好能夠少於 100%。Therefore, in order to reduce page splits, the value should be less than 100 percent. 指定 FILLFACTOR 選項來重建索引可以改變頁面飽和,讓它配合索引的查詢模式。Rebuilding an index with the FILLFACTOR option specified allows the page fullness to be changed to fit the query pattern on the index. 如需有關填滿因數的詳細資訊,請參閱指定索引的填滿因數For more information about fill factor, see Specify Fill Factor for an Index. 同時,ALTER INDEX REORGANIZE 會盡量配合上次指定的 FILLFACTOR 來填滿頁面,以壓縮索引。Also, ALTER INDEX REORGANIZE will compact an index by trying to fill pages to the FILLFACTOR that was last specified. 此舉會將 avg_space_used_in_percent 中的值提高。This increases the value in avg_space_used_in_percent. 請注意,ALTER INDEX REORGANIZE 無法縮減頁面飽和。Note that ALTER INDEX REORGANIZE cannot reduce page fullness. 您必須重建索引才行。Instead, an index rebuild must be performed.

評估索引片段Evaluating Index Fragments

片段是由配置單位之同一檔案中,實際連續的分葉頁所組成。A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. 一個索引至少有一個片段。An index has at least one fragment. 一個索引最多能擁有的片段數相當於該索引之分葉層級中的頁數。The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. 片段較大表示若要讀取同樣數量的頁數,所需的磁碟 I/O 更少。Larger fragments mean that less disk I/O is required to read the same number of pages. 因此,avg_fragment_size_in_pages 值愈大,範圍掃描效能愈佳。Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. avg_fragment_size_in_pages 和 avg_fragmentation_in_percent 值彼此成反比。The avg_fragment_size_in_pages and avg_fragmentation_in_percent values are inversely proportional to each other. 因此,重建或重新組織索引都會減少片段的數量,以及加大片段。Therefore, rebuilding or reorganizing an index should reduce the amount of fragmentation and increase the fragment size.

限制事項Limitations and Restrictions

不會傳回叢集資料行存放區索引的資料。Does not return data for clustered columnstore indexes.

PermissionsPermissions

需要下列權限:Requires the following permissions:

  • 對資料庫中的指定物件具備 CONTROL 權限。CONTROL permission on the specified object within the database.

  • VIEW DATABASE STATE 權限,以傳回指定的資料庫中的所有物件的相關資訊,利用物件萬用字元 @object_id= NULL。VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id=NULL.

  • VIEW SERVER STATE 權限,以傳回所有的資料庫的相關資訊,利用資料庫萬用字元 @database_id = NULL。VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL.

    授與 VIEW DATABASE STATE 可以傳回資料庫中的所有物件,不論特定物件是否拒絕任何 CONTROL 權限。Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

    拒絕 VIEW DATABASE STATE 會造成不允許傳回資料庫中的所有物件 (不論是否授與特定物件任何 CONTROL 權限)。Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. 也,當資料庫萬用字元 @database_id= 指定 NULL,則會省略資料庫。Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

    如需詳細資訊,請參閱動態管理檢視和函數(TRANSACT-SQL)For more information, see Dynamic Management Views and Functions (Transact-SQL).

範例Examples

A.A. 傳回指定之資料表的相關資訊Returning information about a specified table

下列範例會傳回 Person.Address 資料表之所有索引和分割區的大小和片段統計資料。The following example returns size and fragmentation statistics for all indexes and partitions of the Person.Address table. 掃描模式設為 'LIMITED',以達到最佳效能,並且限制傳回的統計資料。The scan mode is set to 'LIMITED' for best performance and to limit the statistics that are returned. 若要執行這項查詢,至少必須有 Person.Address 資料表的 CONTROL 權限。Executing this query requires, at a minimum, CONTROL permission on the Person.Address table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');  
END;  
GO  

B.B. 傳回堆積的相關資訊Returning information about a heap

下列範例會傳回 AdventureWorks2012AdventureWorks2012 資料庫中 dbo.DatabaseLog 堆積的所有統計資料。The following example returns all statistics for the heap dbo.DatabaseLog in the AdventureWorks2012AdventureWorks2012 database. 由於資料表包含 LOB 資料,因此除了針對儲存堆積資料頁的 LOB_DATA 傳回資料列外,IN_ROW_ALLOCATION_UNIT 配置單位也會傳回資料列。Because the table contains LOB data, a row is returned for the LOB_DATA allocation unit in addition to the row returned for the IN_ROW_ALLOCATION_UNIT that is storing the data pages of the heap. 若要執行這項查詢,至少必須有 dbo.DatabaseLog 資料表的 CONTROL 權限。Executing this query requires, at a minimum, CONTROL permission on the dbo.DatabaseLog table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.dbo.DatabaseLog');  
IF @object_id IS NULL   
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN;  
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');  
END;  
GO  

C.C. 傳回所有資料庫的相關資訊Returning information for all databases

下列範例會對所有參數指定萬用字元 NULL,以傳回 SQL ServerSQL Server 執行個體中,所有資料表和索引的所有統計資料。The following example returns all statistics for all tables and indexes within the instance of SQL ServerSQL Server by specifying the wildcard NULL for all parameters. 執行這項查詢需要 VIEW SERVER STATE 權限。Executing this query requires the VIEW SERVER STATE permission.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);  
GO  

D.D. 在指令碼中使用 sys.dm_db_index_physical_stats 來重建或重新組織索引Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

下列範例會自動重新組織或重建資料庫中所有具備 10% 以上平均片段的分割區。The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. 執行這項查詢需要 VIEW DATABASE STATE 權限。Executing this query requires the VIEW DATABASE STATE permission. 這個範例會指定第一個參數為 DB_ID,而不指定資料庫名稱。This example specifies DB_ID as the first parameter without specifying a database name. 如果目前資料庫的相容性層級是 80 或更低,將會產生錯誤。An error will be generated if the current database has a compatibility level of 80 or lower. 若要解決此錯誤,請使用有效的資料庫名稱來取代 DB_ID()To resolve the error, replace DB_ID() with a valid database name. 如需有關資料庫相容性層級的詳細資訊,請參閱ALTER DATABASE 相容性層級(TRANSACT-SQL)For more information about database compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first.  
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  

-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  

-- Open the cursor.  
OPEN partitions;  

-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  

-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  

-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO  

E.E. 使用 sys.dm_db_index_physical_stats 來顯示頁面壓縮的頁面數目Using sys.dm_db_index_physical_stats to show the number of page-compressed pages

下列範例將示範如何針對資料列與頁面壓縮的頁面顯示和比較總頁面數目。The following example shows how to display and compare the total number of pages against the pages that are row and page compressed. 這項資訊可用來判斷針對索引或資料表提供壓縮的優勢。This information can be used to determine the benefit that compression is providing for an index or table.

SELECT o.name,  
    ips.partition_number,  
    ips.index_type_desc,  
    ips.record_count, ips.avg_record_size_in_bytes,  
    ips.min_record_size_in_bytes,  
    ips.max_record_size_in_bytes,  
    ips.page_count, ips.compressed_page_count  
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips  
JOIN sys.objects o on o.object_id = ips.object_id  
ORDER BY record_count DESC;  

F.F. 在 SAMPLED 模式下使用 sys.dm_db_index_physical_statsUsing sys.dm_db_index_physical_stats in SAMPLED mode

下列範例示範 SAMPLED 模式如何傳回不同於 DETAILED 模式結果的近似結果。The following example shows how SAMPLED mode returns an approximate that is different than the DETAILED mode results.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);  
GO  
BEGIN TRAN  
DECLARE @idx int = 0;  
WHILE @idx < 1000000  
BEGIN  
    INSERT INTO t3 (col1, col2)   
    VALUES (@idx,   
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))  
    SET @idx = @idx + 1  
END  
COMMIT;  
GO  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'SAMPLED');  
SELECT page_count, compressed_page_count, forwarded_record_count, *   
FROM sys.dm_db_index_physical_stats (db_id(),   
    object_id ('t3'), null, null, 'DETAILED');  

G.G. 查詢索引片段的 service broker 佇列Querying service broker queues for index fragmentation

||
|-|
|適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL ServerSQL ServerApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL ServerSQL Server.|

下列範例將示範如何查詢 server broker 佇列的分散程度。The following examples shows how to query server broker queues for fragmentation.

--Using queue internal table name   
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('sys.queue_messages_549576996'), default, default, default)   

--Using queue name directly  
select * from sys.dm_db_index_physical_stats (db_id(), object_id ('ExpenseQueue'), default, default, default)  

另請參閱See Also

動態管理檢視與函數 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
索引相關的動態管理檢視和函式 (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL) sys.dm_db_index_operational_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact SQL) sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact SQL) sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact SQL) sys.allocation_units (Transact-SQL)
系統檢視表(Transact SQL)System Views (Transact-SQL)