Page History
...
The following table describes the information displayed in the Instance Overview.
Table 5- 1 Instance Overview view
| View area | Description | ||
|---|---|---|---|
| Sessions |
| ||
| In MS-SQL (Last minute) | Displays the resource consumption breakdown of the instance during the last minute (that is, 60 samples of the Collector). In addition to the resource distribution, the view displays the number of sessions in each state during the last sample. It is important to remember that since this view only displays the last minute's activities, you will always be viewing the activities that occurred during the last minute and not the activities that occurred since the last refresh action. The following information is displayed:
|
See “About session states” on page 36.
...
The following table describes the information displayed in the Tempdb usage view.
Table 5- 2 Instance Tembdb usage view
| View Area | Description |
|---|---|
| Temporary tables | Displays the following information on temporary tables:
|
| Tempdb usage (Last second) | Displays statistical information on the tempdb database files within the last second. If the tempdb database contains more than one file, the displayed value is a total of all tempdb database files.
|
| Tempdb size | Provides a graphical representation of the components of the tempdb database size, broken down as follows:
|
About viewing Instance locks
...
The following table presents a detailed explanation of the Lock Tree and Lock Breakdown (Last Minute) mechanism.
Table 5- 3 Instance Locks view
| View Area | Description | ||
|---|---|---|---|
| Lock tree | It is important to determine the following information for each blocking chain:
The Lock Tree provides the necessary information to get to the root of the problem by providing information on sessions currently involved in the blocking situation. A yellow lock indicates that the session is currently locking other sessions but is not itself blocked. A red lock indicates the session is currently blocked by another session. Note that a session may also block other sessions while being blocked by itself. To gain a better understanding of the lock tree take a closer look at its structure. Notice that the session located in the root of each chain is the session that blocks all others. Each session directly blocks all sessions positioned one level beneath it. The Lock Tree displays the SPID, program name, the object being blocked and the lock type. | ||
| Lock breakdown (Last minute) | Displays the Lock Wait duration broken down into lock levels such as Table, Page and Key, for the selected instance, during the last minute:
|
See “About session states” on page 36.
...
Displays instance statistics for the last 30 minutes, such as CPU usage, memory paging and disk usage. The following table describes the information displayed in the Instance statistics view.
Table 5- 4 Instance Statistics view
| View Area | Description |
|---|---|
| Sessions | Displays the number of sessions connected to the instance, broken down into active and locked sessions, as follows:
|
| CPU usage | Displays statistics that report on CPU usage, broken down into SQL Server CPU usage and Non-SQL Server CPU usage. |
| Page faults | Displays statistics that report on page faults broken down into SQL Server Page Faults/sec and Non-SQL Server Page Faults/sec. |
| Load | Displays statistics that report on the instance load such as Batch Requests/sec, Transactions/sec and Logins/sec. |
| Disk operations | Displays statistics that report on I/O load, such as percentage of elapsed time the disks are busy servicing read/write requests. |
| Network packets | Displays statistics that report on network load, such as Max Packets Received/sec and Max Packets Sent/sec. |
About the Database entity
...
The following table describes the information displayed in the Database Overview view.
Table 5- 5 Database Overview view
| View Area | Description | ||
|---|---|---|---|
| Session | Displays the following session-related information:
| ||
| Statistics | Displays information on the following statistical parameters:
| ||
| In MS-SQL (Last minute) | Displays the resource consumption breakdown of the database during the last minute (that is, 60 samples of the Collector). In addition to the resource distribution, the view displays the number of sessions in each state during the last sample. It is important to remember that since this view only displays the last minute's activities, you will always be viewing the activities that occurred during the last minute and not the activities that occurred since the last refresh action. The following information is displayed:
|
See “About session states” on page 36.
...
The following table provides a detailed explanation of the Lock Tree and Lock Breakdown (Last Minute) mechanism.
Table 5- 6 Database Locks view
| View Area | Description | ||
|---|---|---|---|
| Lock tree | It is important to determine the following information for each blocking chain:
The Lock Tree provides the necessary information to get to the root of the problem by providing information on sessions currently involved in the blocking situation. A yellow lock indicates that the session is currently locking other sessions but is not itself blocked. A red lock indicates the session is currently blocked by another session. Note that a session may also block other sessions while being blocked by itself. To gain a better understanding of the lock tree take a closer look at its structure. Notice that the session located in the root of each chain is the session that blocks all others. Each session directly blocks all sessions positioned one level beneath it. The Lock Tree displays the SPID, program name, the object being blocked and the lock type. | ||
| Lock breakdown (Last minute) | Displays the Lock Wait duration broken down into lock levels such as Table, Page and Key, for the selected database, during the last minute:
|
See “About session states” on page 36.
...
The following table describes the information displayed in the session overview.
Table 5- 7 Session Overview
| View Area | Description | ||
|---|---|---|---|
| Session identifiers | Displays information on the session identifier's connection details, session-related statistics and a breakdown of session resource consumption, during the last minute.
| ||
| Connection | A connection includes session identifiers, such as, database, login, machine, user and work type, as reported by sysprocesses. In ERP systems (if the appropriate ERP extension is installed), Precise for SQL Server replaces some identifiers as follows: In SAP:
In PeopleSoft:
In Siebel:
In COM+:
| ||
| Statistics | Displays information on the following statistical parameters:
| ||
| In MS-SQL (Last minute) | Displays the resource consumption breakdown of the session during the last minute (that is, 60 samples of the Collector). In addition to the resource distribution, the view displays the number of sessions in each state during the last sample. It is important to remember that since this view only displays the last minute's activities, you will always be viewing the activities that occurred during the last minute and not the activities that occurred since the last refresh action. The following information is displayed:
See “About session states” on page 36. |
The following table describes the information displayed in the Session Locks view.
Table 5- 8 Session Locks view
| View Area | Description | ||
|---|---|---|---|
| Locks | Displays lock wait duration broken down into lock levels such as Table, Page and Key, for the selected Session, during the last minute. The sessions that are currently involved in a locking or blocking situation with the selected session are displayed in tree format. Since the Lock tree represents the locks that are currently occurring, and the lock breakdown represents the duration of locks occurring during the last minute, it is possible that a situation may occur whereby a lock was freed, but since the last minute has not yet passed, the lock tree will display the message ‘No locks found' while the Lock Breakdown grid still shows lock duration. The Lock tree provides the necessary information to determine the following information for each blocking chain. A yellow lock indicates that the session is currently locking other sessions but is not itself blocked. A red lock indicates the session is currently blocked by another session. Note that a session may also block other sessions while being blocked by itself. To gain a better understanding of the lock tree take a closer look at its structure. Notice that the session located in the root of each chain is the session that blocks all others. Each session directly blocks all sessions positioned one level beneath it. The Lock Tree displays the SPID, program name, the object being blocked and the lock type. The Lock breakdown (Last minute) displays the Lock Wait duration broken down into lock levels such as Table, Page and Key, for the selected session, during the last minute:
|
The following table describes the information displayed in the Session Text view.
Table 5- 9 Session Text view
| View Area | Description |
|---|---|
| Text | Displays the text of the current statement. If the session is not active and executing a statement, a message notifying you that the session is not active and that no text is available is displayed. |
About the Locked Object entity
...
The following table describes the information displayed in the Locked Object overview view.
Table 5- 10 Locked Object Overview view
| Column | Description |
|---|---|
| Database | Displays the database containing the blocked object. |
| Object | Displays the object being blocked. |
| Sessions | Displays the number of sessions that hold locks on the object or are blocked while trying to acquire a lock on the object. |
| Lock Type | Indicates the type of lock that is held against the object. |
You can display information on Locked Objects, in the Association area, when the selected entity is an instance or database. If an instance is selected, the Association area shows information on all currently locked objects in all the databases in the instance. If a database is selected, the table shows specific information on all currently locked objects in the selected database. Moving the cursor over the icon in the first column displays a ToolTip that identifies the object's lock type.
The following table describes the information displayed the Association area table.
Table 5- 11 Information displayed in Association area for Locked Objects
| Column | Description |
|---|---|
| Icon | Indicates if there is a session waiting for the object. |
| Object | Displays the object being blocked. |
| Sessions | Number of sessions that hold locks on the object or are blocked while trying to acquire a lock on the object. |
| Locks | Total number of locks, broken down into the following lock types: Exclusive, Update, Shared, Intent Exclusive, Intent Update, and Intent Shared. |
| Exclusive | Indicates how many of the locks placed on the sessions were exclusive locks and shows a breakdown of the lock modes into lock levels, such as, Table, Row, Key, and Page. When exclusive locks are placed on a resource, the holding session is granted exclusive access to that resource and it can perform modification operations. |
| Update | Indicates how many of the locks placed on the sessions were update locks and shows a breakdown of the lock modes into lock levels, such as, Table, Row, Key, and Page. Update locks are used to prevent deadlocks. A deadlock occurs when more than one session intends to update a resource and tries to lock the resource for future potential updates. The update lock is an interim stage which is promoted to an exclusive lock when the update is actually made. |
| Shared | Indicates how many of the locks placed on the sessions were shared locks and shows a breakdown of the lock modes into lock levels, such as, Table, Row, Key, and Page. When there are shared locks on an object, concurrent transactions can read but not modify the resource. |
| Intent Exclusive | Indicates how many of the locks placed on the sessions were intent exclusive locks and shows a breakdown of the lock modes into lock levels, such as, Table, Row, Key, and Page. Indicates that a transaction intends to modify some subordinate resources in the lock hierarchy by placing exclusive locks on those particular resources. |
| Intent Update | Indicates how many of the locks placed on the sessions were intent update locks and shows a breakdown of the lock modes into lock levels, such as, Table, Row, Key, and Page. Indicates that a transaction intends to update some subordinate resources in the lock hierarchy, at a later stage, by placing update locks on those particular resources. |
| Intent Shared | Indicates how many of the locks placed on the sessions were intent shared locks and shows a breakdown of the lock modes into lock levels, such as, Table, Row, Key, and Page. Indicates that a transaction intends to read but not modify some subordinate resources in the lock hierarchy by placing shared locks on those particular resources. |
About the Session Holding Locks entity
...
The following table describes the information displayed by the Session Holding Locks overview.
Table 5- 12 Sessions Holding Locks overview
| View Area | Description | ||
|---|---|---|---|
| Session identifies | Displays the following information on the session's identifiers:
See SQL Server Books Online for more information.
| ||
| Connection | A connection includes session identifiers, such as, database, login, machine, user and work type, as reported by sysprocesses. In ERP systems (if the appropriate ERP extension is installed), Precise for SQL Server replaces some identifiers as follows: In SAP:
In PeopleSoft:
In Siebel:
In COM+:
| ||
| Statistics | Displays information on the following statistical parameters:
| ||
| In MS-SQL (Last minute) | Displays the resource consumption breakdown of the session during the last minute (that is, 60 samples of the Collector). In addition to the resource distribution, the view displays the number of sessions in each state during the last sample. It is important to remember that since this view only displays the last minute's activities, you will always be viewing the activities that occurred during the last minute and not the activities that occurred since the last refresh action. The following information is displayed:
See “About session states” on page 36. |
About viewing information on the lock breakdown of a session that is holding locks
...
The Lock Tree displays the SPID, program name, the object being blocked and the lock type. The following table describes the information displayed in this view.
Table 5- 13 Locks view for the Session Holding Locks entity
| View Area | Description | ||
|---|---|---|---|
| Lock tree | The Lock tree provides the necessary information to determine the following information for each blocking chain:
| ||
| Lock breakdown (Last minute) | The Lock breakdown (Last minute) displays the Lock Wait duration broken down into lock levels such as Table, Page and Key, for the selected session, during the last minute:
|
About viewing the text of a session that is holding locks
...
The following table describes the information displayed in the Statement or Batch Overview view.
Table 5- 14 Statement or Batch Overview view
| View Area | Description |
|---|---|
| Statement/Batch ID | Displays the internal statement hash value (SHV) calculated by the Collector, or, in the case of a stored procedure, displays the name of the stored procedure. |
| Database | Identifies the database on which the statement or batch is run. This information is used by Precise for SQL Server to display the statement's execution plan. |
| User | Displays the parsing user who runs the statement or batch. This information is used by Precise for SQL Server to display the statement's execution plan. |
| Executions | Number of times the Collector identifies that the statement or batch was executed. |
| Duration | Total time required to execute the selected statement or batch during the last time slice. |
| Duration (Avg) | Average duration of each statement or batch execution during the last time slice. |
| CPU Time | Cumulative CPU time for the statement or batch during the last time slice. |
| Physical I/O Operations | Total number of physical I/O requests during the execution of the statement or batch during the last time slice. |
| Parallel Sessions (Min) | Minimum number of threads used to execute the statement or batch in parallel. This counter is calculated from the ECID column in sysprocesses. This enables you to verify that SQL Server is using the best execution plan for the current statement. |
| Parallel Sessions (Max) | Maximum number of threads used to execute the statement or batch in parallel. This counter is calculated from the ECID column in sysprocesses. This enables you to verify that SQL Server is using the best execution plan for the current statement. |
| In MS-SQL (Last minute) | Displays the resource consumption breakdown of the statement or batch during the last minute (that is, 60 samples of the Collector). In addition to the resource distribution, the view displays the number of sessions in each state during the last sample. It is important to remember that since this view only displays the last minute's activities, you will always be viewing the activities that occurred during the last minute and not the activities that occurred since the last refresh action. The following information is displayed:
|
About viewing current Statement and Batch text
...
- In the Instance list, choose the instance you want to analyze.
- On the View controls in the Main area, click Tempdb Usage and analyze Tempdb performance.
Precise. Performance intelligence from click to storage. Learn more > >
...