sys.dm_db_index_physical_stats (Transact-SQL)

文章
10/04/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

傳回 SQL Server 資料庫引擎中指定資料表或檢視表之數據和索引的大小和片段資訊。如果是索引，則針對每個分割區中每個層級的 B 型樹狀目錄，各傳回一個資料列。如果是堆積，則針對每個分割區中的 IN_ROW_DATA 配置單位，各傳回一個資料列。對於大型物件（LOB）數據，會針對 LOB_DATA 每個數據分割的配置單位傳回一個數據列。如果數據表中有數據列溢位數據，則會針對 ROW_OVERFLOW_DATA 每個分割區中的配置單位傳回一個數據列。

注意

檔使用一般參考索引的 B 型樹狀結構一詞。在數據列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。這不適用於記憶體優化數據表上的數據行存放區索引或索引。如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

sys.dm_db_index_physical_stats 不會傳回記憶體優化索引的相關信息。如需記憶體優化索引使用的相關信息，請參閱 sys.dm_db_xtp_index_stats。

如果您在裝載可用性群組可讀取次要複本的伺服器實例上查詢sys.dm_db_index_physical_stats，可能會遇到REDO封鎖問題。這是因為這個動態管理檢視會取得指定之用戶數據表或檢視的意圖共享鎖定，而該用戶數據表或檢視可封鎖該用戶數據表或檢視上獨佔（X）鎖定的線程要求 REDO 。

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 |違約

資料庫的識別碼。 database_id為 smallint。有效的輸入是資料庫、、 NULL0或DEFAULT的標識碼。預設值為 0。 NULL、 0和 DEFAULT 在此內容中是相等的值。

指定 NULL 以傳回 SQL Server 實例中所有資料庫的資訊。如果您針對 database_id 指定，也必須針對object_id、index_id和partition_number指定。NULLNULL

可以指定內建函數 DB_ID。當您不使用指定資料庫名稱的情況下使用 DB_ID 時，目前資料庫的相容性層級必須 90 或更高。

object_id |NULL |0 |違約

索引開啟之數據表或檢視表的物件標識碼。 object_id為 int。有效的輸入是數據表和檢視表、、 NULL0或DEFAULT的識別碼。預設值為 0。 NULL、 0和 DEFAULT 在此內容中是相等的值。

在 SQL Server 2016 （13.x）和更新版本中，有效的輸入也包含 Service Broker 佇列名稱或佇列內部數據表名稱。套用預設參數時（也就是所有物件、所有索引等），所有佇列的片段信息都會包含在結果集中。

指定 NULL 以傳回指定資料庫中所有數據表和檢視的資訊。如果您針對 object_id 指定，也必須針對 index_id 和 partition_number指定。NULLNULL

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。有效的輸入是索引或堆積、、 NULL0或 DEFAULTpartion_number。預設值為 0。 NULL、 0和 DEFAULT 在此內容中是相等的值。

指定 NULL 傳回擁有物件之所有分割區的資訊。

partition_number是以 1 為基礎。非分割索引或堆積partition_number設定為 1。

mode |NULL |違約

模式的名稱。 mode 指定用來取得統計數據的掃描層級。 mode 為 sysname。有效的輸入為DEFAULT、、NULLLIMITED、 SAMPLED或 DETAILED。預設值（NULL）為 LIMITED。

傳回的資料表

資料行名稱	資料類型	描述
`database_id`	smallint	數據表或檢視表的資料庫標識碼。在 Azure SQL 資料庫中，這些值在單一資料庫或彈性集區內是唯一的，但在邏輯伺服器內則不是唯一的。
`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` - `EXTENDED INDEX` - `XML INDEX` - `COLUMNSTORE MAPPING INDEX` （內部） - `COLUMNSTORE DELETEBUFFER INDEX` （內部） - `COLUMNSTORE DELETEBITMAP INDEX` （內部）
`alloc_unit_type_desc`	nvarchar(60)	設定單位類型的描述： - `IN_ROW_DATA` - `LOB_DATA` - `ROW_OVERFLOW_DATA` 配置`LOB_DATA`單位包含儲存在 text、ntext、image、varchar（max）、nvarchar（max）、varbinary（max）和 xml 類型的數據行中。如需更多資訊，請見資料類型。配置`ROW_OVERFLOW_DATA`單位包含儲存在 varchar（n）、nvarchar（n）、varbinary（n）和sql_variant數據行中的數據，這些數據會從非數據列推送。
`index_depth`	tinyint	索引層級的數目。 `1`= 堆積或`LOB_DATAROW_OVERFLOW_DATA`配置單位。
`index_level`	tinyint	索引的目前層級。 `0` 用於索引分葉層級、堆積和 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 配置單位。大於 `0` 非分葉索引層級。 index_level是索引根層級的最高層級。只有在模式為 `DETAILED`時，才會處理索引的非分葉層級。
`avg_fragmentation_in_percent`	float	索引的邏輯片段，或配置單位中堆積的範圍 `IN_ROW_DATA` 片段。此值會以百分比來測量，並考慮多個檔案。如需邏輯和範圍片段的定義，請參閱。 `0` 針對 `LOB_DATA` 和 `ROW_OVERFLOW_DATA` 配置單位。 `NULL`當模式為 `SAMPLED`時，適用於堆積。
`fragment_count`	bigint	配置單位分 `IN_ROW_DATA` 葉層級中的片段數目。如需片段的詳細資訊，請參閱。 `NULL`表示索引的非分葉層級，或`LOB_DATAROW_OVERFLOW_DATA`配置單位。 `NULL`當模式為 `SAMPLED`時，適用於堆積。
`avg_fragment_size_in_pages`	float	配置單位分 `IN_ROW_DATA` 葉層級中一個片段的平均頁數。 `NULL`表示索引的非分葉層級，或`LOB_DATAROW_OVERFLOW_DATA`配置單位。 `NULL`當模式為 `SAMPLED`時，適用於堆積。
`page_count`	bigint	索引或數據頁的總數。針對索引，配置單位中 B 型樹狀結構目前層級的 `IN_ROW_DATA` 索引頁總數。針對堆積，配置單位中的數據 `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` 配置單位，則為配置單位中所有頁面的平均值。 `NULL`當模式為 `LIMITED`時。
`record_count`	bigint	記錄總數。針對索引，記錄總數會套用至配置單位中 `IN_ROW_DATA` B 型樹狀結構的目前層級。針對堆積，配置單位中的 `IN_ROW_DATA` 記錄總數。注意：對於堆積，從此函式傳回的記錄數目可能不符合針對堆積執行 `SELECT COUNT()` 所傳回的數據列數目。這是因為數據列可以包含多個記錄。例如，在某些更新情況下，單一堆積數據列可能會因為更新作業而有轉送記錄和轉送的記錄。此外，大部分的大型 LOB 數據列會分割成記憶體中的 `LOB_DATA` 多個記錄。針對 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 配置單位，完整配置單位中的記錄總數。 `NULL`當模式為 `LIMITED`時*。
`ghost_record_count`	bigint	可供配置單位中的準刪除清除工作移除的准刪除記錄數目。 `0` 表示配置單位中 `IN_ROW_DATA` 索引的非分葉層級。 `NULL`當模式為 `LIMITED`時。
`version_ghost_record_count`	bigint	配置單位中未完成快照隔離交易所保留的准刪除記錄數目。 `0` 表示配置單位中 `IN_ROW_DATA` 索引的非分葉層級。 `NULL`當模式為 `LIMITED`時。
`min_record_size_in_bytes`	int	以位元組為單位的最小記錄大小。針對索引，最小記錄大小會套用至配置單位中 `IN_ROW_DATA` B 型樹狀結構的目前層級。針對堆積，配置單位中的 `IN_ROW_DATA` 最小記錄大小。若為 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 配置單位，則為完整配置單位中的最小記錄大小。 `NULL`當模式為 `LIMITED`時。
`max_record_size_in_bytes`	int	以位元組為單位的記錄大小上限。針對索引，記錄大小上限會套用至配置單位中 `IN_ROW_DATA` B 型樹狀結構的目前層級。針對堆積，配置單位中的 `IN_ROW_DATA` 記錄大小上限。針對 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 配置單位，完整配置單位中的記錄大小上限。 `NULL`當模式為 `LIMITED`時。
`avg_record_size_in_bytes`	float	以位元組為單位的平均記錄大小。針對索引，平均記錄大小會套用至配置單位中 `IN_ROW_DATA` B 型樹狀結構的目前層級。針對堆積，配置單位中的 `IN_ROW_DATA` 平均記錄大小。若為 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 配置單位，則為完整配置單位中的平均記錄大小。 `NULL`當模式為 `LIMITED`時。
`forwarded_record_count`	bigint	堆積中具有指向另一個數據位置之正向指標的記錄數目。（當沒有足夠的空間將新數據列儲存在原始位置時，就會在更新期間發生此狀態。 `NULL` 對於堆積配置單位以外的 `IN_ROW_DATA` 任何配置單位。 `NULL`當模式為 `LIMITED`時，適用於堆積。
`compressed_page_count`	bigint	壓縮的頁面數目。對於堆積，不會壓縮新配置的頁面 `PAGE` 。堆積會在 `PAGE` 兩個特殊情況下壓縮：大容量導入數據或重建堆積時。導致頁面配置的典型 DML 作業不會 `PAGE` 壓縮。當值大於您想要的臨界值時 `compressed_page_count` ，重建堆積。對於具有叢集索引的數據表，值 `compressed_page_count` 表示壓縮的有效性 `PAGE` 。
`hobt_id`	bigint	索引或分割區的堆積或 B 型樹狀目錄標識碼。對於數據行存放區索引，這是追蹤數據分割內部數據行存放區數據的數據列集的標識符。數據列集會儲存為數據堆積或 B 型樹狀結構。它們與父數據行存放區索引具有相同的索引標識碼。如需詳細資訊，請參閱 sys.internal_partitions。
`columnstore_delete_buffer_state`	tinyint	`0` = `NOT_APPLICABLE` `1` = `OPEN` `2` = `DRAINING` `3` = `FLUSHING` `4` = `RETIRING` `5` = `READY` 適用於：SQL Server 2016 （13.x）和更新版本、Azure SQL 資料庫和 Azure SQL 受控執行個體
`columnstore_delete_buffer_state_desc`	nvarchar(60)	`NOT VALID` - 父索引不是數據行存放區索引。 `OPEN` - 刪除程式和掃描器會使用此專案。 `DRAINING` - 刪除程式正在清空，但掃描器仍然使用它。 `FLUSHING` - 緩衝區已關閉，且緩衝區中的數據列正在寫入刪除位圖。 `RETIRING` - 關閉刪除緩衝區中的數據列已寫入刪除位圖，但緩衝區尚未被截斷，因為掃描器仍在使用它。新的掃描器不需要使用淘汰的緩衝區，因為開啟的緩衝區就足夠了。 `READY` - 此刪除緩衝區已可供使用。適用於：SQL Server 2016 （13.x）和更新版本、Azure SQL 資料庫和 Azure SQL 受控執行個體
`version_record_count`	bigint	這是在此索引中維護的數據列版本記錄計數。這些數據列版本是由加速資料庫復原功能所維護。適用於：SQL Server 2019 （15.x）和更新版本，以及 Azure SQL 資料庫
`inrow_version_record_count`	bigint	數據列中保留的 ADR 版本記錄計數，以便快速擷取。適用於：SQL Server 2019 （15.x）和更新版本，以及 Azure SQL 資料庫
`inrow_diff_version_record_count`	bigint	以基底版本差異形式保留的 ADR 版本記錄計數。適用於：SQL Server 2019 （15.x）和更新版本，以及 Azure SQL 資料庫
`total_inrow_version_payload_size_in_bytes`	bigint	此索引之數據列內版本記錄的位元元組大小總計。適用於：SQL Server 2019 （15.x）和更新版本，以及 Azure SQL 資料庫
`offrow_regular_version_record_count`	bigint	保留於原始數據列外部的版本記錄計數。適用於：SQL Server 2019 （15.x）和更新版本，以及 Azure SQL 資料庫
`offrow_long_term_version_record_count`	bigint	考慮長期的版本記錄計數。適用於：SQL Server 2019 （15.x）和更新版本，以及 Azure SQL 資料庫

注意

備註

sys.dm_db_index_physical_stats 動態管理函數會取代 DBCC SHOWCONTIG 陳述式。

掃描模式

執行函式的模式會決定執行掃描的層級，以取得函式所使用的統計數據。模式指定為 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 非分葉層級傳回一個數據列。

使用系統函式來指定參數值

您可以使用 Transact-SQL 函式DB_ID和OBJECT_ID來指定database_id和object_id參數的值。不過，將無效的值傳遞給這些函式可能會導致非預期的結果。例如，如果找不到資料庫或物件名稱，因為它們不存在或拼字不正確，則這兩個函式都會傳回 NULL。函 sys.dm_db_index_physical_stats 式會 NULL 解譯為指定所有資料庫或所有物件的通配符值。

此外，會在OBJECT_ID呼叫函式之前sys.dm_db_index_physical_stats處理函式，因此會在目前資料庫的內容中進行評估，而不是database_id中指定的資料庫。此行為可能會導致函 OBJECT_ID 式傳回 NULL 值;或者，如果物件名稱同時存在於目前資料庫內容和指定的資料庫中，則會傳回錯誤訊息。下列範例示範這些非預期的結果。

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'AdventureWorks2022'), 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 AdventureWorks2022;
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

最佳做法

請務必確定當您使用 DB_ID 或 OBJECT_ID時，會傳回有效的標識符。例如，當您使用 OBJECT_ID時，請指定三部分的名稱，例如 OBJECT_ID(N'AdventureWorks2022.Person.Address')，或先測試函式所傳回的值，然後再在函式中使用 sys.dm_db_index_physical_stats 它們。下列範例 A 和 B 示範指定資料庫和物件識別碼的安全方式。

偵測片段

片段會透過針對數據表進行的數據修改（INSERT、 UPDATE和 DELETE 語句）程式進行，因此，針對數據表上定義的索引進行片段化。由於這些修改通常不會平均分散在數據表和索引的數據列之間，因此每個頁面的完整度可能會隨著時間而有所不同。對於掃描數據表部分或所有索引的查詢，這種片段可能會導致更多的頁面讀取，因而阻礙平行掃描數據。

索引或堆積的片段層級會顯示在數據行中 avg_fragmentation_in_percent 。對於堆積，值代表堆積的範圍片段。對於索引，值代表索引的邏輯片段。不同於 DBCC SHOWCONTIG，這兩種情況下的片段計算演算法都會考慮跨越多個檔案的記憶體，因此正確無誤。

邏輯片段

這是索引分葉頁面中順序不一的分頁百分比。失序頁面是指下一個實體頁面配置給索引的頁面，而不是目前分葉頁中「下一頁」指標所指向的頁面。

範圍片段

這是堆積分葉頁面中順序錯亂範圍的百分比。順序錯亂的範圍是包含堆積目前頁面的範圍不是包含上一頁範圍之後的下一個範圍。

的值 avg_fragmentation_in_percent 應該盡可能接近零，以達到最大效能。不過，0% 到 10% 的值可以接受。減少片段的所有方法，例如重建、重新組織或重新建立，都可以用來減少這些值。如需如何分析索引中片段程度的詳細資訊，請參閱優化索引維護以改善查詢效能並減少資源耗用量。

減少索引中的片段

當索引以片段影響查詢效能的方式分散時，有三個選項可減少片段：

卸除並重新建立叢集索引。

重新建立叢集索引會重新發佈數據，併產生完整的數據頁面。您可以在 CREATE INDEX 中使用 FILLFACTOR 選項來設定飽和度的層級。此方法的缺點是索引在卸除和重新建立週期期間脫機，而且作業不可部分完成。如果索引建立中斷，則不會重新建立索引。如需詳細資訊，請參閱 CREATE INDEX。
使用 ALTER INDEX REORGANIZE的 DBCC INDEXDEFRAG取代，以邏輯順序重新排序索引的分葉層級頁面。因為這是在線作業，因此當語句執行時，可以使用索引。作業也可以中斷，而不會遺失已完成的工作。此方法的缺點是，它不會像重新組織數據做為索引重建作業一樣好，而且不會更新統計數據。
使用 ALTER INDEX REBUILD的 DBCC DBREINDEX取代，在在線或離線重建索引。如需詳細資訊，請參閱 ALTER INDEX (Transact-SQL)。

單靠片段並不是重新組織或重建索引的足夠理由。片段的主要效果是，在索引掃描期間，它會減緩頁面預先讀取輸送量的速度。這會導致回應時間變慢。如果片段數據表或索引上的查詢工作負載未涉及掃描，因為工作負載主要是單一查閱，因此移除片段並無作用。

注意

如果索引在壓縮作業期間部分或完全移動，則執行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 可能會引入片段。因此，如果必須執行壓縮作業，您應該先執行壓縮作業，再移除片段。

減少堆積中的片段

若要減少堆積的範圍片段，請在數據表上建立叢集索引，然後卸除索引。這會在建立叢集索引時重新發佈數據。這也使它盡可能優化，考慮資料庫中可用空間的分佈。然後卸除叢集索引以重新建立堆積時，數據不會移動，而且會保持最佳位置。如需如何執行這些作業的詳細資訊，請參閱 CREATE INDEX 和 DROP INDEX。

警告

在數據表上建立和卸除叢集索引，重建該數據表上所有非叢集索引兩次。

壓縮大型對象數據

根據預設， ALTER INDEX REORGANIZE 語句會壓縮包含大型物件（LOB）數據的頁面。由於當空白時不會解除分配 LOB 頁面，因此壓縮此資料可改善刪除許多 LOB 資料或卸除 LOB 資料行時的磁碟空間使用量。

重新組織指定的叢集索引會壓縮叢集索引中包含的所有LOB數據行。重新組織非叢集索引會壓縮索引中非索引鍵（包含）數據行的所有LOB數據行。當在語句中指定時 ALL ，所有與指定數據表或檢視相關聯的索引都會重新組織。此外，會壓縮與叢集索引、基礎表或包含數據行的非叢集索引相關聯的所有LOB資料行。

評估磁碟空間使用量

數據 avg_page_space_used_in_percent 行表示頁面完整度。若要達到最佳磁碟空間使用，這個值應該接近 100%，索引沒有太多隨機插入。不過，索引具有許多隨機插入，而且頁面非常完整，頁面分割數目會增加。這會造成更多的片段。因此，為了減少頁面分割，值應該小於 100%。使用指定的選項重建索引 FILLFACTOR ，可變更頁面完整度，以符合索引上的查詢模式。如需填滿因數的詳細資訊，請參閱指定索引的填滿因數。此外， 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 或 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 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 傳回指定數據表的相關信息

下列範例會傳回數據表所有索引和數據分割的大小 Person.Address 和片段統計數據。掃描模式會設定為 LIMITED 以獲得最佳效能，並限制傳回的統計數據。執行此查詢至少 CONTROL 需要數據表的許可權 Person.Address 。

DECLARE @db_id SMALLINT;
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_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. 傳回堆積的相關信息

下列範例會傳回資料庫中堆積dbo.DatabaseLogAdventureWorks2022的所有統計數據。因為數據表包含 LOB 數據，除了針對儲存堆積數據頁之所傳IN_ROW_ALLOCATION_UNIT回的數據列之外，配置單位也會傳回LOB_DATA數據列。執行此查詢至少 CONTROL 需要數據表的許可權 dbo.DatabaseLog 。

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.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. 傳回所有資料庫的資訊

下列範例會指定所有參數的通配符 NULL ，以傳回SQL Server 實例內所有數據表和索引的所有統計數據。執行此查詢需要 VIEW SERVER STATE 許可權。

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

D. 在腳本中使用sys.dm_db_index_physical_stats來重建或重新組織索引

下列範例會自動重新組織或重建資料庫中平均分散超過 10% 的所有分割區。執行此查詢需要 VIEW DATABASE STATE 許可權。這個範例會 DB_ID 指定為第一個參數，而不指定資料庫名稱。

-- 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
    INNER 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. 使用sys.dm_db_index_physical_stats來顯示頁面壓縮的頁面數目

下列範例示範如何顯示和比較頁面總數與已壓縮數據列和頁面的頁面。此資訊可用來判斷壓縮為索引或數據表提供的好處。

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
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. 在 SAMPLED 模式中使用sys.dm_db_index_physical_stats

下列範例示範模式如何 SAMPLED 傳回與模式結果不同的 DETAILED 近似值。

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

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. 查詢索引片段的 Service Broker 佇列

適用於：SQL Server 2016 (13.x) 和更新版本

下列範例示範如何查詢伺服器訊息代理程式佇列以進行片段化。

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

分享方式：

sys.dm_db_index_physical_stats (Transact-SQL)

語法

引數

database_id |NULL |0 |違約

object_id |NULL |0 |違約

index_id | 0 |NULL |-1 |違約

partition_number |NULL |0 |違約

mode |NULL |違約

傳回的資料表

備註

掃描模式

使用系統函式來指定參數值

最佳做法

偵測片段

邏輯片段

範圍片段

減少索引中的片段

減少堆積中的片段

壓縮大型對象數據

評估磁碟空間使用量

評估索引片段

限制

權限

範例

A. 傳回指定數據表的相關信息

B. 傳回堆積的相關信息

C. 傳回所有資料庫的資訊

D. 在腳本中使用sys.dm_db_index_physical_stats來重建或重新組織索引

E. 使用sys.dm_db_index_physical_stats來顯示頁面壓縮的頁面數目

F. 在 SAMPLED 模式中使用sys.dm_db_index_physical_stats

G. 查詢索引片段的 Service Broker 佇列

意見反映

更多資源

分享方式：

sys.dm_db_index_physical_stats (Transact-SQL)

語法

引數

database_id |NULL |0 |違約

object_id |NULL |0 |違約

index_id | 0 |NULL |-1 |違約

partition_number |NULL |0 |違約

mode |NULL |違約

傳回的資料表

備註

掃描模式

使用系統函式來指定參數值

最佳做法

偵測片段

邏輯片段

範圍片段

減少索引中的片段

減少堆積中的片段

壓縮大型對象數據

評估磁碟空間使用量

評估索引片段

限制

權限

範例

A. 傳回指定數據表的相關信息

B. 傳回堆積的相關信息

C. 傳回所有資料庫的資訊

D. 在腳本中使用sys.dm_db_index_physical_stats來重建或重新組織索引

E. 使用sys.dm_db_index_physical_stats來顯示頁面壓縮的頁面數目

F. 在 SAMPLED 模式中使用sys.dm_db_index_physical_stats

G. 查詢索引片段的 Service Broker 佇列

相關內容

意見反映

更多資源