SQL Server Findings

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.

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

• A scalability issue was raised. Check performance over time in the Over Time tab.
• In the Statements tab, tune a statement by doing one of the following:
  • If a heavy operator was identified, use the SQL findings to identify the heaviest operator.
  • If a missing index was identified (SQL Server 2005 only), this means that the SQL Server optimizer identified a missing index when the access plan was created. Detailed index information can be found in the Recommend tab in the SQL tab.
  • If missing statistics were identified this means that the SQL Server optimizer identified missing statistics when the access plan was created. Detailed information on missing statistics for the collapsed statement can be found in the Recommend tab in the SQL tab.
  • If an increase 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.

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.

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.

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.

Perform one of the following options:

• Examine scalability by analyzing degradation in average duration for statement resulting from an increase in the number of executions.
• Check the program executing the statement and try to identify application changes.
• Explore other statement or batch execution identifiers, such as, machine or users, in the Activity tab.

Perform the following options:

• Try to determine if this is a pure scalability issue. Increase the time frame and explore statement scalability over a larger period of time. If it is a problem of scalability, this can be treated in many ways.
• For example, you can avoid unscalable operations, such as table scans, in an execution plan.
• Eliminate concurrency by distributing the operation of the application over several MS-SQL servers.

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.

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.

Perform one of the following options:

• Examine the information displayed in the Recommend tab in the Objects tab to identify index recommendations.
• Use the What if feature in the Recommend tab to evaluate which statements were affected, based on a large set of statements 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 Objects 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.

Several changes may effect in MS-SQL response time and may effect other statement changes as well. Changes such as index creation/drop can affect statement performance:

• Identify the change in the Objects tab.
• Examine the change, when it was made, the performance changes related to the change, and identify any other changes in the object that may result from the change.

Perform one of the following options:

• Examine the object’s behavior over time and explore the usage patterns of the statements.
• Check whether usage patterns dramatically changed. If yes, consider improving them by indexing or partitioning.
• Identify major users by associating to users or machines in the Association. Determine if this is acceptable for your system based on your knowledge of your system.

Volume changes may effect response time especially in:

• Table scans: may effect costs dramatically.
• 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.

Try to identify the heaviest statements and tune them.

Perform one of the following options:

• Examine table details of common access patterns and associated statements in the Objects tab (SQL Server 2005 only).
• Examine the recommendations for the index and statistics of the selected object. Explore table details in the Objects tab.

Perform one of the following options:

• Examine locking information in the Activity tab.
• Examine the index and statistics recommendations for the selected object.
• Explore table details in the Objects tab.

Perform one of the following options:

• Examine the statements causing index updates, in the Statements tab.
• Check if the index is being used in execution plans.
• Try to identify index update patterns (such as, daily or nightly), in the In Oracle graph, in the Read/Write Operations tab.

Perform one of the following options:

• When insert statements are part of a load, batch, or night activity, consider dropping the index before performing the activity, and recreating it afterwards.
• If the index is not used in execution plans consider dropping the index or unused columns from the index, to reduce index overhead. If the index is used in execution plans, launch to the What-If tab to see which statements may be effected by this change.

• Examine the information displayed in the 'Common Access Patterns' and Operational Statistics'
• Associate to statements
• Examine the information displayed in the Recommend tab in the SQL tab to identify missing statistics and analyze index recommendations.

Perform one of the following options:

• Examine the index scans percentage of overall usage of the index
• Associate to statements and find out why the optimizer chose to use index scans by looking for missing predicates, functions on the index columns, non-selective columns, range scan queries, inadequate indexes, etc.
• 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 one of the following options:

• Examine the information displayed in the Recommend tab in the SQL tab and check for index covering 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 one of the following options:

• Examine the table activity and locking information in the Activity tab
• Examine lock counters in the Statistics tab.

To reduce the lock wait for the instance, consider the following solutions: Concentrate on locked statements:

• In the Activity tab sort statements by their lock wait.
• Identify a major statement.
• Narrow down the time frame and drill down into it.
• Change association to Blocker Sessions.
• Concentrate on locked objects. Check to see if the lock appears in the Current tab. If yes, examine the lock chain to identify the statement holding the lock.
• Concentrate on blocker sessions. Try to identify the locking statement in the Activity tab using a narrower time frame matching, the lock periods. Focus on the locked table and associated statements. The DML statement (and update queries) that are NOT waiting for locks can be the immediate suspects.

• Examine the type of statements heavily using the tempdb resource in the Activity tab.
• Check that a sort is being performed on the statements and tune the Sort operation. Tuning a Sort operation can be performed by making sure that only the relevant columns and rows are being sorted. You can view the columns being sorted in the SQL tab.
• Check how the tempdb files are created in the Objects tab. For example, check on which devices the files reside and check the auto-extend parameters (a small value can create fragmentation at the OS level).
• Check the load on the devices on which the tempdb files are created. You can view this in the Statistics tab.

• Tune sorts or joins:
  • Try reducing the space required for Sort operations by reducing the number of sorted columns or by filtering the rows to be sorted.
  • Verify that Hash and Merge joins occurred as the result of the proper join methodology. Hash and Merge joins are temporary storage consumers.
  • Create indexes to eliminate the sorts or change the join methodology to a Nested Loop.
  • Check the values of the min server memory (MB) and index create memory (KB) instance parameters.
• Tune tempdb files:
  • Verify that tempdb files are distributed across several devices.
  • Check the load on each device.
  • Verify the auto-extend parameters.
  • Verify that the file is not fragmented at the OS level.
• Tune row versioning:
  • Verify that row versioning is enabled only when needed.
  • Verify that the Snapshot Isolation level is used properly.
• Examine tempdb usage when the applications explicitly create temporary tables. Try creating indexes on those tables after they are populated.

• Examine the hit ratio of the caches in SQL Server, in the Statistics tab.
• Examine the page faults the SQL Server issues. A small number of page faults and a low hit ratio can indicate that the SQL Server is not maximizing the use of server memory.
• Examine the memory being allocated to the SQL Server as opposed to the memory available to the server.
• Examine the memory consumption of other processes in the Insight Savvy for OS.
• Examine how long the applications wait for I/O.
• Examine page life expectancy, in the Statistics tab.

Before increasing memory for the SQL Server, check that you are not going to adversely affect other applications running on the server by doing so. In addition, adding increasing the SQL Server memory too much, can decrease the performance, because SQL Server will have to do paging.

Experts recommend allowing operating systems to have 20% available memory. If you have other applications running on the same server, check their memory requirements in the Insight Savvy for OS.

When you determine how much memory should be allocated to SQL Server, you can change its settings. In this case you can configure memory to be fixed and not dynamic.

Check the value of the min server memory (MB) instance parameter. This parameter indicates how much memory is allocated to every session connected to the SQL Server. If you find that you are experiencing many idle sessions (due to the threshold settings in the connection pooling) you are wasting too much memory.

SQL Server does not allow the configuration of different settings for each cache, so you must verify whether or not the buffer cache has enough memory.

• Examine the SQL Server page faults, in the Statistics tab.
• Examine the memory allocated to SQL Server.
• Examine the memory resources used by other processes on the server.
• If Insight Savvy for OS is installed, check the memory usage of all processes on the server.

Because SQL Server is your RDBMS, and it is the single resource being used by most of your applications, it is essential that it receive all the resources it requires. You should be aware of which applications are also running on the server and how they affect your SQL Server.

Examine the amount of memory being used by the SQL Server and the amount of memory being used by the other processes located on the server. Verify that SQL Server has enough memory allocated to it.

Prioritize the other applications running on the server and decide if any of the other processes can be migrated to another server, thereby freeing memory resources to the SQL Server.

• Examine CPU usage of the server, in the Statistics tab.
• Examine In MS-SQL breakdown, in the Activity tab.
• If Insight Savvy for OS is installed, examine CPU usage of all processes on the server.

Because SQL Server is your RDBMS, and it is the single resource being used by most of your applications, it is essential that it receive all the resources it requires. You should be aware of which applications are also running on the server and how they affect your SQL Server.

Examine the amount of CPU being used by the SQL Server and the amount of memory being used by the other processes located on the server. Verify that SQL Server has enough CPU allocated to it.

Prioritize the other applications running on the server and decide if any of the other processes can be migrated to another server, thereby freeing CPU resources to the SQL Server.

• Examine which database in the instance suffers from transaction log bottleneck, in the Activity tab.
• Examine the In MS-SQL breakdown of the applications, in the Activity tab.
• Examine the load on the devices holding the transaction logs, in the Statistics tab.
• Examine Log Flush counters, in the Statistics tab.

The transaction log is the file that experiences many writes and few reads. Every update to the data is eventually written to the transaction log. Since every database has its own transaction log, the first thing to do is to try to pinpoint the specific database.

If all of databases are experiencing a transaction log bottleneck, try examining the recovery interval (min) configuration.

When you have identified which database is experiencing a transaction log bottleneck, examine the applications using this database.

A bottleneck in the transaction log indicates that your application performs commits at a very high or very low frequency. This means that many records must be saved at the same time. The challenge is finding how many commits to do and how many records to save in each commit.

You can also consider moving the transaction logs to different and faster devices.

• Examine which database in the instance suffers from Internal Waits, in the Activity tab.
• Examine the Internal Wait category using the Internal Waits view, in the Activity tab.
• Examine a specific Internal Wait in the Statistics tab (SQL Server 2005 only).

An Internal Wait is divided into the following substates:

• Buffer Pool. Groups together the events that show contention on pages in the buffer pool. Buffer wait on tempdb pages are considered to be part of the tempdb wait.
• Latch. The session is waiting for an internal lock to be released.
• Parallel. The session is waiting for one of its sub-threads to complete its operation.
• DTC. Aggregates waits that occur when Distributed Transaction Coordinator (DTC) sessions have to wait for each other.
  This state is only available for SQL Server 2005 instances.
• DB Mirror. Aggregates the new waits that occur when DB mirroring is performed, such as the waits that occur if the communication layer used by DB mirroring becomes backlogged.
  This state is only available for SQL Server 2005 instances.
• Profiler. Aggregates a number of states associated with the SQL Profiler and lets you see how much of the database resources it consumes.
  This state is only available for SQL Server 2005 instances.
• Memory. Aggregates several types of waits that indicate that a session is waiting for memory to be allocated to it. This state is only available for SQL Server 2005 instances.
• Backup. Includes the wait type that commonly occurs when a Backup command is performed. This state is only available for SQL Server 2005 instances.
• Other Internal waits. Aggregates the following types of waits:
  
  • Full text waits. Includes wait types dedicated to the full text indexing service and appears whenever a full text index is in progress.
  • HTTP waits. Includes waits that occur when HTTP and SOAP operations are executing.
• Query notifications. Aggregates a number of states associated with the synchronization of Query Notification sessions.
  This state is only available for SQL Server 2005 instances.

Perform one of the following options:

• Examine the current tempdb content to find out which resource type is being consumed the most:
  
  • User temporary tables
  • Internal objects such as temporary system tables caused by Sort, Hash Join or Spooling operations.
• Examine the type of statements heavily using the tempdb resource in the Statements tab.
• Examine the Statistics to find out the number of files/tables creation number and rate.
• Examine the Tempdb Usage to find out open transactions preventing tempdb to shrink.

• Examine the type of statements heavily using the tempdb resource in the Statements tab.
  • Tune sorts.
  • 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.
  • Hash and Merge joins are temporary storage consumers. 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 min memory (MB) and index create memory (KB) instance parameters. Use the index create memory option to control the amount of memory used by index creation sorts. The index create memory option 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 min memory per query option.
• Tune Hash Joins:
  • Verify that there are no missing indexes.
  • Verify that there are adequate indexes.
  • Search for a missing 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.
• Tune Index Spool, Table Spool and Row Count Spool:
  • Spooling is 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.
• Tune row versioning. If a version store is not shrinking, it implies that a long-running transaction is preventing version store cleanup.
• Examine tempdb usage when the applications explicitly create temporary tables. Try creating indexes on those tables after they are populated.
• Examine the Tempdb Usage to find out open transactions preventing tempdb to shrink.

• Examine the high CPU usage statements.
• Examine the CPU usage to find the number of waiting for CPU tasks.
• Examine where the schedulers are not evenly loaded on the instance.
• Examine the overall CPU usage (using Insight OS) to find out if other processes occupy the CPUs.

• Examine the device activity over time and database files contention.
• Examine storage contention in Physical Disks, Devices and Files on the same raid group.
• Examine disks statistics.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage contention in Physical Disks, Devices and Files on the same raid group.
• Examine disks statistics.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage contention in Physical Disks, Devices and Files on the same raid group.
• Examine disks statistics.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage contention in Physical Disks, Devices and Files on the same raid group.
• Examine disks statistics.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage contention in Physical Disks, Devices and Files on the same raid group.
• Examine disks statistics.

• Compare the storage devices activity over time.
• Examine the storage devices statistics.

•  In the activity tab, check which database files are the most I/O consuming and spread them evenly across the storage devices.
• Consult with the storage administrator and check for other applications using the same storage devices or their underlying physical disks.
• Consult with the storage administrator about the RAID policy. A different striping may spread the I/O load across the storage devices.

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

• Examine the device activity over time and database files contention.
• Examine storage contention in Physical Disks, Devices and Files on the same raid group.
• Examine disks statistics.