Page History
...
Indicates that a transaction intends to modify some subordinate resources in the lock hierarchy by placing exclusive locks on those particular resources.
Table 5-11 Information 11 Information displayed in Association area for Locked Objects
...
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
■ Overview
■ Locks
■ Text
- 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 5-12 Sessions 12 Sessions Holding Locks overview
View area Description
...
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.
...
■ Machine -> User Application
In PeopleSoft:
■ Login -> PS User ID
■ Work Type -> Interactive, Batch
In Siebel:
■ Login -> Siebel User
...
■ Work Type -> Interactive, Batch, Background
In COM+:
■ Work Type -> COM+
Table 5-12 Sessions Holding Locks overview
View area Description
Statistics Displays information on the following statistical parameters:
...
■ %—percentage of time the selected entity was in this state.
Note: 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 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 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 13 Locks view for the Session Holding Locks entity
...
■ 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.
■
Table 5-13 Locks view for the Session Holding Locks entity
View area Description
Lock breakdown (Last Minute) The Lock breakdown (Last 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:
...
■ %—Percentage of time the selected session was locked at the specified lock level.
Note: 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
...
The following table describes the information displayed in the Statement or Batch Overview view.
Table 5-14 Statement 14 Statement or Batch Overview view
View area Description
...
Physical I/O Operations Total number of physical I/O requests during the execution of the statement or batch during the last time slice.
Table 5-14 Statement or Batch Overview view
View area Description
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.
...
■ %—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
...
- Observing current session activity
...
- Examining a single connection
...
- Viewing ERP data
...
- Examining contentions
...
- Monitoring tempdb usage
Examining resource consumption of an entire instance and database
...
To examine resource consumption of an entire instance and database
...
- 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.
- consumption for the last minute.
...
- Launch to the Statistics tab, in-context, to view additional statistical information on the selected instance.
Observing current session activity
...
To observe current session activity
...
- In the Instance list, choose the instance you want to analyze.
...
- 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
...
To examine a single connection
...
- In the Instance list, choose the instance you want to analyze.
...
- In the Association area, drill down to the session whose connection you want to analyze.
...
- 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.
...
- 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
...
- Verify that the Precise for SQL Server Interpoint extension is installed.
...
- For more information, see the Precise Installation Guide.
...
- 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.
...
...
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
...
- 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.
...
...
Analyzing locks currently held
...
To analyze locks currently held
...
- In the Instance list, choose the item you want to analyze.
...
- 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.
...
- 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.
...
- 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.
...
- If you find an object that is locked, examine the sessions that are holding locks on the object by drilling down to the object.
...
- On the View controls in the Main area, click Overview and view the lock type.
...
- 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
...
- 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 > >
...