Page History
...
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 | ||||
---|---|---|---|---|
|
...
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 | ||||
---|---|---|---|---|
|
...
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 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:
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 36See About session states.
About viewing Instance statistics
...
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 36See About session states.
About viewing Database locks
...
Info |
---|
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 , "No locks found' " while the Lock Breakdown grid still shows lock duration. |
...
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 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.
| ||
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 36See About session states. |
The following table describes the information displayed in the 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:
|
...
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. |
...
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 36See About session states. |
About viewing information on the lock breakdown of a session that is holding locks
...
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 , "No locks found' " while the Lock Breakdown grid still shows lock duration.
...
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:
|
...
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.
See “Examining contentions” on page 73.
. For more information, see Examining contentions.
To To examine resource consumption of an entire instance and database
...
- Verify that the Precise for SQL Server Interpoint extension is installed. For more information, see the Precise Installation Guidesee Installation and Administration.
- In the Instance list, choose the instance you want to analyze. For this example, you are viewing Siebel session data.
- 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.
...
- 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 Examining locking over time of a database entity” on page 101entity.
Anchor | ||||
---|---|---|---|---|
|
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.
...
- 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.