Page History

...

• Minimum Record Size. Indicates the minimum record size in the data pages.
• Maximum Record Size. Indicates the maximum record size in the data pages.
• Average Record Size. Indicates the average record size in the data pages.
• 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, since there was no free space in the original page.
  Accessing forwarded records adversely affects performance because additional I/O is performed. 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 a better performance. To reorganize an index, use DBCC DBREINDEX. If the index is a clustered index, a Reindex action will reorganize the data pages.
• Pages. Displays the number of data pages.
• Extents. Displays the number of extents used to store the data pages.
• Extent Switches. The number of times SQL Server moved from one extent to another while scanning the data 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 a table. The higher the value, the better. The scan density is not valid, if the table spans multiple files.
• Best Count. 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. Number of times the SQL Server moved from one extent to another while scanning the data pages (including the first extent access).
• Average Free Bytes per Page. Average free bytes per page in the data pages.
• Average Page Density (%). The average page density is a percentage indicating how full the data 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, scanning the table involves more read operations than if no free space were available on the pages. However, keeping 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 the new page is not usually contiguous to the page being split.
• Logical Fragmentation (%). The logical fragmentation indicates how well ordered the data 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. The logical fragmentation is computed by dividing the number of out-of-order pages by the number of the data pages. The lower the value, the less fragmentation there is. This is relevant only when there is a clustered index on the table, because ordered pages are important.
• Extent Fragmentation (%). The extent fragmentation indicates how well the extents, which contain the data 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 value, the less fragmentation there is.
  This number is not relevant to heaps, because physical order has no importance in the data pages.

...

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

...