...
Table 13-15 Missing Indexes findings
...
...
| next | Examine the information displayed in the Recommend tab in the Objects tab to identify index recommendations. |
| Advice |
...
Perform one of the following options: |
...
- Examine the information displayed in the Recommend tab in the Objects tab to identify index recommendations.
|
...
- Use the What if feature in the Recommend tab to evaluate which statements were affected, based on a large set of statements that were active during the selected time frame. This feature lets you check whether it is possible to improve performance by creating one or several indexes.
|
| Anchor |
|---|
| MissingStatistics |
|---|
| MissingStatistics |
|---|
|
Missing Statistics
...
Table 13-16 Missing Statistics findings
...
...
| next | Examine the information displayed in the Recommend tab in the Objects tab to identify missing statistics and analyze index recommendations. |
...
| Advice | Perform the following options: |
...
- Examine the information displayed in the Recommend tab in the Objects tab to identify missing statistics and analyze index recommendations.
- Use the What if feature in the Recommend tab to evaluate a missing index, based upon a large set of statements or batches that were active during the selected time frame.
This feature lets you check whether it is possible to improve performance by creating one or several indexes.
Once statistics are collected, remember that statistics should be periodically maintained.
|
| Anchor |
|---|
| TableSchemaChangeMayIncreaseItsAccessingTime |
|---|
| TableSchemaChangeMayIncreaseItsAccessingTime |
|---|
|
Table Schema Change May Increase Its Accessing Time
The total In MS-SQL time of the object increased after changes were made to the schema.
Table 13-17 Table Schema Change May Increase Its Accessing Time findings
...
...
| next | Examine table details and schema changes in the Objects tab. |
| Advice |
...
Several changes may effect in MS-SQL response time and may effect other statement changes as well. Changes such as index creation/drop can affect statement performance: |
...
- Identify the change in the Objects tab.
|
...
- Examine the change, when it was made, the performance changes related to the change, and identify any other changes in the object that may result from the change.
|
| Anchor |
|---|
| ObjectIsNotScalable |
|---|
| ObjectIsNotScalable |
|---|
|
Object Is Not Scalable
The total In MS-SQL contribution of the object increased when the number of statements executions changed.
Table 13-18 Object Is Not Scalable findings
...
...
| next | Examine the object’s behavior over time and explore the usage patterns of the statements. |
| Advice |
...
Perform one of the following options: |
...
- Examine the object’s behavior over time and explore the usage patterns of the statements.
|
...
- Check whether usage patterns dramatically changed. If yes, consider improving them by indexing or partitioning.
|
...
- Identify major users by associating to users or machines in the Association. Determine if this is acceptable for your system based on your knowledge of your system.
|
...
| Anchor |
|---|
| TableGrowthMayIncreaseItsAccessingTime |
|---|
| TableGrowthMayIncreaseItsAccessingTime |
|---|
|
Table Growth May Increase Its Accessing Time
The total In MS-SQL time of the object increased following a major change in the table size.
Table 13-19 Table Growth May Increase Its Accessing Time findings
...
...
| next | Examine table details and associated statements in the Objects tab to identify statements that may be affected by table growth. |
| Advice |
...
Volume changes may effect response time especially in: |
...
- Table scans: may effect costs dramatically.
|
...
- Index structure: Every level in the index depth requires I/O synchronization. An index depth of four will result in four synchronized I/Os for each key, meaning that I/O cost will be too high and the optimizer may choose not to use the index. Try defragmenting the index or redesigning it.
Try to identify the heaviest statements and tune them. |
| Anchor |
|---|
| IncreaseinResourceConsumption |
|---|
| IncreaseinResourceConsumption |
|---|
|
Increase in Resource Consumption
The total In MS-SQL time of the object has increased.
Table 13-20 Increase in Resource Consumption findings
...
...
| next | Perform one of the following options: |
- Examine table details of common access patterns and associated statements in the Objects tab (SQL Server 2005 only).
- Examine the recommendations for the index and statistics of the selected object. Explore table details in the Objects tab.
|
...
| Advice | The average In MS-SQL time of the object has increased but not as a result of major table growth, scalability change or schema changes. A major change can result from a change in execution plan. Examine the table details and the statement accessing it and try to identify what caused the change. Compare explain plans using the Compare tab. |
Locked Object
...
Regular locks can be categorized as follows:■ During
- During the blocker session, a locked statement ran for a short period of time. Afterwards the session was idle or continued running other statements. In this case, it is possible to identify the blocker session, but not necessarily the blocker statement.
...
- During the blocker session, a locked statement ran for a long period of time. Identifying the blocker statement is easier in this case.
Table 13-21 Locked Object findings
...
...
| next | Perform one of the following options: |
...
- Examine locking information in the Activity tab.
|
...
- Examine the index and statistics recommendations for the selected object.
|
...
- Explore table details in the Objects tab.
|
...
| Advice | Identify the blocker session in the Activity tab and examine the application and application timing. Examine the lock chain to identify the statement holding the lock. |
...
| Anchor |
|---|
| IndexOverhead |
|---|
| IndexOverhead |
|---|
|
Index Overhead
Most of the activity on the index is due to the fetching of index pages from the disk, reflecting changes made by INSERT, DELETE, and UPDATE statements.
Table 13-22 Index Overhead findings
...
...
| next | Perform one of the following options: |
...
- Examine the statements causing index updates, in the Statements tab.
|
...
- Check if the index is being used in execution plans.
|
...
- Try to identify index update patterns (such as, daily or nightly), in the In Oracle graph, in the Read/Write Operations tab.
|
...
| Advice | Perform one of the following options: |
...
- When insert statements are part of a load, batch, or night activity, consider dropping the index before performing the activity, and recreating it afterwards.
|
...
- If the index is not used in execution plans consider dropping the index or unused columns from the index, to reduce index overhead. If the index is used in execution plans, launch to the What-If tab to see which statements may be effected by this change.
|
| Anchor |
|---|
| HighAmountofIndexScans |
|---|
| HighAmountofIndexScans |
|---|
|
High Amount of Index Scans
...
Table 13-23 High Amount of Index Scan findings
...
...
| next | - Examine the information displayed in the 'Common Access Patterns' and Operational Statistics'
|
...
...
- Examine the information displayed in the Recommend tab in the SQL tab to identify missing statistics and analyze index recommendations.
|
...
| Advice | Perform one of the following options: |
...
- Examine the index scans percentage of overall usage of the index
|
...
- Associate to statements and find out why the optimizer chose to use index scans by looking for missing predicates, functions on the index columns, non-selective columns, range scan queries, inadequate indexes, etc.
|
...
- Examine the information displayed in the Recommend tab in the SQL tab to identify missing statistics and analyze index recommendations.
|
...
- Use the What If feature in the Recommend tab to evaluate which statements or batches were affected, based on a large set of statements or batches that were active during the selected time frame. This feature lets you check whether it is possible to improve performance by creating one or several indexes.
|
...
| Anchor |
|---|
| HighAmountofTableLookups |
|---|
| HighAmountofTableLookups |
|---|
|
High Amount of Table Lookups
...
Table 13-24 High Amount of Table Lookup findings
...
...
| next | Examine the information displayed in the Recommend tab in the SQL tab to identify and analyze index covering recommendations. |
| Advice |
...
Perform one of the following options: |
...
- Examine the information displayed in the Recommend tab in the SQL tab and check for index covering recommendations.
|
...
- Use the What If feature in the Recommend tab to evaluate which statements or batches were affected, based on a large set of statements or batches that were active during the selected time frame. This feature lets you check whether it is possible to improve performance by creating one or several indexes.
|
...
| Anchor |
|---|
| HighAmountofUnusedHeapPages |
|---|
| HighAmountofUnusedHeapPages |
|---|
|
High Amount of Unused Heap Pages
...
Table 13-25 High amount of unused Heap Pages
...
...
| next | Examine the difference between the number of rows and allocated pages and consider rearranging the table by creating a cluster index and then dropping it. |
...
| Anchor |
|---|
| AboutInstancefindings |
|---|
| AboutInstancefindings |
|---|
|
About Instance findings
The following instance findings can help you tune your system:
...