sys.dm_db_index_physical_stats (Transact-SQL)

Returns size and fragmentation information for the data and indexes of the specified table or view. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. For information about allocation units and partitions, see Tables and Index Data Structures Architecture.

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 | DEFAULT
  Is the ID of the database. database_id is smallint. Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. The default is 0. NULL, 0, and DEFAULT are equivalent values in this context.

  Specify NULL to return information for all databases in the instance of SQL Server. If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.

  The built-in function DB_ID can be specified. When using DB_ID without specifying a database name, the compatibility level of the current database must be 90.

• object_id | NULL | 0 | DEFAULT
  Is the object ID of the table or view the index is on. object_id is int.

  Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. The default is 0. NULL, 0, and DEFAULT are equivalent values in this context.

  Specify NULL to return information for all tables and views in the specified database. If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.

• index_id | 0 | NULL | -1 | DEFAULT
  Is the ID of the index. index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. The default is -1. NULL, -1, and DEFAULT are equivalent values in this context.

  Specify NULL to return information for all indexes for a base table or view. If you specify NULL for index_id, you must also specify NULL for partition_number.

• partition_number | NULL | 0 | DEFAULT
  Is the partition number in the object. partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. The default is 0. NULL, 0, and DEFAULT are equivalent values in this context.

  Specify NULL to return information for all partitions of the owning object.

  partition_number is 1-based. A nonpartitioned index or heap has partition_number set to 1.

• mode | NULL | DEFAULT
  Is the name of the mode. mode specifies the scan level that is used to obtain statistics. mode is sysname. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED.

Table Returned

Column name	Data type	Description
database_id	smallint	Database ID of the table or view.
object_id	int	Object ID of the table or view that the index is on.
index_id	int	Index ID of an index. 0 = Heap.
partition_number	int	1-based partition number within the owning object; a table, view, or index. 1 = Nonpartitioned index or heap.
index_type_desc	nvarchar(60)	Description of the index type: HEAP CLUSTERED INDEX NONCLUSTERED INDEX PRIMARY XML INDEX SPATIAL INDEX XML INDEX
alloc_unit_type_desc	nvarchar(60)	Description of the allocation unit type: IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA The 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. For more information, see Data Types (Transact-SQL). 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. For more information, see Row-Overflow Data Exceeding 8 KB.
index_depth	tinyint	Number of index levels. 1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.
index_level	tinyint	Current level of the index. 0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. Greater than 0 for nonleaf index levels. index_level will be the highest at the root level of an index. The nonleaf levels of indexes are only processed when mode = DETAILED.
avg_fragmentation_in_percent	float	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. 0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.
fragment_count	bigint	Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. For more information about fragments, see Remarks. NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.
avg_fragment_size_in_pages	float	Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit. NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.
page_count	bigint	Total number of index or data pages. For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the total number of data pages in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.
avg_page_space_used_in_percent	float	Average percentage of available data storage space used in all pages. For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the average of all data pages in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit. NULL when mode = LIMITED.
record_count	bigint	Total number of records. For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. 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. Also, most large LOB rows are split into multiple records in LOB_DATA storage. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit. NULL when mode = LIMITED.
ghost_record_count	bigint	Number of ghost records ready for removal by the ghost cleanup task in the allocation unit. 0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit. NULL when mode = LIMITED.
version_ghost_record_count	bigint	Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit. 0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit. NULL when mode = LIMITED.
min_record_size_in_bytes	int	Minimum record size in bytes. For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the minimum record size in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit. NULL when mode = LIMITED.
max_record_size_in_bytes	int	Maximum record size in bytes. For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the maximum record size in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit. NULL when mode = LIMITED.
avg_record_size_in_bytes	float	Average record size in bytes. For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the average record size in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit. NULL when mode = LIMITED.
forwarded_record_count	bigint	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.) NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap. NULL for heaps when mode = LIMITED.
compressed_page_count	bigint	The number of compressed pages. For heaps, newly allocated pages are not PAGE compressed. A heap is PAGE compressed under two special conditions: when data is bulk imported or when a heap is rebuilt. Typical DML operations that cause page allocations will not be PAGE compressed. Rebuild a heap when the compressed_page_count value grows larger than the threshold you want. For tables that have a clustered index, the compressed_page_count value indicates the effectiveness of PAGE compression.

Remarks

The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement. This dynamic management function does not accept correlated parameters from CROSS APPLY and OUTER APPLY.

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. 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 requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in. For more information about locking, see Lock Modes.

The LIMITED mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, only the associated PFS and IAM pages are examined; the data pages of the heap are not scanned. In SQL Server 2005, all pages of a heap are scanned in LIMITED mode.

With LIMITED mode, compressed_page_count is NULL because the Database Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. 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..The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.

The DETAILED mode scans all pages and returns all statistics.

The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. 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

You can use the Transact-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. 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. The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.

Additionally, 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. 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 AdventureWorks;
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

Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. For example, when you use OBJECT_ID, specify a three-part name such as OBJECT_ID(N'AdventureWorks.Person.Address'), or test the value returned by the functions before you use them in the sys.dm_db_index_physical_stats function. Examples A and B that follow demonstrate a safe way to specify database and object IDs.

Detecting Fragmentation

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.

The algorithm for calculating fragmentation is more precise in SQL Server 2008 than in SQL Server 2000. As a result, the fragmentation values will appear higher. For example, in SQL Server 2000, a table is not considered fragmented if it has page 11 and page 13 in the same extent but not page 12. However, to access these two pages would require two physical I/O operations, so this is counted as fragmentation in SQL Server 2008.

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. 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.

The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. 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. For more information about how to analyze the degree of fragmentation in an index, see Reorganizing and Rebuilding 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. 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. For more information, see CREATE INDEX (Transact-SQL).

• 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.

• Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. 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. For more information, see this Microsoft Web site.

Note

Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is moved partly or completely during the shrink operation. Therefore, if a shrink operation must be performed, you should not to do it after 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. For information about how to perform these operations, see CREATE INDEX and DROP INDEX.

Compacting Large Object Data

By default, the ALTER INDEX REORGANIZE statement compacts pages that contain large object (LOB) data. 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.

Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. When ALL is specified in the statement, all indexes that are associated with the specified table or view are reorganized. 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

The avg_page_space_used_in_percent column indicates page fullness. 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. Therefore, in order to reduce page splits, the value should be less than 100 percent. 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 Fill Factor. Also, ALTER INDEX REORGANIZE will compact an index by trying to fill pages to the FILLFACTOR that was last specified. This increases the value in avg_space_used_in_percent. 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. Larger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. 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.

Permissions

Requires the following permissions:

• CONTROL permission on the specified object within the database.

• 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 permission to return information about all databases, by using the database wildcard @database\_id = NULL.

Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Also, when the database wildcard @database\_id=NULL is specified, the database is omitted.

For more information, see Dynamic Management Views and Functions (Transact-SQL).

Examples

A. Returning information about a specified table

The following example returns size and fragmentation statistics for all indexes and partitions of the Person.Address table in the AdventureWorks database. The scan mode is set to 'LIMITED' for best performance and to limit the statistics that are returned. 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'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_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. Returning information about a heap

The following example returns all statistics for the heap dbo.DatabaseLog in the AdventureWorks database. 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. 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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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. Returning information for all databases

The following example returns all statistics for all tables and indexes within the instance of SQL Server by specifying the wildcard NULL for all parameters. Executing this query requires the VIEW SERVER STATE permission.

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

D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Executing this query requires the VIEW DATABASE STATE permission. This example specifies DB_ID as the first parameter without specifying a database name. An error will be generated if the current database has a compatibility level of 80 or lower. To resolve the error, replace DB_ID() with a valid database name. For more information about database compatibility levels, see sp_dbcmptlevel (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

See Also

Reference

Dynamic Management Views and Functions (Transact-SQL)

Index Related Dynamic Management Views and Functions (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)

System Views (Transact-SQL)

Concepts

Table and Index Organization

Change History

Updated content
Explained how LIMITED, SAMPLED, and DETAILED modes affect the value of compressed_page_count.