Eksportér (0) Udskriv
Udvid alt
Expand Minimize
EN
Dette indhold er ikke tilgængeligt på dit sprog, men her er den engelske version.

sys.dm_db_index_physical_stats

Updated: April 2, 2011

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see sys.dm_db_index_physical_stats.

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. Does not return information about xVelocity memory optimized columnstore indexes.

Topic_Link Transact-SQL Syntax Conventions

sys.dm_db_index_physical_stats( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

database_id | NULL | 0 | DEFAULT
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 or greater.

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.

 

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.

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.

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

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.

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, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

With LIMITED mode, compressed_page_count is NULL because the 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. Results in SAMPLED mode should be regarded as approximate. 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.

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

noteNote
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. For information about how to perform these operations, see CREATE INDEX and DROP INDEX.

CautionCaution
Creating and dropping a clustered index on a table, rebuilds all non-clustered indexes on that table twice.

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.

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.

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

Fællesskabsindhold

Tilføj
Vis:
© 2014 Microsoft