Page History

Perform one of the following options:

• Click Locate to find the heavy operator and tune it.
• Identify recommendations in the Recommend tab in the SQL tab.
• In case of Hash or Sort operators examine internal wait for the statement.

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

Perform one of the following options:

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

Perform the following options:

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