Page History

Volume changes may effect response time especially in:

• Table scans. May effect costs dramatically and create larger scans.

• Index scans. May create larger scans.

• 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.

• Examine changes in the History tab and analyze these changes over time by launching to the

• Objects tab.

Perform one of the following options:

• Examine the history of the statement.

• Examine the index and statistics recommendations for the selected statement.

• Examine the statement plan in the SQL tab.

• Examine statement activity in the Activity tab.

The average In MS-SQL time of the statement or batch 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 the execution plan. Examine the history of the statement and try to identify whether a major change took place. Compare explain plans using the Compare tab.

Check instance behavior. An increase in instance execution over time, may affect the performance of regular statements.

Perform one of the following options:

• Check the statement or batch locking information and associated blocker sessions in the Activity tab.

• Check the locked object in the Activity tab.

• Check statement or batch activity in the Activity tab. Increase the time frame, if necessary.

Identify the blocker session in the Activity tab and examine the application and application timing. Examine the lock chain to identify the statement or batch holding the lock:

• In the Activity tab sort statements or batches by their lock wait.

• Identify major statements or batches.

• Narrow the time frame. Drill down into major statements or batches.

• In the Association area, select Blocker Sessions.

• Try to identify lock patterns within a larger time frame.

Perform one of the following solutions:

• Examine statement activity in the Activity tab.

• Examine the index and statistics recommendations for the selected statement.

• Examine the plan of the statement in the SQL tab.

The In MS-SQL time of the statement is consistently high for the select time frame, as compared with resource consumption of the previous week. This finding is issued when the statement shows up within the top resource consumers of your application, their resource consumption was always high, and no major change in resource consumption occurred during the last two weeks.

Check which resource is most-consumed by the statement or batch and determine how you can make it available to the statement.

For example: Consider scheduling different activities that uses the same resource at different times, thereby freeing the resources for the statement.