Page History
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
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. |
| Anchor | ||||
|---|---|---|---|---|
|
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.
The following table describes the internal wait substates reported by Precise for SQL Server.
Table 3- 3 Internal wait states
| 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 |
|---|---|
| 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 |
|---|---|---|
 | 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. |
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Info |
|---|
If the Precise for SQL Server Collector is down, the instance availability status reported is "Unknown.". |
| Anchor | ||||
|---|---|---|---|---|
|
...
The Activity tab can also be referred to when planing planning your system's capacity, performing period-to-period comparisons, and determining how best to optimize storage. Use the Activity tab to examine resource consumption of your SQL Server instance, over time, as well as the resource consumption of your databases, programs, logins, and so on. You can also identify dominant resource consumption by selecting the Overview view.
...
As you can see schema changes, volume changes, and database option changes, were made to objects referred by this statement. By examining the time the changes were reported and when the statement's performance began to deteriorate, you can locate the changes that may have been the cause of the degradation in statement performance. In the Association area you can view additional details regarding the changes and you can launch from a specific change to another tab to further your investigation. (For example: by clicking on the “Launch icon” Launch icon of “Instance Instance parameter changes” Changes you will launch to Object tab while displaying the Instance Parameters Changes” Changes in the Main Area.
About recommending indexes for an SQL statement
...
The Statistics tab provides many performance counters grouped into several predefined overtime graphs that enable you to locate various aspects of performance problems, such as CPU, paging, I/O, and network. The Statistics tab can be used to monitor your system's current state as well as previous states. It is also possible to monitor a specific counter associated with a particular performance group by selecting the required counter.
Precise. Performance intelligence from click to storage. Learn more > >
...
...