Page History
...
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
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
- Launch to the Statistics tab, in-context, to view additional statistical information on the selected instance.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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).
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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 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 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
...