Page History
This section includes the following topics:
...
- About the Current tab
...
- How the Current tab is structured
...
- About examining entities in the Current tab
...
- How the Current tab can help you identify performance problems
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.
...
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.
...
| Info |
|---|
...
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.
...
| 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 locks currently held” on page 73.
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.
...
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
...
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” on page 22.
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
■ Database
■ Session
■ Locked Object
■ Session Holding Locks
■ Statement and Batch
■ Active Session
- 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 following table describes the information displayed in the Instance Overview.
Table 5-1 Instance 1 Instance Overview view
View area Description
...
■ Open Transactions (Max)—Maximum number of transactions that were opened in SQL Server during the last 15 minutes.
...
■ Sessions (Last Sample)—Current number of sessions in the specified state. For example, there are currently three sessions experiencing a Lock Wait 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 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 5-2 Instance 2 Instance Tembdb usage view
View area Description
...
■ 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.
| 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 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 5-3 Instance 3 Instance Locks view
View area Description
...
■ 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.
...
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:
...
Note: 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 36.
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 5-4 Instance 4 Instance Statistics view
View area Description
...
■ Active sessions—number of sessions (different SPIDs) that were in one of the In MS-SQL states during the last sample.
...
CPU usage Displays statistics that report on CPU usage, broken down into SQL Server CPU usage and Non-SQL Server CPU usage.
Table 5-4 Instance 4 Instance Statistics view
View area Description
Page faults Displays statistics that report on page faults broken down into SQL Server Page Faults/sec and Non-SQL Server Page Faults/sec.
...
Network packets Displays 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
...
The following table describes the information displayed in the Database Overview view.
Table 5-5 Database 5 Database Overview view
View area Description
...
■ Active Sessions—number of sessions (different SPIDs) that were in one of the In MS-SQL states during the last sample.
...
■ Open Transaction (Total)—displays the total transactions opened by all the sessions that were connected to the selected database during the last 15 minutes.
Table 5-5 Database Overview view
View area Description
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.
...
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 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.
| 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 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 5-6 Database 6 Database Locks view
View area Description
...
■ 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.
...
The Lock Tree displays the SPID, program name, the object being blocked and the lock type.
Table 5-6 Database Locks view
View area Description
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:
...
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 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 5-7 Session 7 Session Overview
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:
...
See SQL Server Books Online for more information.
Login Time—time the session started.
...
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.
...
Wait Time—if the session is waiting this field displays the current wait time in milliseconds.
Table 5-7 Session Overview
View area Description
Connection A connection includes session identifiers, such as, database, login, machine, user and work type, as reported by sysprocesses.
...
■ 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+
...
■ %—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.
The following table describes the information displayed in the Session Locks view.
Table 5-8 Session 8 Session Locks view
View area Description
...
■ %—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.
The following table describes the information displayed in the Session Text view.
Table 5-9 Session 9 Session Text view
View area Description
Text 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 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 5-10 Locked 10 Locked Object Overview view
Column Description
...
Lock Type Indicates 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 5-11 Information 11 Information displayed in Association area for Locked Objects
...
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 displayed in Association area for Locked Objects
...