Page History

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:

• Login -> SAP User

• Work Type -> Dialog, Update, Spool, Background, Enqueue

• Machine -> User Application

In PeopleSoft:

• Login -> PS User ID

• Work Type -> Interactive, Batch

In Siebel:

• Login -> Siebel User

• Program -> Siebel View

• Machine -> Siebel Server

• Work Type -> Interactive, Batch, Background

In COM+:

• Work Type -> COM+

Displays information on the following statistical parameters:

• CPU Time. CPU time for the sessions during the last minute.

• Physical I/O

• Operations. Total number of physical I/O requests performed by the session during the last minute.

• Memory Usage. Number of pages in the procedure cache that are currently allocated to the SPID. A negative number indicates that the process is freeing memory allocated by another process.

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:

• State. Displays the In MS-SQL state.

• MS-SQL. Graphical representation of the Time column.

• Time. Amount of time the selected entity was in this state.

• %. Percentage of time the selected entity was in this state.

See “About session states” on page 36.

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:

• Lock Level. Such as, Row, Key, Page, Table, or Other. “Other” includes all other locks.

• Lock Wait. A graphical representation of the data displayed in the Time column.

• Time. Amount of time the selected session was locked at the specified lock level.

• %. Percentage of time the selected session was locked at the specified lock level.

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.

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.

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.

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.

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.

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.