Page History
...
Precise for SQL Server collects and displays performance data that enables quick and accurate performance monitoring and analysis. To understand the information displayed in the various graphs, tables and views, it is important to understand how information is collected.
About duration values
Precise for SQL Server displays duration values for several entities. The following table describes the duration value for each type of entity.
Table 3-1 Duration values for entities
| Entity |
|---|
...
| Description |
|---|
| Current |
...
| session | The time that has elapsed since the session started (login time). This is not the time the session was active executing statements. For example, if a session was connected to SQL Server for one hour without executing any statement, the duration will still be one hour. |
| Programs (such as, Logins, Machines, DB Users, Databases, time units) |
...
The elapsed time of the sessions executing the program.
|
...
The average duration counter displays the average time each program has been running. | |
| Statement (batch) |
...
The time that elapsed while the statement was executed. This is very similar to the duration of programs (see above); but since the statement is almost always active, this duration also indicates the time the statement was active. The average duration counter displays the average execution time of the statement. |
If Interpoint is installed (for SAP, PeopleSoft, Siebel or COM+), the duration and average duration fields have no significant meaning and can be ignored.
About session states
SQL Server provides reporting on many different types of events. Precise for SQL Server groups events according to logical relationships to enable you to easily pinpoint heavy resource consumers.
...
Precise for SQL Server also provides statistics about the waits encountered by threads that are in execution. This information is only available for SQL Server 2005 instances. Diagnosing these counters can provide important information on performance issues throughout the system.
About In MS-SQL states
The In MS-SQL session states signify that the session is performing activities in the SQL Server. The following table describes In MS-SQL session states.
Table 3-2 In MS-SQL session states
| Icon |
|---|
...
| In MS-SQL |
|---|
...
| State | Description | |
|---|---|---|
 | Using CPU | The session is currently executing and not waiting for any other event. Using CPU can indicate a performance issue if the value exceeds 90% of the In MS-SQL value. When SQL Server does read ahead, the session does not suffer from I/O wait, but may still show a great deal of CPU usage. |
 |
...
| I/O |
...
| Wait | The session is waiting for an I/O operation to be completed or terminated. I/O operation is much slower than CPU operation so it is very important to monitor whether an application is suffering from I/O wait. I/O can be easily resolved by reorganizing files across disks or by adding new disks to your system. |
...
 | Tempdb Wait | The |
...
session is waiting for an I/O operation or a buffer wait on tempdb pages. A |
...
high value may indicate a bottleneck in tempdb. |
...
Tempdb is the most heavily shared resource across the entire instance and can affect the performance of all applications. It is therefore important to monitor its usage. SQL Server 2005 uses tempdb for row versioning and online index builds, in addition to managing temporary objects, sorting and hashing. |
...
 | Lock Wait | The session is waiting for a lock to be released. The lock is held by another session. |
 |
...
| Redo Log Buffer |
...
| Wait | The session is waiting for an operation of the log file to terminate. This state is generally encountered during a commit or rollback operation. A high log wait value may indicate a problem with the commit frequency in the processes experiencing massive updates to data. |
...
 | Memory Wait | The |
...
| session is waiting for an internal resource to be freed. This state is divided into substates. |
...
 | Remote Wait | The |
...
| session is waiting for a remote query to terminate. |
...
| Some of the DBCC commands that check the database pages also create this type of wait. A remote resource can be either SQL Server and Oracle. |
...
 | CLR Wait | The session is currently performing a common language runtime (CLR) execution and is waiting for another CLR task or event to be initiated or completed. |
...
| All CLR-related waits are grouped together under this category. This state is only available for SQL Server 2005 instances. |
...
 | Buffer Wait | The |
...
| session is completing a network I/O operation. |
...
| A high value can occur if results from a large result set are transferred to the client using shared memory netlibs or TCP/IP. In this case it is important to verify that only required rows and columns are returned. |
About internal waits
The Internal Waits view displays internal waits, indicating that the session is waiting for an internal resource to be freed. An Internal Wait is divided into substates.
...