Security
Permissions
Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.
Check index fragmentation using SQL Server Management Studio
To check the fragmentation of an index
In Object Explorer, Expand the database that contains the table on which you want to check an index’s fragmentation.
Expand the Tables folder.
Expand the table on which you want to check an index’s fragmentation.
Expand the Indexes folder.
Right-click the index of which you want to check the fragmentation and select Properties.
Under Select a page, select Fragmentation.
The following information is available on the Fragmentation page:
Page fullness
Indicates average fullness of the index pages, as a percentage. 100% means the index pages are completely full. 50% means that, on average, each index page is half full.Total fragmentation
The logical fragmentation percentage. This indicates the number of pages in an index that are not stored in order.Average row size
The average size of a leaf level row.Depth
The number of levels in the index, including the leaf level.Forwarded records
The 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.)Ghost rows
The number of rows that are marked as deleted but not yet removed. These rows will be removed by a clean-up thread, when the server is not busy. This value does not include rows that are being retained due to an outstanding snapshot isolation transaction.Index type
The type of index. Possible values are Clustered index, Nonclustered index, and Primary XML. Tables can also be stored as a heap (without indexes), but then this Index Properties page cannot be opened.Leaf-level rows
The number of leaf level rows.Maximum row size
The maximum leaf-level row size.Minimum row size
The minimum leaf-level row size.Pages
The total number of data pages.Partition ID
The partition ID of the b-tree containing the index.Version ghost rows
The number of ghost records that are being retained due to an outstanding snapshot isolation transaction.
Remove fragmentation using SQL Server Management Studio
To reorganize or rebuild an index
In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
Expand the Tables folder.
Expand the table on which you want to reorganize an index.
Expand the Indexes folder.
Right-click the index you want to reorganize and select Reorganize.
In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.
Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
Click OK.
To reorganize all indexes in a table
In Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes.
Expand the Tables folder.
Expand the table on which you want to reorganize the indexes.
Right-click the Indexes folder and select Reorganize All.
In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.
Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
Click OK.
To rebuild an index
In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
Expand the Tables folder.
Expand the table on which you want to reorganize an index.
Expand the Indexes folder.
Right-click the index you want to reorganize and select Rebuild.
In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.
Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
Click OK.