Page History

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

• A heavy operator was identified. Use the SQL findings to identify the heaviest operator.

• 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 SQL tab.
• Missing statistics were identified. The SQL Server optimizer identified missing statistics when the access plan was created. Detailed information on missing statistics for this statement can be found in the Recommend tab in the SQL tab.
• An increase of In MS-SQL time was identified. Use Precise for SQL Server to try and locate the cause. Check schema changes, major table growth and scalability changes.

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