Page History
...
Table 3-3 Internal wait states
Internal State Description
...
| Internal State | Description |
|---|---|
| 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 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: |
...
|
...
|
...
|
...
|
...
|
...
This state is only available for SQL Server 2005 instances. |
About lock waits
The Locking view displays a breakdown of lock waits, indicating that the session is waiting for an application lock to be freed. Lock waits are divided into substates.
Table 3-4 Lock wait states
Lock Wait Description
...
| Lock Wait | Description |
|---|---|
| Row lock | The lock is being held on a row in a heap table. |
| Key |
...
| lock | The lock is being held on a key in an index or a row in a clustered index. |
| Page |
...
| lock | The lock is being held on a page. |
| Table |
...
| lock | The lock is being held on the entire table. |
| Other |
...
| lock | Includes locks on database, extents and files. |
| MD statistics |
...
| lock | Indicates a metadata lock is being held on the statistics of a table or index. |
| MD partition |
...
| lock | Indicates a metadata lock is being held on a partition function or partition schema. |
| MD other |
...
| lock | Indicates a general metadata lock is being held. |
About Non-In MS-SQL states
The Non-In MS-SQL session states signify activities performed outside of the SQL Server. The following table describes Non-In MS-SQL session states.
Table 3-5 Non-In MS-SQL session states
Icon State Description
...
| Icon | State | Description |
|---|---|---|
 | Request Wait | The session is waiting for the client to issue a request. A high value can indicate that a connection pool is too large and that many connections have been left open and are not active. |
 |
...
| Parallel Exchange Wait | The thread in a parallel session is waiting for data exchange from another thread. |
...
 | Idle | Indicates that the session is waiting for something other than a user request or is executing the WAITFOR DELAY command. |
About Statement identifiers
Each statement is identified by the following parameters:
...
For example, the statement "select * from sysobjects" executed in the master and msdb databases is represented by two different statements in the product. The ability to roll up the performance of those statements to one is also important. This is supplied using other identifiers such as the program entity.
How Precise for SQL Server monitors availability
The Precise for SQL Server Collector monitors SQL server every second and collects data regarding resource consumption. If the instance status (Up/Down) was changed, a message code is sent by the Collector and causes the current availability of the instance saved in the PMDB to be changed. In addition, the availability of the instance is saved, over time, in the PMDB. During a planned downtime period the instance availability is showed as it was available during this period.Note:
| Info |
|---|
If the Precise for SQL Server Collector is down, the instance availability status reported is "Unknown". |
| Anchor | ||||
|---|---|---|---|---|
|
...