This section includes the following topics:

About the Current tab

The Current tab displays near real-time information on all sessions that were connected to your SQL Server database during the last minute. If you have discerned an immediate performance issue and are looking for an indication as to where to begin your analysis, start with the Current tab. It is also worthwhile to start with the Current tab if your system appears to be 'hung' and there is no throughput.

You can use this tab to monitor your system's current activities and resource consumption and to identify performance problems, such as current locking problems and sessions involved in locks, as they occur.

The Current tab provides answers to the following types of questions: "How many sessions are currently running?" or "Why doesn't my application work when I click OK?" The Current tab also shows you which sessions are currently active, and which machines are connected to which users.

Using the Current tab, you can monitor current connections to your SQL Server instance, check the state of each connection, and identify the resources currently utilized by each session. Further investigation of each session provides a thorough understanding of the SQL statement being executed, the login name executing the statement, the program, and the resource utilization for the session. In addition, you can view the statements and batches executed by the selected session within the last time slice.

If you are working with SAP, PeopleSoft, or Siebel, and ERP support is installed, the Current tab displays information on the current SQL Server activity in relation to the ERP application and users, and specific information on executed views and transactions.

In the Current tab, data on a current session is continuously updated to provide information on what happened during the last minute or time slice. Because the displayed session information is derived from a dynamic application, it is recommended to regularly refresh the screen, either manually or automatically, to ensure that the information displayed is up-to-date.

The Current tab displays information on the last minute of activity. For example, if you've worked at your screen for ten minutes and then refresh the information displayed on your screen, you will be able to view new information that occurred during the last minute of activity.

See Analyzing locks currently held.

How the Current tab is structured

The Current tab displays information on a selected entity and its associated entities. For example, if the parent entity is a Session, its associated entities would be Statements or Batches.

When you open the Current tab from the Dashboard tab, the selected entity is by default Instance, meaning that information is displayed on the instance level. When you open the Current tab from the Activity tab, the tab is launched with the selected instance or entity (in the case of an entity, this is only true of the database entity), meaning that the information is displayed on the instance or entity level. If you open the Current tab from another tab, the historical settings (meaning those settings which were selected when you left the tab, such as a drill-down to a Locked Objects entity) are taken into account and the information displayed the last time you viewed this tab is displayed (similar to clicking the History button and returning to a previous tab).

The selected entity is always reflected in the Tab heading, which serves as a point of orientation. The highest-level entity you can view information for in the Current tab is Instance. You can view another instance by selecting it from the Instance list.

About the Main Area in the Current tab

The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. For example, if you are analyzing an Instance entity, you can, focus exclusively on the data displayed in the overview, such as, In MS-SQL data (default), or choose to view performance statistics on the temporary database (Tempdb) in the SQL Server.

About the Association area in the Current tab

The Association area provides corresponding information on the entities associated with the selected entity (displayed in the Main area). For example, you can associate to the Locked Objects related to the selected instance. The selection you make is reflected in the Association area only; the Main area remains unchanged. For some entities, tabs above the Association area enable you to view additional information. For example, the tabs displayed for a database entity are: General, In MS-SQL and Statistics. Clicking a tab displays different table columns with the same type of child entities.

From the Association area, you can also drill down to another entity by clicking a table row. A drill-down affects the entire tab. When you drill down to another entity, the Tab heading displays the new selection, the Main area displays information on the newly selected entity, and the Association area displays the entities associated with the selected entity.

For example, when you want to view information on a specific session's holding locks, in the Current tab, choose Sessions Holding Locks from the Association controls. The Association area changes to display sessions holding locks-related information. Note that the Tab heading and the Main area remain unchanged.

If you want to view detailed information on a specific session holding lock, click the row of the sessions holding locks you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays locking and In MS-SQL information on the session you drilled down to, and the Association area shows information on the statements associated with this session.

See About Precise for SQL Server tabs.

About examining entities in the Current tab

The Current tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views.

The following entities can be examined in the Current tab:

  • Instance
  • Database
  • Session
  • Locked Object
  • Session Holding Locks
  • Statement and Batch
  • Active Session

About the Instance entity

The Instance entity displays information on the resource consumption of the entire instance and allows you to focus on how it is performing, during the last minute.

About getting an overview of current Instance activities

The Instance Overview displays information on the activity of the instance, such as number of sessions and the resource consumption breakdown of the instance.

The following table describes the information displayed in the Instance Overview.

Table 1 Instance Overview view

View areaDescription
Sessions
  • Sessions. Indicates the number of sessions (different SPIDs) that were connected to the SQL Server instance during the last sample.
  • Active Sessions. Number of sessions (different SPIDs) connected to the SQL Server instance during the last sample that were not in a state that is not part of the In MS-SQL group.
  • Locked Sessions. Number of blocked sessions in the last sample.
  • Memory Usage (Max). Displays the maximum memory (in MB) consumed by all sessions that were opened in SQL Server during the last 15 minutes.
  • Open Transactions (Max). Maximum number of transactions that were opened in SQL Server during the last 15 minutes.
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:

  • State. Displays the In MS-SQL state.
  • %. Percentage of time the selected entity was in this state.
  • In MS-SQL. Graphical representation of the Time column.
  • Time. Amount of time the selected entity was in this state.
  • Sessions (Last Sample). Current number of sessions in the specified state. For example, there are currently three sessions experiencing a Lock Wait state.

All the information displayed in this table represents information collected during the last minute, with the exception of the Sessions column, which represents the number of sessions, in the last sample, that were in a particular state.

See About session states.

About viewing Instance Tempdb usage

Displays information on the current usage of the tempdb database.

The following table describes the information displayed in the Tempdb usage view.

Table 2 Instance Tembdb usage view

View AreaDescription
Temporary tables

Displays the following information on temporary tables:

  • Active. Number of temporary tables that are currently used by any session.
  • Idle. Number of temporary tables that are currently not used by any session.
  • Local. Number of local temporary tables currently defined in the database.
  • Global. Number of global temporary tables currently defined in the database.
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.

  • Reads.Total number of reads that were executed in the tempdb database during the last second before the screen was refreshed.
  • Writes. Total number of writes that were executed in tempdb database during the last second before the screen was refreshed.
  • Bytes Read. Total number of bytes that were read during the last second before the screen was refreshed.
  • Bytes Written. Total number of bytes that were written in tempdb database during the last second before the screen was refreshed.
  • I/O Wait (MS). Total amount of time (in milliseconds) that sessions waited for I/Os to complete in tempdb database.
Tempdb size

Provides a graphical representation of the components of the tempdb database size, broken down as follows:

  • Active. Total size of all the temporary tables currently in use.
  • Idle. Total size of all the temporary tables that are currently not in use.
  • Free Space. Total free space available in the tempdb database.
  • Other Size. Space currently used by other tables/operations. For example, system tables defined on the tempdb, space used by a sort operation currently being executed, or raw versioning.

About viewing Instance locks

Displays lock wait duration broken down into lock levels such as Table, Page and Key, for the selected Instance, during the last minute. The sessions that are currently involved in a locking or blocking situation 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 following table presents a detailed explanation of the Lock Tree and Lock Breakdown (Last Minute) mechanism.

Table 3 Instance Locks view

View AreaDescription
Lock tree

It is important to determine the following information for each blocking chain:

  • Which sessions are involved in the lock?
  • Which session is blocking the others?
  • Which object is blocked?
  • What type of lock is causing the block?

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:

  • 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 instance was locked at the specified lock level.
  • %. Percentage of time the selected instance was locked at the specified lock level.
  • Sessions (Last Sample). Current number of sessions locked at the specified lock level.

The table only displays lock levels that the selected instance was blocked by. If the selected instance was not blocked, the information area remains empty.

See About session states.

About viewing Instance statistics

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 4 Instance Statistics view

View AreaDescription
Sessions

Displays the number of sessions connected to the instance, broken down into active and locked sessions, as follows:

  • Sessions. Number of sessions (different SPIDs) connected to the instance during the last sample.
  • Active sessions. Number of sessions (different SPIDs) that were in one of the In MS-SQL states during the last sample.
  • Locked sessions. Number of blocked session in the last sample.
CPU usageDisplays statistics that report on CPU usage, broken down into SQL Server CPU usage and Non-SQL Server CPU usage.
Page faultsDisplays statistics that report on page faults broken down into SQL Server Page Faults/sec and Non-SQL Server Page Faults/sec.
LoadDisplays statistics that report on the instance load such as Batch Requests/sec, Transactions/sec and Logins/sec.
Disk operationsDisplays statistics that report on I/O load, such as percentage of elapsed time the disks are busy servicing read/write requests.
Network packetsDisplays statistics that report on network load, such as Max Packets Received/sec and Max Packets Sent/sec.

About the Database entity

Displays information on the activity and resource consumption of the database, including the number of sessions connected to the database during the last sample taken, various session-related statistics and the database resource consumption breakdown for the last minute.

About getting an overview of current Database activities

Displays information on the activity and resource consumption of the database, including the number of sessions connected to the database during the last sample taken, various session-related statistics and the database resource consumption breakdown for the last minute.

The following table describes the information displayed in the Database Overview view.

Table 5 Database Overview view

View AreaDescription
Session

Displays the following session-related information:

  • Sessions. Number of sessions (different SPIDs) that were connected to the database during the last sample.
  • Active Sessions. Number of sessions (different SPIDs) that were in one of the In MS-SQL states during the last sample.
  • Locked Sessions. Number of blocked sessions connected to the database during the last sample.
Statistics

Displays information on the following statistical parameters:

  • Memory Usage (Max). Displays the maximum memory (in MB) consumed by all sessions that were connected to the selected database during the last 15 minutes.
  • Memory Usage (Total). Displays the total memory (in MB) consumed by all sessions that were opened in SQL Server during the last 15 minutes.
  • Open Transactions (Max). Displays the maximum transactions opened by all the sessions that were connected to the selected database during the last 15 minutes.
  • Open Transaction (Total). Displays the total transactions opened by all the sessions that were connected to the selected database during the last 15 minutes.
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:

  • State. Displays the In MS-SQL state.
  • In 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.
  • Sessions (Last Sample). Current number of sessions in the specified state. For example, there are currently three sessions experiencing a Lock Wait state.

All the information displayed in this table represents information collected during the last minute, with the exception of the Sessions column, which represents the number of sessions, in the last sample, that were in a particular state.

See About session states.

About viewing Database locks

Displays lock wait duration broken down into lock levels such as Table, Page and Key, for the selected Database, during the last minute. The sessions that are currently involved in a locking or blocking situation 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 following table provides a detailed explanation of the Lock Tree and Lock Breakdown (Last Minute) mechanism.

Table 6 Database Locks view

View AreaDescription
Lock tree

It is important to determine the following information for each blocking chain:

  • Which sessions are involved in the lock?
  • Which session is blocking the others?
  • Which object is blocked?
  • What type of lock is causing the block?

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:

  • 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 database was locked at the specified lock level.
  • %. Percentage of time the selected database was locked at the specified lock level.
  • Sessions (Last Sample). Current number of sessions locked at the specified lock level.

All the information displayed in this table represents information collected during the last minute, with the exception of the Sessions column, which represents the number of sessions, in the last sample, that were in a particular state.

See About session states.

About the Session entity

Displays information on a single connection with Microsoft SQL Server. Precise for SQL Server identifies a session by the following attributes:

  • System Process ID (SPID). A unique integer assigned to each user connection when the connection is made.
  • Login time
  • Session identifiers such as program, database user, or login

When one of these attributes changes, a new session is recorded and displayed.

The following table describes the information displayed in the session overview.

Table 7 Session Overview

View AreaDescription
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.

  • Session identifiers:
    • State. Current state of the session.
    • Status. Current status of the session as reported by SQL Server, such as:
      • Sleeping. Session is idle.
      • Runnable. SPID is currently executing.
      • Dormant. Same as Sleeping.
      • Rollback. Session is rolling back the transaction.
      • Defwakeup. Session is waiting for a resource that is in the process of being freed.
        The wait resource field should indicate the resource in question.
      • Spinloop. Session is waiting while attempting to acquire a spinlock used for concurrent control on Symmetric Multi-Processing (SMP) systems.
        See SQL Server Books Online for more information.
    • Login Time. Time the session started.
    • Last Batch Time. If the session is currently executing a batch this field displays the time the batch started; otherwise it displays the time the previous batch finished executing.
    • Duration. Time elapsed since login time.
    • Idle Time. Time elapsed since last batch.
    • Open Transactions. Number of open transactions for the SPID.
    • Parallel Sessions. Current number of subthreads used to execute a batch in parallel. This counter is calculated from the ecid column in sysprocesses. It enables you to verify that SQL Server uses the best execution plan for the current statement.
    • Blocked By. If the session is blocked by another session, displays the SPID of the blocker session.
    • Wait Type. An internal SQL Server field that signifies the current wait identifier, used by the Collector to assign a Session State.
    • Wait Time. If the session is waiting this field displays the current wait time in milliseconds.
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:

  • 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+
Statistics

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.
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:

  • 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.

All the information displayed in this table represents information collected during the last minute, with the exception of the Sessions column, which represents the number of sessions, in the last sample, that were in a particular state.

See About session states.

The following table describes the information displayed in the Session Locks view.

Table 8 Session Locks view

View AreaDescription
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:

  • 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.

The table only displays lock levels that the selected session was blocked by. If the selected session was not blocked, the information area remains empty.

The following table describes the information displayed in the Session Text view.

Table 9 Session Text view

View AreaDescription
TextDisplays 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 Overview view in the Main area displays information on the objects that are currently locked (such as, Database, Object, Sessions, and Lock Type).

The following table describes the information displayed in the Locked Object overview view.

Table 10 Locked Object Overview view

ColumnDescription
DatabaseDisplays the database containing the blocked object.
ObjectDisplays the object being blocked.
SessionsDisplays the number of sessions that hold locks on the object or are blocked while trying to acquire a lock on the object.
Lock TypeIndicates 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 11 Information displayed in Association area for Locked Objects

ColumnDescription
IconIndicates if there is a session waiting for the object.
ObjectDisplays the object being blocked.
SessionsNumber of sessions that hold locks on the object or are blocked while trying to acquire a lock on the object.
LocksTotal 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 fact that a currently active session is holding a resource does not necessarily mean that there is a contention between resources. The Session Holding Locks entity displays information that lets you differentiate between sessions that are locking resources and sessions that are holding resources.

The following views display information on sessions holding locks:

  • Overview
  • Locks
  • Text

About getting an overview of current sessions holding locks

Displays information on the session identifier, including connection details, session-related statistics and a breakdown of session resource consumption, during the last minute.

The following table describes the information displayed by the Session Holding Locks overview.

Table 12 Sessions Holding Locks overview

View AreaDescription
Session identifies

Displays the following information on the session's identifiers:

  • State. Current state of the session.
  • Status. Current status of the session as reported by SQL Server, such as:
    • Sleeping. Session is idle.
    • Runnable. SPID is currently executing.
    • Dormant. Same as Sleeping.
    • Rollback. Session is rolling back the transaction.
    • Defwakeup. Session is waiting for a resource that is in the process of being freed.
      The wait resource field should indicate the resource in question.
    • Spinloop. Session is waiting while attempting to acquire a spinlock used for concurrent control on Symmetric Multi-Processing (SMP) systems.

See SQL Server Books Online for more information.

  • Login Time. Time the session started.
  • Last Batch Time. If the session is currently executing a batch this field displays the time the batch started; otherwise it displays the time the previous batch finished executing.
  • Duration. Time elapsed since login time.
  • Open Transactions. Number of open transactions for the SPID.
  • Parallel Sessions. Current number of subthreads used to execute a batch in parallel. This counter is calculated from the ecid column in sysprocesses. It enables you to verify that SQL Server uses the best execution plan for the current statement.
  • Blocked by. If the session is blocked by another session, displays the SPID of the blocker session.
  • Wait Type. An internal SQL Server field that signifies the current wait identifier, used by the Collector to assign a Session State.
  • Wait Time. If the session is waiting this field displays the current wait time in milliseconds.
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:

  • 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+
Statistics

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.
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:

  • 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.

All the information displayed in this table represents information collected during the last minute, with the exception of the Sessions column, which represents the number of sessions, in the last sample, that were in a particular state.

See About session states.

About viewing information on the lock breakdown of a session that is holding locks

The Locks view 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.

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 following table describes the information displayed in this view.

Table 13 Locks view for the Session Holding Locks entity

View AreaDescription
Lock tree

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.
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:

  • 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.

The table only displays lock levels that the selected session was blocked by. If the selected session was not blocked, the information area remains empty.

About viewing the text of a session that is holding locks

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 Statement and Batch entities

The Statement and Batch entities display information on a SQL statement that was executed by the selected session during the last time slice.

About getting an overview of current Statement and Batch entities

Displays general information on the statement or batch, including overall properties, its breakdown In MS-SQL during the last time slice, and the breakdown of the locks during the last time slice.

When the selected entity is a Batch, the information area displays the entire text of the ad hoc batch or stored procedure.

When the selected entity is a Statement, the information area displays only the part of the batch text that forms the statement.

The following table describes the information displayed in the Statement or Batch Overview view.

Table 14 Statement or Batch Overview view

View AreaDescription
Statement/Batch IDDisplays 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.
DatabaseIdentifies 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.
UserDisplays 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.
ExecutionsNumber of times the Collector identifies that the statement or batch was executed.
DurationTotal 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 TimeCumulative CPU time for the statement or batch during the last time slice.
Physical I/O OperationsTotal 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:

  • State. Displays the In MS-SQL state.
  • In 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.

About viewing current Statement and Batch text

Displays text of the selected statement or batch.

How the Current tab can help you identify performance problems

You can identify a performance problem by doing one or more of the following:

Examining resource consumption of an entire instance and database

The Current tab provides an almost real-time picture of the performance and behavior of your SQL Server instance (or part of your instance). The Current tab displays information on the last time slice of activity, unless otherwise configured as for the last minute. Usually you will examine the activities of the entire instance level to determine what is happening in your system. By examining the current activity at an instance level of display, you can see how it is currently behaving and determine if there is an overall problem that affects all sessions or if there is a problem with specific sessions. For more information, see Examining contentions.

To examine resource consumption of an entire instance and database

  1. In the Instance list, choose the instance you want to analyze.
    Do one of the following:
    • On the View controls in the Main area, click Overview. This will show you a breakdown of resource consumption for the last minute.
    • On the View controls, click Statistics to analyze the current statistics of the selected instance.
  2. Launch to the Statistics tab, in-context, to view additional statistical information on the selected instance.

Observing current session activity

The Association area of the Current tab lets you view which sessions are currently connected to the Instance, or which sessions are currently using a specific database or holding a specific object. You can observe the current state of the session and determine if it is active or waiting for a resource. You can also identify the session by different identifiers such as its login information, the machine it is running on, or the programs it is running. The currently active TSQL code is displayed for active sessions.

To observe current session activity

  1. In the Instance list, choose the instance you want to analyze.
  2. On the Association controls in the Association area, select one of the following to focus your investigation on the sessions that were active in SQL Server during the last minute:
    • Active Sessions
    • Sessions
    • Sessions Holding Locks

Examining a single connection

You may need to explore a single running SPID, to obtain more information on it. In the Association area you can drill down to the specific SPID in question, and continue your analysis.

To examine a single connection

  1. In the Instance list, choose the instance you want to analyze.
  2. In the Association area, drill down to the session whose connection you want to analyze.
  3. Continue your analysis by examining its resource breakdown, its recent statements, or the batches in which the session was executed, during the last few minutes.
  4. On the View controls, click Locks and observe which locks the session is holding or waiting for.

Viewing ERP data

Certain obstacles make it difficult to investigate ERP sessions. Most activity is registered in SQL Server under generic names. For example, in PeopleSoft®, all PeopleSoft activities are listed under a single login (sa) and generic program names (PeopleSoft).

Installing the Precise for SQL Server Interpoint extension enables you to correlate SQL Server information with ERP information. For example, if you install Interpoint for Siebel, you will be able to correlate between SQL Server sessions to Siebel.

To view ERP data

  1. Verify that the Precise for SQL Server Interpoint extension is installed. For more information, see Installation and Administration.
  2. In the Instance list, choose the instance you want to analyze. For this example, you are viewing Siebel session data.
  3. In the Association area, view Siebel session data for each session in the selected instance and observe to which SQL Server session it is correlated to.

Examining contentions

One of the common uses of the Current tab is to solve contentions. A contention is a situation in which a session waits for a resource held by another session. In a busy application, there can be situations in which one session locks many other sessions that are waiting for the resource. For example, every time a user logs on to the system the Logon table is updated. If the system is not properly tuned a situation could arise where while the Logon table is being held by another session no user could log in into the system. This type of scenario can be resolved very easily using Precise for SQL Server. You can observe all the locks on the instance, in the Locks view of the Instance entity.

To examine contentions

  1. In the Instance list, choose the instance you want to analyze.
  2. On the View controls in the Main area, click Locks and examine the locking contentions.
  3. Check which sessions and programs are holding the locks and locate which objects are involved in contentions.
  4. In the Association area, on the Locks tab, observe the status of the locking session and the type of lock (for example, table lock or page lock). See Examining locking over time of a database entity.

Analyzing locks currently held

To be able to avoid a blocking situation, it is important to have an accurate picture of all the locks held in the system. This will help you understand if the lock system is tuned and if it is possible to avoid a potential blocking situation. Using Precise for SQL Server, you can now observe the locks currently held in the Instance and group them according to lock level and lock mode.

To analyze locks currently held

  1. In the Instance list, choose the item you want to analyze.
  2. On the View controls in the Main area, click Locks and examine the locking contentions. Move the cursor over the locked session in the tree to display the text of the locked session.
  3. On the Association controls, in the Association area, click Locked Objects, and examine the locks in SQL Server. Identify which objects have many locks held on them, which objects are in use, and how they are being held.
  4. Examine all the locks in the instance shows which objects are in use (there is a lock on the object) and how they being held. As you can see the table is involved in a contention with other sessions.
  5. If you find an object that is locked, examine the sessions that are holding locks on the object by drilling down to the object.
  6. On the View controls in the Main area, click Overview and view the lock type.
  7. After drilling down to the object, on the Association controls in the Association area, click Sessions to find the sessions that hold locks on the object.

Monitoring tempdb usage

Exploring Tempdb usage is very important during the tuning and monitoring process. Even a well-tuned application can suddenly suffer from a degradation in response time, due to a session that was busy with the Tempdb database. It is therefore important to monitor Tempdb performance.

To monitor Tempdb usage

  1. In the Instance list, choose the instance you want to analyze.
  2. On the View controls in the Main area, click Tempdb Usage and analyze Tempdb performance.


IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal