Page History
...
- About the Objects tab
- How the Objects tab is structured
- About Objects tab entities
- About the Instance entity About the Database entity
- How the Objects tab can help you identify performance problems
| Anchor | ||||
|---|---|---|---|---|
|
...
Objects
...
About the Index and Unused Index entities
...
tab
It is important to understand the relationship between database objects and statements to make well-founded tuning decisions.
The Objects tab is a comprehensive browser that allows you to understand the relationships and associations between database schema objects. It also enables further understanding of the relationships between data dictionary components and the statements that access them. This information allows you to identify the statements that will be affected by changes to a database object, and the schema relationship impacts that are associated with an object change.
The Objects tab is also very useful for determining scaling strategies for your database. Are you considering scaling out or scaling up? If you thoroughly understand the relationship between a table, the SQL executed on the table and the resources consumed by these SQL statements, you can identify the tables that may be candidates for either a horizontal or vertical scale-out. To engage a successful scale-out, you need to ensure that the tables you are horizontally partitioning to different databases engage in enough activity and resource consumption to spread the load across multiple systems. The same is true for vertical scaling. If you know the overall resource consumption of the instance and the consumption per table, you can make educated decisions as to how to best vertically partition your tables into multiple databases.
The Objects tab is also very useful for scale-ups. Too frequently companies add CPUs to an existing server in their attempt to improve performance. Although occasionally successful, step one of any scale-up activity should be to identify the major latencies in the system and resolve them. Only once the major wait states are resolved, will a scale-up actually be able to improve performance. Otherwise, you may be simply wasting money and valuable resources.
The Objects tab also allows you to observe storage utilization, over time, at varying levels, such as, database, file group, database files.
While the Activity tab focuses on SQL tuning, the Object tab focuses on database object tuning. In many cases, you may want to tune a database object rather than a specific query or program. This is especially true if you did not personally write the application in question, as for example in ERP and CRM applications, and cannot therefore change the text of any statement.
Object tuning is a powerful tool. Taking into consideration important aspects regarding objects can ensure maximum application performance. Precise for SQL Server allows you to examine your index structure by displaying the usage of each index. Storage layout can be optimized by analyzing the object storage configuration and metric information.
Because Precise for SQL Server tracks changes in the database objects, the effect of schema changes on performance can be easily observed.
| Anchor | ||||
|---|---|---|---|---|
|
The Objects tab displays information on a selected entity and its associated entities. When you open the Objects tab the selected entity is by default Instance, meaning that information is displayed on the instance level. When you launch the Objects tab from the Activity tab with one of the following entities—Database, Table, Index, DB file or Locked Object—the tab is launched in context with the entity you selected in the Activity tab.
If you open the Objects tab from another tab, the historical settings (those settings which were selected when you left the tab, such as the last entity you drilled down to) are taken into account and the information displayed the last time you viewed the tab is displayed (similar to clicking the History button and returning to a previous tab).
The selected entity is always reflected in the Tab heading, which serves as a point of orientation. The highest level entity you can view information for in the Objects tab is the Instance level. You can select an instance from the Instance list.
The entities displayed in the Association area are associated with the selected entity displayed in the Main area. At times, the relationship between the entity displayed in the Main area and those displayed in the Association area is that of parent to child and sometimes it merely represents that there is a relationship between the selected entity and the entities displayed in the Association area.
About the Main area in the Explore Objects tab
The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. You can, for example, focus exclusively on SQL Server Log data, or Instance Parameters, or Instance Parameters changes.
About the Association area in the Explore Objects tab
The Association area provides corresponding information on the child entities. You can view information on one type of child entity at a time, such as instances only or databases only, by selecting a control from the View Controls list. The selection you make is reflected in the Association area only; the Main area remains unchanged. For some entities, tabs above the Association area enable you to view additional information: Clicking a tab changes the table columns for the same type of child entities.
From the Association area, you can also drill down to another entity by clicking a table row. A drill-down affects the whole tab: When you drill down to another entity, the Tab heading reflects the new selection, the Main area displays information on the newly selected entity, and the Association area shows data on its associated entities.
For example, when you want to view information on a specific logical file, choose Logical Files from the Association controls. The Association area changes to display all the files groups associated with the selected Logical File. Note that the Tab heading and the Main area remain unchanged. In the Association area, click the row of the logical file you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays an overview of the logical file you want to investigate, and the Association area shows general details on the file groups that are associated with the Logical File you selected.
See About Precise for SQL Server tabs.
About the Recommend tab
Precise for SQL Server uses the Microsoft® Index Tuning Wizard to achieve recommended indexes or statistics for the selected table. This helps the optimizer choose a better access plan and enhances the performance of the statements or batches that access the selected table. The recommendations are based on a workload. The workload contains all the statements stored in the PMDB that were executed during the selected time period that have an average duration time exceeding the value defined in the registry (where 0 is the default). The recommend indexes process only performs the recommend process on indexes or statistics additions.
The following information is displayed in the Recommend tab:
- Recommended Index/Statistics
- Recommendation details
About viewing Recommended Indexes/Statistics
Displays the recommended indexes or statistics, as follows:
- Icon. Indicates whether the item is a recommended index or statistic.
- Table. Displays the name of the table to which the recommendation refers.
- Unique. Indicates whether the index is unique. This value is not relevant for recommended statistics.
- Clustered. Indicates whether the index is clustered. This value is not relevant for recommended statistics.
- Columns List. Displays a list of the index's or statistic's columns separated by commas. The sort order (ASC, DESC) is also displayed for recommended indexes.
About viewing Recommendation details
Displays details on the recommended index or statistic. The following tabs are available:
- Columns. Displays a list of all the columns in the recommended index's or statistic's table as follows:
- Icon. Indicates whether the data in the column will be stored in the index in ascending (ASC) or descending (DESC) order. No icon is displayed for columns that are not part of the recommended index.
- Column. Name of the column.
- Key Number. Key number of the column in the index.
- Column Type. The physical storage type of the column.
- Part of Index. Indicates whether the column participates in an existing index.
- First Key of Index. Indicates whether the column participates as the first key in an existing index.
- Used. Number of times the column is used in the breakdown of the workload statements according to the following types: Range Scan, Table Scan, Sort and Join.
- DDL. Displays the DDL text of the recommended index or statistic.
- Table's Indexes/Statistics. Displays a list of the existing indexes or statistics of the table selected in the recommendation area.
The following information is displayed:- Icon. Indicates whether the item is an index or statistic.
- Name. Displays the name of the index or statistic
...
It is important to understand the relationship between database objects and statements to make well-founded tuning decisions.
The Objects tab is a comprehensive browser that allows you to understand the relationships and associations between database schema objects. It also enables further understanding of the relationships between data dictionary components and the statements that access them. This information allows you to identify the statements that will be affected by changes to a database object, and the schema relationship impacts that are associated with an object change.
The Objects tab is also very useful for determining scaling strategies for your database. Are you considering scaling out or scaling up? If you thoroughly understand the relationship between a table, the SQL executed on the table and the resources consumed by these SQL statements, you can identify the tables that may be candidates for either a horizontal or vertical scale-out. To engage a successful scale-out, you need to ensure that the tables you are horizontally partitioning to different databases engage in enough activity and resource consumption to spread the load across multiple systems. The same is true for vertical scaling. If you know the overall resource consumption of the instance and the consumption per table, you can make educated decisions as to how to best vertically partition your tables into multiple databases.
The Objects tab is also very useful for scale-ups. Too frequently companies add CPUs to an existing server in their attempt to improve performance. Although occasionally successful, step one of any scale-up activity should be to identify the major latencies in the system and resolve them. Only once the major wait states are resolved, will a scale-up actually be able to improve performance. Otherwise, you may be simply wasting money and valuable resources.
The Objects tab also allows you to observe storage utilization, over time, at varying levels, such as, database, file group, database files.
While the Activity tab focuses on SQL tuning, the Object tab focuses on database object tuning. In many cases, you may want to tune a database object rather than a specific query or program. This is especially true if you did not personally write the application in question, as for example in ERP and CRM applications, and cannot therefore change the text of any statement.
Object tuning is a powerful tool. Taking into consideration important aspects regarding objects can ensure maximum application performance. Precise for SQL Server allows you to examine your index structure by displaying the usage of each index. Storage layout can be optimized by analyzing the object storage configuration and metric information.
Because Precise for SQL Server tracks changes in the database objects, the effect of schema changes on performance can be easily observed.
...
The Objects tab displays information on a selected entity and its associated entities. When you open the Objects tab the selected entity is by default Instance, meaning that information is displayed on the instance level. When you launch the Objects tab from the Activity tab with one of the following entities—Database, Table, Index, DB file or Locked Object—the tab is launched in context with the entity you selected in the Activity tab.
If you open the Objects tab from another tab, the historical settings (those settings which were selected when you left the tab, such as the last entity you drilled down to) are taken into account and the information displayed the last time you viewed the tab is displayed (similar to clicking the History button and returning to a previous tab).
The selected entity is always reflected in the Tab heading, which serves as a point of orientation. The highest level entity you can view information for in the Objects tab is the Instance level. You can select an instance from the Instance list.
The entities displayed in the Association area are associated with the selected entity displayed in the Main area. At times, the relationship between the entity displayed in the Main area and those displayed in the Association area is that of parent to child and sometimes it merely represents that there is a relationship between the selected entity and the entities displayed in the Association area.
About the Main area in the Explore Objects tab
The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. You can, for example, focus exclusively on SQL Server Log data, or Instance Parameters, or Instance Parameters changes.
About the Association area in the Explore Objects tab
The Association area provides corresponding information on the child entities. You can view information on one type of child entity at a time, such as instances only or databases only, by selecting a control from the View Controls list. The selection you make is reflected in the Association area only; the Main area remains unchanged. For some entities, tabs above the Association area enable you to view additional information: Clicking a tab changes the table columns for the same type of child entities.
From the Association area, you can also drill down to another entity by clicking a table row. A drill-down affects the whole tab: When you drill down to another entity, the Tab heading reflects the new selection, the Main area displays information on the newly selected entity, and the Association area shows data on its associated entities.
For example, when you want to view information on a specific logical file, choose Logical Files from the Association controls. The Association area changes to display all the files groups associated with the selected Logical File. Note that the Tab heading and the Main area remain unchanged. In the Association area, click the row of the logical file you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays an overview of the logical file you want to investigate, and the Association area shows general details on the file groups that are associated with the Logical File you selected.
See “About Precise for SQL Server tabs” on page 22.
About the Recommend tab
Precise for SQL Server uses the Microsoft® Index Tuning Wizard to achieve recommended indexes or statistics for the selected table. This helps the optimizer choose a better access plan and enhances the performance of the statements or batches that access the selected table. The recommendations are based on a workload. The workload contains all the statements stored in the PMDB that were executed during the selected time period that have an average duration time exceeding the value defined in the registry (where 0 is the default). The recommend indexes process only performs the recommend process on indexes or statistics additions.
The following information is displayed in the Recommend tab:
- Recommended Index/Statistics
- Recommendation details
About viewing Recommended Indexes/Statistics
Displays the recommended indexes or statistics, as follows:
- Icon. Indicates whether the item is a recommended index or statistic.
- Table. Displays the name of the table to which the recommendation refers
- .
- Unique. Indicates whether the index is unique. This value is not relevant for
- statistics.
- Clustered. Indicates whether the index is clustered. This value is not relevant for
- statistics.
- Columns List. Displays a list of the columns of the index
- or statistic
- separated by commas.
The sort order (
- ascending,
- descending) is also displayed for
- indexes.
About viewing Recommendation details
Displays details on the recommended index or statistic. The following tabs are available:
- Columns. Analyzed Statements. Displays a list of all the columns in the recommended index's or statistic's table as follows:
- Icon. Indicates whether the data in the column will be stored in the index in ascending (ASC) or descending (DESC) order. No icon is displayed for columns that are not part of the recommended index.
- Column. Name of the column.
- Key Number. Key number of the column in the index.
- Column Type. The physical storage type of the column.
- Part of Index. Indicates whether the column participates in an existing index.
- First Key of Index. Indicates whether the column participates as the first key in an existing index.
- Used. Number of times the column is used in the breakdown of the workload statements according to the following types: Range Scan, Table Scan, Sort and Join.
- DDL. Displays the DDL text of the recommended index or statistic.
- Table's Indexes/Statistics. Displays a list of the existing indexes or statistics of the table selected in the recommendation area.
The following information is displayed:- Icon. Indicates whether the item is an index or statistic.
- Name. Displays the name of the index or statistic.
- Unique. Indicates whether the index is unique. This value is not relevant for statistics.
- Clustered. Indicates whether the index is clustered. This value is not relevant for statistics.
- Columns List. Displays a list of the columns of the index or statistic separated by commas.
The sort order (ascending, descending) is also displayed for indexes.
- Analyzed Statements. Displays a list of all the statements in the workload.
The following information is displayed:- Statement. Indicates the statement ID.
- In MS-SQL. Displays the resource consumption (as a stacked bar) of the specified statement within the selected time period.
- Text. Displays the statement's text.
...
- statements in the workload.
The following information is displayed:- Statement. Indicates the statement ID.
- In MS-SQL. Displays the resource consumption (as a stacked bar) of the specified statement within the selected time period.
- Text. Displays the statement's text.
| Anchor | ||||
|---|---|---|---|---|
|
The following entities can be examined in the Objects tab:
- Instance
- Database
- Active Objects
- Table and Table Contain Same Column
- Index and Unused Index
- Partition
- Column
- View and View Contain Same Column
- User
- Logical File
- File Group
- Statistics
- Stored Procedure
- Function
- Trigger
- Statement
- Batch
- Physical Disk
- Logical Volume
- Disk Partition
| Anchor | ||||
|---|---|---|---|---|
|
The Instance entity displays configuration information, configuration changes, as collected by the Collect Schema Changes process, and performance information, as collected by the Precise for SQL Server Collector for the entire instance.
The following views are available:
- Overview
- SQL Server Log
- Instance Parameters
- Instance/Database Changes
- Databases Created/Dropped
About getting an overview of instance configuration and performance information
The Overview displays configuration and performance information for the entire instance. The Overview is divided into the following areas:
- Server Details. Displays server configuration details such as version and number of CPUs.
- Instance Details. Displays SQL Server instance configuration details such as version, service pack and server details.
- In MS-SQL. Displays resource consumption, over the selected time period, of the selected instance, as collected by the Precise for SQL Server Collector.
- Space Allocation. Displays the total free space and total used space (in MB) over the selected time period, of all the files in the selected instance, as collected by the Collect Space Utilization process.
About viewing SQL server log messages
The SQL Server Log view displays the SQL Server log messages from the recent error log file, for the selected time period.
About viewing instance configuration parameters
The Instance Parameter view displays the selected instance's configuration parameters. This includes the possible range values and configuration values, for each parameter. A change icon is displayed if the Collect Schema Change Process detects a change in any of the parameters.
Precise for SQL Server reports changes that occurred within the selected time period. If you observe that a parameter was changed, select Instance Parameter Changes from the View controls, to view additional information.
About viewing changes to instance parameters and database options
The Instance/Database view displays the changes to instance parameters and database options vs. resource consumption, over the selected time period, to enable you to identify configuration performance changes caused by these actions, as follows:
- Instance/Database Changes.
- Sample Date. The date when the changes were captured by the Collect Schema Changes process.
- Database. The name of the database where the change occurred. For Instance Configuration changes, the value is N/A.
- Data Changed. Displays one of the following values:
- Schema. Any change in the database schema.
- Database Option. A description of the database option that was changed
- Configuration Parameter. A description of the instance configuration parameter that was changed.
- New Value. Displays the new value that resulted following the change.
- Old Value. Displays the previous value that preceded the change.
- In MS-SQL. Displays resource consumption of the selected instance, over the selected time period
The following entities can be examined in the Objects tab:
- Instance
- Database
- Active Objects
- Table and Table Contain Same Column
- Index and Unused Index
- Partition
- Column
- View and View Contain Same Column
- User
- Logical File
- File Group
- Statistics
- Stored Procedure
- Function
- Trigger
- Statement
- Batch
- Physical Disk
- Logical Volume
- Disk Partition
...
The Instance entity displays configuration information, configuration changes, as collected by the Collect Schema Changes process, and performance information, as collected by the Precise for SQL Server Collector for the entire instance.
The following views are available:
- Overview
- SQL Server Log
- Instance Parameters
- Instance/Database Changes
- Databases Created/Dropped
About getting an overview of instance configuration and performance information
The Overview displays configuration and performance information for the entire instance. The Overview is divided into the following areas:
- Server Details. Displays server configuration details such as version and number of CPUs.
- Instance Details. Displays SQL Server instance configuration details such as version, service pack and server details.
- In MS-SQL. Displays resource consumption, over the selected time period, of the selected instance, as collected by the Precise for SQL Server Collector.
- Space Allocation. Displays the total free space and total used space (in MB) over the selected time period, of all the files in the selected instance, as collected by the Collect Space Utilization process.
About viewing SQL server log messages
The SQL Server Log view displays the SQL Server log messages from the recent error log file, for the selected time period.
About viewing instance configuration parameters
The Instance Parameter view displays the selected instance's configuration parameters. This includes the possible range values and configuration values, for each parameter. A change icon is displayed if the Collect Schema Change Process detects a change in any of the parameters.
Precise for SQL Server reports changes that occurred within the selected time period. If you observe that a parameter was changed, select Instance Parameter Changes from the View controls, to view additional information.
About viewing changes to instance parameters and database options
The Instance/Database view displays the changes to instance parameters and database options vs. resource consumption, over the selected time period, to enable you to identify configuration performance changes caused by these actions, as follows:
- Instance/Database Changes.
- Sample Date. The date when the changes were captured by the Collect Schema Changes process.
- Database. The name of the database where the change occurred. For Instance Configuration changes, the value is N/A.
- Data Changed. Displays one of the following values:
- Schema. Any change in the database schema.
- Database Option. A description of the database option that was changed
- Configuration Parameter. A description of the instance configuration parameter that was changed.
- New Value. Displays the new value that resulted following the change.
- Old Value. Displays the previous value that preceded the change.
- In MS-SQL. Displays resource consumption of the selected instance, over the selected time period, as collected by the Precise for SQL Server Collector.
- Schema Changes. Displays an overtime graph that indicates whether changes occurred in the database, or a breakdown of the instances into the following groups:
- Schema Changed. Any change in one of the database's Schema.
- Database Option Changed. Any change in one of the database's options.
- Instance Property Changed. Any change in one of the instance configuration parameters.
The following configuration parameters that can be displayed:
- Schema Changes. Displays an overtime graph that indicates whether changes occurred in the database, or a breakdown of the instances into the following groups:
- Schema Changed. Any change in one of the database's Schema.
- Database Option Changed. Any change in one of the database's options.
- Instance Property Changed. Any change in one of the instance configuration parameters.
The following configuration parameters that can be displayed:
- Maximum recovery interval in minutes
- Number of locks for all users
- Number of open database objects
- Maximum worker threads.
- Network packet size
- Memory for index create sorts (KB)
- Priority boost
- Minimum memory per
- Maximum recovery interval in minutes
- Number of locks for all users
- Number of open database objects
- Maximum worker threads.
- Network packet size
- Memory for index create sorts (KB)
- Priority boost
- Minimum memory per query (KB)
- Query wait(s)
- Set working set size
- Affinity mask
- Cost threshold for parallelism
- Maximum degree of parallelism
- Minimum Server Memory size (MB)
- Maximum Server Memory size (MB)
- Maximum estimated cost of query allowed to run by query governor
- User mode scheduler uses lightweight pooling
- Recovery Model Changed
- Auto Update Statistics Changed
- Ansi Null Changed
- Auto Shrink File Changed
- Close Cursors On Commit Changed
- Status Changed
- Auto Create Statistics Changed
- Truncate Log Changed
- Compatibility Level Changed
...
- the name of the user, object, and database it is associated with
- a summary of the time it spent in SQL Server, during the time frame you selected
- its size (in pages)
See “About About the Table and Table Contain Same Column entities” on page 113 and “About entities and About the Index and Unused Index entities” on page 117entities.
About the Active Objects Association area
...
- 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:
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
The Statistics view displays distribution statistics for the selected index.
...
- 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 following additional information is available for SQL Server 2005 and later objects:
- 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.
...
The Partition entity displays information on how an index or table is partitioned. All tables and indexes in a database are considered to be partitioned , in SQL Server 2005, even if they are made up of only one partition. The data of partitioned tables and indexes is divided into units that can be spread across more than one file group in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions.
...
The Statistics view displays distribution statistics for the selected statistic. see “About See About viewing distribution statistics for a selected index” on page 120index.
About the Statistics entity Association area
...
For more information about the Storage Device entity, see “About About Storage Unit entities” on page 137entities.
About the Storage Unit entity Association area
...
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
...
| Info |
|---|
The values might be incorrect due to out-of-date space usage information. To update the values, use the |
...
| 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 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
...
To determine whether to recommend an index for a table
- 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. Drill down to the table you want to analyze.
- On the Recommend tab, analyze the statements that are associated with the selected table and observe their resource consumption.
The Recommend Indexes output shows a list of recommended indexes and statistics. For each recommended item, detailed information is displayed in the Details area, such as a list of key columns and the DDL of the Create Index or Statistics text.
Precise. Performance intelligence from click to storage. Learn more > >
...
index for a table
- 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. Drill down to the table you want to analyze.
- On the Recommend tab, analyze the statements that are associated with the selected table and observe their resource consumption.
The Recommend Indexes output shows a list of recommended indexes and statistics. For each recommended item, detailed information is displayed in the Details area, such as a list of key columns and the DDL of the Create Index or Statistics text.
...