...
| Info |
|---|
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 Analyzing locks currently held” on page 73held.
| Anchor |
|---|
| HowtheCurrenttabisstructured |
|---|
| HowtheCurrenttabisstructured |
|---|
|
How the Current tab is structured
...
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 About Precise for SQL Server tabs” on page 22tabs.
| Anchor |
|---|
| AboutexaminingentitiesintheCurrenttab |
|---|
| AboutexaminingentitiesintheCurrenttab |
|---|
|
About examining entities in the Current tab
...
| View area | Description |
|---|
| 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.
| Info |
|---|
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” on page 36 About session states.
About viewing Instance Tempdb usage
...
| View Area | Description |
|---|
| 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.
| Info |
|---|
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” on page 36See About session states.
About viewing Instance statistics
...
| View Area | Description |
|---|
| 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.
| Info |
|---|
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” on page 36See About session states.
About viewing Database locks
...
| View Area | Description |
|---|
| 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.
| Info |
|---|
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” on page 36See About session states.
About the Session entity
...
| 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. - 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 waitresource 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+: |
| 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.
| Info |
|---|
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” on page 36See About session states. |
The following table describes the information displayed in the Session Locks view.
...
| View Area | Description |
|---|
| 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 waitresource 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+: |
| 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.
| Info |
|---|
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” on page 36See About session states. |
About viewing information on the lock breakdown of a session that is holding locks
...
- In the Instance list, choose the instance you want to analyze.
- On the View controls in the Main area, click Locks and examine the locking contentions.
- Check which sessions and programs are holding the locks and locate which objects are involved in contentions.
- 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” on page 101.
| Anchor |
|---|
| Analyzinglockscurrentlyheld |
|---|
| Analyzinglockscurrentlyheld |
|---|
|
Analyzing locks currently heldTo 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.
...