Page History

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.

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 to prevent unnecessary table lookups or in case of 2000 instance, use the index covering technique to eliminate extra table lookups

Perform one of the following options:

• Examine the index and statistics recommendations for this object in the Recommend tab, in the Objects tab.

• Explore table details in the Objects tab.

• Examine table activity in the Activity tab.

One of the following operations (for example, table scan, index scan, clustered index scan or filter) was identified as a major cost indicator. Try to eliminate the major consuming operation by doing one of the following:

For a table scan:

• Eliminate a full table scan or cluster index scan.

• Try to identify high selectivity columns for the statements or batches. Create an index matching the statement or batch's predicates.

• Try to identify if there is a small set of columns that can be used in an index-only operation.

• Partition the table according to the best predicates existing in the statements or batches.

• Improve full table scan. Table fragmentation can affect overhead when a table scan or partial table scan is performed. Determine whether the table is heavily fragmented. If it is, defragment the table.

For an index scan:

• If a missing index finding is detected, launch to the Recommend tab. Otherwise try to identify where the 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.

For a Filter:

• Eliminate a filter by doing one of the following:

• • Consider creating an index view.

• • Consider performing an index on the computed column.

For a Sort:

• Try reducing the space or memory required for Sort operations by reducing the number of sorted columns or by filtering the rows to be sorted.

• Try to find a way to eliminate the sort by using pre-sorted information like creating an index on the sorted columns.

• Check the values of the Minimum memory per query (KB) and Minimum memory for index create sorts (KB) instance parameters. Use the

• Memory for index create sorts (KB) configuration to control the amount of memory used by index creation sorts. The Minimum memory for index create sorts (KB) configuration is self-configuring and should work in most cases without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value. Query sorts are controlled through the Minimum memory per query (KB).

For a Hash:

• Hash join is used where the joined tables or row sets are large or where there aren't adequate indexes or the lack of indexes at all.

• • Verify that there are no missing indexes or the indexes are adequate.

• • Search for a missing WHERE clause or a missing condition in the WHERE clause.

• • Search for a non-sargeable expression. A non-sargeable expression is an expression preventing the optimizer of using the index in the ideal way or not at all like a function expression.

• • Check the values of the Minimum Size of Server Memory (MB) and Max Size of Server Memory (MB) instance parameters.

For a Index Spool, Table Spool and Row Count Spool:

• Spooling are internally temporary tables created by SQL Server on the tempdb database. Spooling may cause tempdb overhead and thus resulting in instance performance degradation caused by tempdb wait or tempdb major growth.

• • Try to eliminate spooling operations by rephrasing the query.

• • Try to reduce the number of logical reads or writes.