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.
| Anchor | ||||
|---|---|---|---|---|
|
The Show Contig view displays data storage and fragmentation information for the selected table. The following information is displayed:
...
- Name
- File Group
- Exec is ANSI null on
- Table has Cluster Index
- Insert Trigger Count
- Owner
- No. of Columns
- Table has Index
- Delete Trigger Count
- Update Trigger Count
- Partition Schema
- Partition Function
| Anchor | ||||
|---|---|---|---|---|
|
The Usage Report view displays usage information and access methods based on statements stored in the PMDB. The following information is displayed:
...
- 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 UPDATEUSAGEcommand 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.
| Anchor | ||||
|---|---|---|---|---|
|
The Show Contig view displays data storage and fragmentation information for the selected index. The following information is displayed:
...
- File Group
- Fill Factor
- Is Pad Index
- Index Depth
- Is Page Lock Disallowed
- No of Keys
- Is Clustered Index
- Is Unique
- Is Auto Statistics
- Is Row Lock Disallowed
- Position
- Order by
- Partition Schema
- Partition Function
| Anchor | ||||
|---|---|---|---|---|
|
The Usage Report view displays usage information and access methods based on statements stored in the PMDB. The following information is displayed:
...
The Statistics view displays distribution statistics for the selected statistic. see See About viewing distribution statistics for a selected index.
...
SQL Server allows you track the schema changes and compare them with the resource consumption of the entire database or a table. This allows you to detect changes that affect the resource consumption of the database. The Object Changes view of the Database entity allows you to check the database objects that where changed, dropped or created in reference to the resource consumption of the entire database over time. A summary of the schema changes is displayed. Drilling down to the specific object provides detailed information on the schema changes.
See “About About the Database entity” on page 60entity.
To analyze how schema changes affect the resource consumption of various entities
...
- In the Time Frame list, choose the period of time you want to analyze.
- In the Instance list, choose the item you want to analyze.
- On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
- On the Association controls in the Association area, click Tables or Indexes. Drill down to a specific table or index entity (depending upon your selection).
- On the View controls in the Main area, click Show Contig and analyze the fragmentation information displayed for the selected table or index. Determine whether the table or index is heavily fragmented and needs to be reorganized.
See “About About viewing data storage and fragmentation information in the Show Contig view” on page 114 and “About view and About viewing data storage and fragmentation information of a selected index in the Show Contig view” on page 118view.
| Anchor | ||||
|---|---|---|---|---|
|
...
- In the Time Frame list, choose the period of time you want to analyze.
- In the Instance list, choose the item you want to analyze.
- On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
- On the Association controls in the Association area, click Tables or Indexes. Drill down to a specific table or index entity (depending upon your selection).
- On the View controls in the Main area, click Usage Report to observe information on the access methods used on the table or index.
See “About About viewing usage information and access methods in the Usage Report” on page 116 and “About Report and About viewing a usage report” on page 121report.
| Anchor | ||||
|---|---|---|---|---|
|
Distribution statistics of an index can help you determine whether or not the index is useful to the query optimizer. The information displayed in the Statistics view of the Index entity indicates the selectivity of an index; the more selective an index is, the more useful it is, since this means more rows can be eliminated from consideration.
See “About About viewing distribution statistics for a selected index” on page 120index.
To examine the distribution statistics of indexes
...