Page History
...
- Properties. Displays general details on the index such as number of keys, fill factor, and whether the index is clustered or unique.
- In MS-SQL. Displays Using CPU and I/O Wait of the selected index over a selected time period, based on its contribution to overall performance when accessed by the execution plan.
Space Allocation. Displays the amount of free space and used space (in MB) in the selected index, over the selected time period.
...
Info The values may be incorrect due to out-of-date space usage information. To update the values, use the DBCC UPDATEUSAGE command to recalculate the space usage fields. For more information, see SQL Server Books Online.
- Schema
...
- Changes. Displays an overtime graph that indicates whether there where index changes, broken down into the following groups:
...
- Change in Property. Indicates any change made to the index properties, such as if the index was changed from clustered to non-clustered.
...
- Change in Structure. Indicates any change made to the index structure, such as if a new key was added to the index.
...
- Rows. Displays the number of rows in the selected index, over the selected time period, as collected by the Collect Space Utilization process.
About viewing data storage and fragmentation information of a selected index in the Show Contig view
The Show Contig view displays data storage and fragmentation information for the selected index. The following information is displayed:
- Minimum Record
...
- Size. Displays the minimum record size of the pages in the leaf level of the index.
- Maximum Record
...
- Size. Displays the maximum record size of the pages in the leaf level of the index.
- Average Record
...
- Size. Displays the average record size of the pages in the leaf level of the index.
- Number of Forwarded
...
- Records
...
- . When there is not enough free space to write an updated record in its page, SQL Server allocates the updated record to another page and instructs the old record to point to the new location.
The Number of forwarded records is the number of records allocated to another page, because there was no free space available in the original page.
Every access to a forwarded record requires an additional step than access to a regular record, which affects performance. Check the number of forwarded records in your table or index in relation to the total number of records. If the ratio is high, consider reorganizing the table or index to achieve better performance. UseDBCC DBREINDEXto reorganize an index. If the index is a clustered index, reindexing will reorganize the data pages.
...
- Pages. Displays the number of pages in the leaf level of the index.
- Number of
...
- Extents. Number of extents used to store the pages in the leaf level of the index.
- Extent
...
- Switches. Displays the number of times SQL server moved from one extent to another while scanning the leaf level pages (not including the first extent access).
- Scan
...
- Density. Indicates the ratio between the optimum number of extent switches and the actual number of extent switches. This value determines the general level of fragmentation in an index. The higher the value, the less fragmentation there is in the index. The scan density is not valid if the table spans multiple files.
- Best
...
- Count. Displays the optimum number of extent switches. This number can be achieved, if the extents and pages are perfectly linked, and there is no fragmentation.
- Actual
...
- Count. Displays the number of times SQL Server moved from one extent to another while scanning the index pages (including the first extent access).
- Average Free Bytes per Page
...
- . Displays the average free bytes per page in the leaf level pages.
- Average Page Density (%)
...
- . The average page density is a percentage indicating how full the index pages are.
Space that cannot be utilized due to row size constraints is counted as used. The higher the percentage, the better.
When space is available within the index pages, that is, when the indexes are not making the most efficient use of space, this means that scanning the table involves more read operations than if no free space were available on the pages. However, maintaining free space on the pages is sometimes desirable to accommodate future expansion in the storage of the table's data and to reduce the potential for page splits. You can request to keep free space available by specifying a low fill factor value, when you create an index. For example, a fill factor of "10" means that the pages will be 10% full while creating the index. Having room on a page means that there is space to insert more rows without having to split a page. Splitting is a relatively expensive operation and can lead to a break of contiguity between pages, because usually the new page is not contiguous to the page being split. - Logical
...
- Fragmentation. Indicates how well ordered the leaf pages are inside an extent and between extents. Well ordered means that the number of the current page scanned is higher than the previous one. Logical fragmentation is computed by dividing the number of out-of-order pages by the number of pages in the table. The lower the value, the less fragmentation there is in the index. This is relevant only when there is a clustered index on the table because then there is significance to ordered pages.
- Extent Fragmentation (%)
...
- . The extent fragmentation indicates how well the extents, which contain the leaf pages, are physically contiguous on the disk.
The page number of its first page identifies an extent. If the extents 8, 16, 24, or 32 make up an index, then we can see that the extents are contiguous on the disks, which means there are no gaps. On the other hand, if the extents 8, 24, 32, and 64 make up an index, then we can see that we have a gap. The extent fragmentation is computed by dividing the number of gaps by the number of extents, so in this example, the extent fragmentation is 1/4, or 25 percent.
The lower the number, the less fragmentation there is.
This number is not relevant to heaps, because physical order has no importance in the data pages
...
- .
About viewing distribution statistics for a selected index
...