Page History
...
- 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.Info By default, not all
dbccfields are displayed ion the Show Contig view.
About viewing distribution statistics for a selected index
The Statistics view displays distribution statistics for the selected index.
General
Displays general information on the distribution statistics, as follows:
- Statistics
...
- Updated. Indicates the last time the statistics were updated.
- Row
...
- Sampled. Indicates the number of rows sampled for the statistics calculation.
- Average Key
...
- Length. Indicates the average length of all the keys in the index.
...
- Steps. The number of steps that were sampled and used in the statistics calculation. A "step" is a range of key values (the first key in the index) that were sampled.
The following values are recorded for each step:
...
- High value in the range
...
- Density = 1 / (1+No. of distinct values in the range)
...
- No. of rows in the range (not including the High value)
...
- No. of rows equal to the "High value"
...
- No. of distinct values in the range (not including the "High value")
...
- Avg. No. of rows per distinct value ("No. Of rows in range" / "No. of distinct values in the range")
- Rows
...
- Sampled. The percentage of rows sampled for the statistics calculation.
About Density
Indicates how selective an index is, as follows:Density This
- Density. This indicator provides a measure of how selective the index is. The more selective an index is, the more useful it is, since this means that more rows can be eliminated from consideration. For example, a unique index is the most selective index since each index entry can point to only one row.
Density = 1/No. of unique values. Density values can be between "0" and "1". The lower the Density value, the more selective the index. - Average
...
- Length. Indicates the average length of the specified set of keys.
...
- Columns. The left-based subset of key columns in the index (including the first column alone). If there is a clustered index on the table, its keys are considered part of the key in every non-clustered index row, because non-clustered index rows point to the clustered index rows. For example, if there is a non-clustered index with the columns, "CustomerID" and "OrderDate". The table also contains a clustered index, "OrderID".
The columns will be:
...
- CustomerID
...
- CustomerID, OrderDate
...
- CustomerID, OrderDate, OrderID
About Distribution
Displays additional distribution statistics for the selected index, as follows:
- High
...
- Value. Each row in the Distribution table represents information about a range of key values (the first key in the index). This range is called a "Step". "High value" is the upper value in the range.
- Rows in
...
- Range. Indicates the number of rows in the sample that have the specified key range values, not including the high value in the range.
- Rows Equal to High
...
- Value. Indicates the number of rows from the sample that have the same value as the specified High value
- Distinct Values in
...
- Range. Indicates the number of distinct values inside the range not including the High value.
- Rows per Distinct Values in Range (Avg)
...
- . The average number of rows, per distinct value, in the specified range, not including the High value in the range. This value is computed by dividing the number of rows in the range by the number of distinct values in the range (the number of distinct values in the range always being greater than zero).
...
- Density. Step Density means the uniqueness of the data values. This information provides a measure of how selective the index is. The more selective an index is, the more useful it is, because this means more rows can be eliminated from consideration. A unique index, for example, is the most selective index, where each index entry can point to only one row.
Step Density = 1/(1+No. of distinct values in the range) In this case, we calculate the density of the current step.
Step Density values can be between "0" and "1". The lower the Step Density value, the more unique the data values.
About viewing schema changes for a selected index and index key
The Schema Changes view shows a table that summarizes the schema changes made on the selected index and index keys. For example, this would include whether the first key of an index was dropped, making the index unusable in statements.
The following index information is monitored by the schema change process:■ File
- 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
About viewing a usage report
The Usage Report view displays usage information and access methods based on statements stored in the PMDB. The following information is displayed:
- Report was run with the following parameters
...
- . Displays usage information of the selected index, as follows:
...
- Start Date. Indicates the minimum execution date for a statement, within the selected time period.
...
- End Date. Indicates the maximum execution date for a statement, within the selected time period.
- Statement
...
- Explain. Displays statement explain, as follows:
...
- Statement Access the
...
- Object. Indicates the total number of statements that were explained successfully in the PMDB that refer to the selected index, within the selected time period.
...
- Statement Not Access the
...
- Object. Indicates the total number of statements that were explained successfully in the PMDB that do not refer to the selected index, within the selected time period.
...
- Statements Not Explained
...
- Yet. Indicates the total number of statements in the PMDB that were not yet explained, within the selected time period.
...
- Statements Explained Unsuccessfully. Indicates the total number of statements in the
...
- PMDB that were explained unsuccessfully, within the selected time period.
- Table
...
- Profile. Displays a breakdown, by statement type, of the number of statements that referred to the specified table (SELECT, INSERT, UPDATE, DELETE); for example, 100 statements performed SELECT on the specified table. The In MS-SQL counters (Using CPU and I/O Wait) are also displayed.
About viewing common access patterns for indexes (SQL Server 2005 only)
The Common Access Patterns view displays information that indicates to what extent an index was used. The following information is displayed:
- User
...
- Operations. Displays the number of user seeks, scans, lookups or updates.
- System
...
- Operations. Displays the number of system seeks, scans, lookups or updates.
- User vs. System
...
- Operations. Graph comparing user operations to system operations, over the selected time period.
- Last User or System Seek, Lookups, Scans or Updates
...
- . Displays when the last user or system seek, lookup, scan or update operation occurred.
About viewing operational statistics
The Operational Statistics view displays information that indicates whether or not the index on a table is being used. The following graphs are displayed:■ Range
- Range scan vs. Singleton lookups
...
- Physical I/O operations
...
- Locking and Latching contentions
...
- Locking and Latching contention waits
About the Index and Unused Index entities Association area
You can view general details on the entities associated with the selected Index entity in the Association area. It is possible to associate to the Index entity from the following entities: Database, Table, Column, View, User, File Group, Physical Disk, Logical Volume, HP Storage Device, HDS Storage Device, Statement and Batch.
The following additional information is available for SQL Server 2005 objects:Partitions Indicates
- Partitions. Indicates the number of partitions defined for the selected index.
- Index
...
- Partitioned. Indicates whether or not the index is partitioned.
- Partition
...
- Schema. Indicates under which file groups to map the partition.
- Partition
...
- Function. Displays the name of the function that defines how the index should be partitioned.
- Index
...
- Aligned. Indicates whether or not the index is aligned with its table.
- Number of Non-key
...
- Columns. Displays the number of non-key columns used within an index.
- Index Non-Key
...
- Columns. Displays the non-key column names.
| Anchor | ||||
|---|---|---|---|---|
|
...
The following view is available:■ Overview
- Overview
About getting an overview of an index or table that is partitioned
The Overview displays general details on the selected index or table, as follows:Properties The
- Properties. The following information is displayed for each partition:
...
- Object. Name of the object being partitioned.
■ Partition
- Partition. Displays the partitions id.
■ Filigreed
- Filigreed. Indicates where the data related to a partition is stored.
■ Rows
- Rows. Displays the number of rows per partition.
...
- Max Value
...
- . Indicates the upper value of a partition.
...
- Min Value
...
- . Indicates the lower value of a partition.
...
- Upper Boundary
...
- . Indicates whether data falling on the maximum value will be assigned to the next partition or to the same partition—yes will assign it to the next partition; no to the same partition it falls in.
...
- Space Allocation Displays the amount of space utilized by the partitions.
Rows Displays the number of rows in a partition, over the selected time period.
...