sys.dm_db_index_physical_stats (Transact-SQL)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

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

sys.dm_db_index_physical_stats 不會傳回記憶體優化資料行存放區索引的相關資訊。 如需記憶體優化索引使用的相關資訊，請參閱 sys.dm_db_xtp_index_stats （Transact-SQL） 。

如果您在裝載可用性群組 可讀取次要複 本的伺服器實例上查詢 sys.dm_db_index_physical_stats ，可能會遇到 REDO 封鎖問題。 這是因為此動態管理檢視會 IS 取得指定之使用者資料表或檢視的鎖定，而該檢視可封鎖該使用者資料表或檢視上鎖定之執行緒 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 。 有效的輸入是資料庫、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 |預設

索引開啟之資料表或檢視表的物件識別碼。 object_id為 int 。

有效的輸入是資料表和檢視表、Null、0 或 DEFAULT 的識別碼。 預設值是 0。 Null、0 和 DEFAULT 在此內容中是相等的值。 自 SQL Server 2016 （13.x） 起，有效的輸入也包含 Service Broker 佇列名稱或佇列內部資料表名稱。 套用預設參數時（也就是所有物件、所有索引等），所有佇列的片段資訊都會包含在結果集中。

指定 Null 以傳回指定資料庫中所有資料表和檢視的資訊。 如果您為 object_id 指定 Null，也必須為 index_id 和 partition_number 指定 Null。

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

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

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

mode |Null |預設

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

傳回的資料表

備註

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

掃描模式

執行函式的模式會決定執行掃描的層級，以取得函式所使用的統計資料。 模式 指定為 LIMITED、SAMPLED 或 DETAILED。 函式會周遊組成資料表或索引之指定分割區之配置單位的頁面鏈結。 sys.dm_db_index_physical_stats 不論其執行模式為何，只需要意圖共用 （IS） 資料表鎖定。

LIMITED 模式是最快的模式，會掃描最少的頁面數目。 針對索引，只會掃描 B 型樹狀結構的父層級頁面（也就是分葉層級上方的頁面）。 針對堆積，會檢查相關聯的 PFS 和 IAM 頁面，並以 LIMITED 模式掃描堆積的資料頁。

若為 LIMITED 模式，則為 Null， compressed_page_count 因為 Database Engine 只會掃描 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 FULLTEXT CATALOG (TRANSACT-SQL)。

• 使用 ALTER INDEX REORGANIZE，取代 DBCC INDEXDEFRAG 為 ，以邏輯順序重新排序索引的分葉層級頁面。 因為這是線上作業，因此當語句執行時，可以使用索引。 作業也可以中斷，而不會遺失已完成的工作。 此方法的缺點是，它不會像重新組織資料做為索引重建作業一樣好，而且不會更新統計資料。

• 使用 ALTER INDEX REBUILD，取代 的 DBCC DBREINDEX ，在線上或離線重建索引。 如需詳細資訊，請參閱 ALTER INDEX (Transact-SQL)。

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

減少堆積中的片段

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

範例

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

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

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. 傳回堆積的相關資訊

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

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 指定為第一個參數，而不指定資料庫名稱。 如果目前的資料庫相容性層級為 80 或更低，則會產生錯誤。 若要解決錯誤，請以有效的資料庫名稱取代 DB_ID() 。 如需資料庫相容性層級的詳細資訊，請參閱 ALTER DATABASE 相容性層級（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
    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);

另請參閱

• 動態管理檢視和函數 (Transact-SQL)
• 索引相關的動態管理檢視和函式 （Transact-SQL）
• sys.dm_db_index_operational_stats (Transact-SQL)
• sys.dm_db_index_usage_stats (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• sys.allocation_units (Transact-SQL)
• 系統檢視 （Transact-SQL）