Page History
...
- Properties. Displays general table details, such as, number of columns, file group, and whether or not the table uses a clustered index.
- In MS-SQL. Displays the Using CPU and I/O Wait counters of the selected table, over the 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 table, over the selected time period, and specifies whether the space is populated by an index or data.
The values may be incorrect due to out-of-date space usage information. To update the values, use theDBCC UPDATEUSAGEcommand to recalculate the space usage fields. For more information, see SQL Server Books Online. - Schema
...
- Changes. Displays an overtime graph that indicates whether changes were made to the selected table and a breakdown of its indexes or columns into the following groups:
...
- Table Change in
...
- Property. Indicates any change made to one of the table's properties, such as if the table was renamed.
...
- Table Change in
...
- Structure. Indicates any change made to the table's structure, such as whether a new column was added to the table.
...
- Index Change in
...
- Property. Indicates any change made to one of the index's properties, such as whether the index was changed from clustered to non-clustered.
...
- Index Change in
...
- Structure. Indicates any change made to the index's structure, such as whether a new key was added to the index.
...
- Index Created/
...
- Dropped. Indicates any addition or deletion of indexes.
...
- Rows. Displays the number of rows in the selected table, over the selected time period, as collected by the Collect Space Utilization process.
About viewing data storage and fragmentation information in the Show Contig view
The Show Contig view displays data storage and fragmentation information for the selected table. The following information is displayed:
- 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, useDBCC 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.
...
| Info |
|---|
By default, not all dbcc fields are displayed in the Show Contig view. |
About viewing changes made to the table and index levels in the Change Log Summary
The Change Log Summary view displays changes made to the table and index levels vs. resource consumption, over a selected time period, to enable you to locate schema changes that caused performance problems. The following information is displayed:
- Change Log
...
- Summary.
- Sample Date. Indicates the estimated last change date of the table (and/or its sub-objects). This value reflects the date that the change was first identified by the Precise for SQL Server Schema Changes Process, not the date the object was changed.
Object Type—indicates Type. Indicates whether the object is a table or index. Object—displays the table or index name.
Change Type—Indicates Type. Indicates the type of change detected. The following change types are reported:
■ Created—indicates Created. Indicates that the index was created
■ Dropped—indicates Dropped. Indicates that the index was dropped
■ Change in Structure Table—indicates Table. Indicates any change that was made to the table's column(s) and the creation or deletion of the table's trigger(s).
■ Index—indicates Index. Indicates any change that was made to the index keys of the index or to the index depth.
■ Change in Properties Table—indicates Table. Indicates any change that was made to one of the following table's properties: Name, Owner or File Group.
■ Index—indicates Index. Indicates any change that was made to one of the following index's properties: File Group, Fill Factor, Is Auto Statistics, Is Clustered Index, Is Pad Index, Is Page Lock Disallowed and Is Row Lock Disallowed.
- In MS-
...
- SQL. Displays resource consumption of the selected table, over a selected time period, based on its contribution to overall performance, when accessed by the execution plan.
- Schema
...
- Changes. Displays an overtime graph that indicates whether changes were made to the selected table and a breakdown of its indexes or columns into the following groups:
■ Table Change in Property—indicates any change made to one of the table's properties, such as if the table was renamed.
...