Page History

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.