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 10.3 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.)
DECLARE @db_id INT;
DECLARE @tab_id INT;
DECLARE @ind_id INT;
SET @db_id = DB_ID('sample');
SET @tab_id = OBJECT_ID('employee');
SELECT avg_fragmentation_in_percent, avg_page_space_used_in_percent
(@db_id, @tab_id, NULL, NULL, NULL)
As you can see from Example 10.3, 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, 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.
Editing Index Information
After you have viewed the index fragmentation information, as discussed in the previous section, you can use the following system features to edit that information and to edit other index information:
- sys.indexes catalog view
- sys.index_columns catalog view
- sp_helpindex system procedure
- OBJECTPROPERTY property function
- SQL Server Management Studio
- sys.dm_db_index_usage_stats DMV
- sys.dm_db_missing_index_details DMV
The sys.indexes catalog view contains a row for each index and a row for each table without a clustered index. The most important columns of this view are object_id, name, and index_id. object_id is the name of the database object to which the index belongs, while name and index_id are the name and the ID of that index, respectively.
The sys.index_columns catalog view contains a row per column that is part of an index or a heap. This information can be used together with the information from the sys.indexes catalog view to obtain further properties of a specific index.
sp_helpindex displays all indices on a table as well as column statistics. The syntax of this procedure is
sp_helpindex [@db_object = ] 'name',
where db_object is the name of a table.
The OBJECTPROPERTY property function has two properties in relation to indices: IsIndexed and IsIndexable. The former informs you whether a table or view has an index, while the latter specifies whether a table or view can be indexed.
To edit information about an existing index using SQL Server Management Studio, choose the database in the Databases folder and expand Tables. Expand the Indexes folder. The list of all existing indices for that table is shown. After you double-click one of the indices, the system shows you the Index Properties dialog box with all properties of that index. (You can also use Management Studio to create a new index or drop an existing one.)
The sys.dm_db_index_usage_stats view returns counts of different types of index operations and the time each type of operation was last performed. Every individual seek, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this DMV. That way you can get general information about how often an index is used to determine which indices are used more heavily than the others.
The sys.dm_db_missing_index_details view returns detailed information about missing indices. The most important columns of this DMV are index_handle and object_id. The former identifies a particular missing index, while the latter specifies the table where the index is missing.