During the life cycle of an index, it can become fragmented, meaning the storage of data in its pages is done inefficiently. There are two forms of index fragmentation: internal and external. Internal fragmentation specifies the amount of data, which is stored within each page. External fragmentation occurs when the logical order of the pages is wrong.
To get information concerning internal index fragmentation, you use the dynamic management view (DMV) called sys.dm_db_index_physical_stats. This DMV returns size and fragmentation information for the data and indices of the specified table. For each index, one row is returned for each level of the B+-tree. Using this DMV, you can obtain information about the degree of fragmentation of rows on data pages. You can use this information to decide whether reorganization of the data is necessary.
Example 1 shows how you can use the sys.dm_db_index_physical_stats view. (You need to drop all existing indices on the works_on table before you start the batch. Example 10.4 shows the use of the DROP INDEX statement.)
EXAMPLE 1
As you can see from Example 1, the sys.dm_db_index_physical_stats view has five parameters. The first three specify the IDs of the current database, table, and index, respectively. The fourth specifies the partition ID (see Chapter 25), and the last one specifies the scan level that is used to obtain statistics. (You can always use NULL to specify the default value of the particular parameter.)
This view has several columns, of which avg_fragmentation_in_percent and avg_page_space_used_in_percent are the most important. The former specifies the average fragmentation in percent, while the latter defines the percentage of the used space.
Leave a Reply