Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

ColumnDescription
What to do nextIn the SQL tab, examine the information displayed in the History tab to identify object changes and correlate these changes to performance degradation.
Advice

Several changes may effect In MS-SQL time and may effect other statements In MS-SQL as well. Changes such as index creation/drop can cause access plan changes that can effect statement performance.

Do the following:

  • Identify the change in the History tab
  • Compare statement or batch execution plans in the Compare tab.

...

ColumnDescription
What to do next

Perform one of the following options:

  • Examine the SmarTune object findings for the selected object.
  • Examine the index and statistics recommendations in the Recommend tab in the Objects tab.
  • Examine table details in the Objects tab.
  • Examine table activity in the Activity tab.
Advice

The following scenarios indicate which factors can lead to heavy resource consumption and what steps you can take:

  • In MS-SQL time of the object increased. Identify and try to find the source of the change. An example of such a change can be table growth or statistics changes.
  • A missing index was identified (SQL Server 2005 only). The SQL Server optimizer identified missing indexes when the access plan was created. Detailed index information can be found in the Recommend tab in the Objects tab.
  • Locked Object. Try to identify blocker sessions.
  • The object is suffering from high number of index scans.
    Try to identify where a matching level problem exists, indicating that the index column order does not match the statement or batch WHERE columns.
    Example:
    An index on A, B, C, while the statement or batch is "select … where A=3 and C=7".
    Many irrelevant index leaf pages will be read in this case. This can be avoided by either changing the index column sequence or adding columns to the index.
    Analyze the recommendations provided in the Recommend tab in the SQL tab.
  • The object is suffering from high number of table lookups.
    Verify that all the columns in the select list are indeed needed.
    Try to use the INCLUDE (2005 only) option to add the needed columns to the index leafs leaves to prevent unnecessary table lookups or in case of 2000 instance, use the index covering technique to eliminate extra table lookups

...

ColumnDescription
What to do nextExamine the storage devices statistics and hit radio.
Advice
  • It has been detected that the storage devices (LUNs) that serve the instance get bad cache performance (i.e. low "hit ratio"). To relief the cache contention, consult the storage administrator about the following:
  • Expanding the storage cache allocated to the database devices.
  • Enabling "EMC Cache Partitioning" , to isolate the instance cache and avoid external contentions.

...