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
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
See “Analyzing locks currently held” on page 73.
| Anchor | ||||
|---|---|---|---|---|
|
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
The Association area provides corresponding information on the entities associated with the selected entity (displayed in the Main area). For example, you can associate to the Locked Objects related to the selected instance. The selection you make is reflected in the Association area only; the Main area remains unchanged. For some entities, tabs above the Association area enable you to view additional information. For example, the tabs displayed for a database entity are: General, In MS-SQL and Statistics. Clicking a tab displays different table columns with the same type of child entities.
From the Association area, you can also drill down to another entity by clicking a table row. A drilldown drill-down affects the entire tab. When you drill down to another entity, the Tab heading displays the new selection, the Main area displays information on the newly selected entity, and the Association area displays the entities associated with the selected entity.
...
See “About Precise for SQL Server tabs” on page 22.
| Anchor | ||||
|---|---|---|---|---|
|
The Current tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views.
...
- Instance
- Database
- 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 Instance Overview displays information on the activity of the instance, such as number of sessions and the resource consumption breakdown of the instance.
...
Table 5-1 Instance Overview view
View area Description
...
| 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 viewing Instance Tempdb usage
Displays information on the current usage of the tempdb database.
...
Table 5-2 Instance Tembdb usage view
View area Description
...
| View Area | Description |
|---|---|
| Temporary tables | Displays the following information on temporary tables: |
...
|
...
|
...
|
...
|
| Tempdb usage (Last second) |
...
Displays statistical information on the tempdb database files within the last second. If the tempdb database contains more than one file, the displayed value is a total of all tempdb database files. |
...
|
...
|
...
|
...
|
...
|
...
|
| Tempdb |
...
| size | Provides a graphical representation of the components of the tempdb database size, broken down as follows: |
...
|
...
|
...
|
...
|
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.
...
Table 5-3 Instance Locks view
View area Description
...
| 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 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 Statistics view
| View Area |
|---|
...
| Description |
|---|
View area Description
...
| Sessions | Displays the number of sessions connected to the instance, broken down into active and locked sessions, as follows: |
...
|
...
|
...
|
| 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 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. |
| Load |
...
| Displays statistics that report on the instance load such as Batch Requests/sec, Transactions/sec and Logins/sec. |
| Disk |
...
| operations | Displays statistics that report on I/O load, such as percentage of elapsed time the disks are busy servicing read/write requests. |
| 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
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.
...
Table 5-5 Database Overview view
View area Description
...
| 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 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.
...
Table 5-6 Database Locks view
View area Description
...
| 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 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 . A unique integer assigned to each user connection when the connection is made.
- Login time
- Session identifiers such as program, database user, or login
...
Table 5-7 Session Overview
View area Description
...
| 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.
...
Displays text of the selected statement or batch.
| Anchor | ||||
|---|---|---|---|---|
|
You can identify a performance problem by doing one or more of the following:
...