Page History
...
The following instance findings can help you tune your system:
- Locked Instance
- Tempdb Bottleneck
- Buffer Cache Is Too Small
- Other Applications Influence SQL Server (Memory)
- Other Applications Influence SQL Server (CPU)
- Transaction Log Bottleneck
- Extensive Internal Wait
- Tempdb Major Growth
- High CPU Wait
| Anchor | ||||
|---|---|---|---|---|
|
x% of the In MS-SQL time was spent waiting for locks. Regular locks can be categorized as follows:
...
■ 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.
| Anchor | ||||
|---|---|---|---|---|
|
The instance is experiencing a bottleneck of the tempdb database. x% of the In MS SQL time was spent waiting for tempdb. The tempdb database is used for temporary storage for sorting, joining, and, in SQL Server 2005, for row versioning.
...
■ Examine tempdb usage when the applications explicitly create temporary tables. Try creating indexes on those tables after they are populated.
| Anchor | ||||
|---|---|---|---|---|
|
A SQL Server instance is configured to work with a small buffer cache. Your applications are experiencing x% of the In MS-SQL time for I/O wait. The application’s overall performance is therefore not optimal.
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
A SQL Server instance experienced many page faults and lack of memory, while other processes on the same server did not experience any page faults problem.
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
A SQL Server instance experienced CPU shortage, as a result of other processes running on the server.
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
Some of the databases suffer from transaction log bottleneck. Log wait consumes x% of the In MS-SQL time.
...
You can also consider moving the transaction logs to different and faster devices.
| Anchor | ||||
|---|---|---|---|---|
|
The SQL Server instance has spent much of its time waiting for Internal Wait.
...
Aggregates a number of states associated with the synchronization of Query Notification sessions. This state is only available for SQL Server 2005 instances.
| Anchor | ||||
|---|---|---|---|---|
|
The tempdb database has experienced major growth. The tempdb database is used for temporary storage for sorting, joining, and, in SQL Server 2005, for row versioning.
...
■ Examine the Tempdb Usage to find out open transactions preventing tempdb to shrink.
| Anchor | ||||
|---|---|---|---|---|
|
The instance used SQL Server resources while waiting for CPU.
...