Page History
...
| Column | Description |
|---|---|
 | Click to launch the SQL tab with the selected entity, to continue your analysis of the selected statement or batch. When launching to the SQL tab from a batch entity, the launch is performed in the context of the batch that has the latest explain statement performed on it. |
| Entity Name | Displays the name of the entity (statement or batch). |
| In MS-SQL | |
| % | |
| Statement Executions | |
| Statement Duration (Avg) | |
| Text |
Entity Name
In MS-SQL
...
| Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown, over the selected time period. | |
| % | Displays the percentage of the total In MS-SQL. |
| Statement |
...
| Executions | Number of statements executed within the selected time period by the selected entity. |
| Statement Duration (Avg) |
...
| Average duration for a single statement, in the selected entity, during the selected time period, calculated by dividing the Statement Duration by the number of executions. | |
| Text |
...
| Displays the statement's text (formatted for easy readability). |
About viewing In MS-SQL information for Statement and Batch entities
The In MS-SQL tab contains the In MS-SQL counter and the breakdown of the In MS-SQL into its states. The In MS-SQL is displayed with a normalized view while all the other counters display the time each state was consumed.
About viewing Statistics information for Statement and Batch entities
The following table describes the additional information displayed in the Statistics tab in the Association area.
Table 6-6 Information displayed in the Statistics tab
| Column | Description |
|---|---|
Column Description
...
| Click to launch the SQL tab with the selected entity, to continue your analysis of the selected statement or batch. |
| Entity Name (such as Statement) |
...
| Displays the name of the entity. | |
| CPU |
...
| Time | Cumulative CPU time for the entity during the selected time period. |
| Physical I/O |
...
| Operations | Total number of physical I/O requests performed by the selected entity during the selected time period. |
| 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. | |
| Parallel Sessions (Max) |
...
| Maximum number of threads used to execute the statement or batch in parallel. This counter is calculated from the <command>ecid</command> column in sysprocesses. This enables you to verify that SQL Server is using the best execution plan for the current statement. |
About viewing Procedure Cache information for Statement and Batch entities
The Procedure Cache is the part of the SQL Server memory pool that stores execution plans for batches, stored procedures and triggers. Detailed information on the execution plan run of a specific statement, collapsed statement, batch or collapsed batch is displayed in the Procedure Cache tab.
...
Table 6-7 Information displayed in the Procedure Cache tab
| Column | |
|---|---|
Column Description
...
| Description |
|---|
Entity name (such as Instance, Database) |
...
| Displays the name of the entity. | |
| Elapsed |
...
| Time | Amount of time it took to complete the executions of the plan. |
| Execution |
...
| Count | Number of times the plan was executed. |
| Avg |
...
| Duration | Average amount of time it took to execute the plan, calculated as total elapsed time divided by the execution count. |
| Recompilations |
...
| Number of times a plan was recompiled while it remained in the procedure cache. |
| CPU |
...
| Time | Amount of CPU time consumed by the executions of the plan. |
| CLR |
...
| Time | Time consumed inside CLR objects by the executions of the plan. |
| Logical I/ |
...
| O | Number of logical reads and writes performed by the executions of the plan. |
| Physical |
...
| Reads | Number of physical reads performed by the executions of the plan. |
About viewing Plan information for Statement and Batch entities
The following table describes the additional information displayed in the Plan tab in the Association area.
Table 6-8 Information displayed in the Plan tab
| Column | Description |
|---|---|
Columns Description
...
| Click to launch the SQL tab with the selected entity, to continue your analysis of the selected statement or batch. Explain data for the statement will be in extrapolated context to the batch. |
| Entity Name ( |
...
| Such as Statement) |
...
| Displays the name of the entity. |
...
| Database | Identifies the database on which the statement or batch is run. |
...
| User | Displays one of the SQL Server users by whom this statement was executed. If this statement is not part of a stored procedure, this is the user that will be used as the parsing user when explaining this batch. |
| Most Recent |
...
| Plan | Indicates the last time the access path of the statement or batch was changed. |
| Last Show |
...
| Plan | Indicates the last time the statement or batch was explained. |
| Cost |
...
| Indicates the total estimated cost of the statement's or batch's execution. |
| Table |
...
| Scan | Indicates whether a table scan was performed in the execution plan (applicable to Statement entity only). |
| Clustered Index |
...
| Scan | Indicates whether a clustered index scan was performed in the execution plan (applicable to Statement entity only). |
...
| clustered Index Seek | Indicates whether a clustered index seek was performed in the execution plan (applicable to Statement entity only). |
| Index |
...
| Scan | Indicates whether an index scan was performed in the execution plan (applicable to Statement entity only). |
| Index |
...
| Seek | Indicates whether an index seek was performed in the execution plan (applicable to Statement entity only). |
| Bookmark |
...
| Indicates whether a bookmark operation was performed in the execution plan (applicable to Statement entity only). |
| Sort |
...
| Indicates whether a sort operation was performed in the execution plan (applicable to Statement entity only). |
| Merge |
...
| Join | Indicates whether a merge join operation was performed in the execution plan (applicable to Statement entity only). |
| Hash |
...
| Join | Indicates whether a hash join operation was performed in the execution plan (applicable to Statement entity only). |
...
| Nestled Loops Join | Indicates whether a nested loops join operation was performed in the execution plan (applicable to Statement entity only). |
| Parallel |
...
| Access | Indicates whether a parallel access operation was performed in the execution plan (applicable to Statement entity only). |
| Show Plan |
...
| Error | Shows the error that occurred during the last explain process. |
About the Collapsed statement and collapsed batch entities Association area
The following tabs are displayed in the Association area of the Collapsed Statement and Collapsed Batch entities:
...
Clicking a tab shows additional information on the entities displayed in the Association area table. The rows of the table remain the same; the columns change to display additional details on the entities listed in the table. The information displayed in each tab is described below.
About viewing performance data for Collapsed Statement and Collapsed Batch entities
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 6-9 Information displayed in the Performance tab
| Column | Description |
|---|---|
Column Description
...
| Entity Name | Displays the name of the Collapsed Statement or Collapsed Batch. |
| In MS- |
...
| SQL | Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown, over the selected time period. |
| % |
...
| Displays the percentage of the total In MS-SQL. |
| Occurrences |
...
| Number of different statements possessing the selected collapsed form. |
| Statement |
...
| Executions | Number of statements executed within the selected time period by the selected entity. |
| Statement Duration (Avg) |
...
| Average duration for a single statement, in the selected entity, during the selected time period, calculated by dividing the Statement Duration by the number of executions. | |
| Text |
...
| Displays the statement or batch text (formatted for easy readability), in the collapsed form. |
About viewing In MS-SQL data for Collapsed Statement and Collapsed Batch entities
This tab contains the In MS-SQL counter and the breakdown of the In MS-SQL into its states. The In MS-SQL is displayed with a normalized view while all the other counters display the time each state was consumed.
About viewing Statistics data for Collapsed Statement and Collapsed Batch entities
The following table describes the additional information displayed in the Statistics tab in the Association area.
Table 6-10 Information displayed in the Statistics tab
| Column | |
|---|---|
Column Description
| Description |
|---|
| Entity Name (such as Collapsed Statement) |
...
| Displays the name of the entity. | |
| Database |
...
| Identifies the database on which the collapsed statement or batch is run. |
| CPU |
...
| Time | Cumulative CPU time for the entity during the selected time period. |
| Physical I/O |
...
| Operations | Total number of physical I/O requests performed by the selected entity over the selected time period. |
| 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. | |
| Parallel Sessions (Max) |
...
| Maximum 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. |
About viewing Procedure Cache data for Collapsed Statement and Collapsed Batch entities
The Procedure Cache is the part of the SQL Server memory pool that stores execution plans for batches, stored procedures and triggers. Detailed information on the execution plan run of a specific instance is displayed in the Procedure Cache tab.
The following table describes the information displayed for execution plans in the Procedure Cache tab.
Table 6-11 Information displayed in the Procedure Cache tab
...
.
The following table describes the information displayed for execution plans in the Procedure Cache tab.
Table 6-11 Information displayed in the Procedure Cache tab
| Column | Description |
|---|---|
| Entity name (such as Instance, Database) |
...
| Displays the name of the entity. | |
| Elapsed |
...
| Time | Amount of time it took to complete the executions of the plan. |
| Execution |
...
| Count | Number of times the plan was executed. |
| Avg |
...
| Duration | Average amount of time it took to execute the plan, calculated as total elapsed time divided by the execution count. |
| Recompilations |
...
| Number of times a plan was recompiled while it remained in the procedure cache. |
| CPU |
...
| Time | Amount of CPU time consumed by the executions of the plan. |
| CLR |
...
| Time | Time consumed inside CLR objects by the executions of the plan. |
| Logical I/ |
...
| O | Number of logical reads and writes performed by the executions of the plan. |
| Physical |
...
| Reads | Number of physical reads performed by the executions of the plan. |
About Session and Blocker Session entities
The Session entity displays performance, statistics and locking information on the selected session. This entity differs from other entities in the Activity tab in that session information is retrieved from the Collector's temporary files and not from the PMDB.
...
- Overview
- In MS-SQL
- Locking
- Internal Waits
- Overall Activity
About getting an overview of Session and Blocker Session entities
The Overview displays general details on the selected session or blocker session, such as, Machine, Login, and Client Process ID. A breakdown of the selected session's or blocker session's resource consumption breakdown is also displayed.
About viewing In MS-SQL data for Session and Blocker Session entities
The In MS-SQL view displays the resource consumption of the selected session over the selected time period. This graph enables you to analyze performance trends for the session, over time.
| Info |
|---|
...
The graph displays data in time slices. |
See “About session states” on page 36 and “Focusing on information in overtime graphs” on page 32.
About viewing Session and Blocker Session Locking
The Locking view displays the blocking tree and lock wait duration broken down into lock levels (such as, Table, Page, and Key), for the selected session, within the specified time period. The following information is displayed:Locks Displays
- Locks. Displays which sessions were involved in a blocking situation (either locking or blocking). It is important to understand which sessions are involved in each blocking chain, as there may be several sessions involved at the same time. It is then important to determine which session is blocking others, which object is being blocked, and what type of lock has been applied.
A yellow lock indicates that the session is currently blocking 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 a different session. - Lock wait
...
- graph. The Lock Wait graph shows a breakdown of the Lock Waits, and when they occurred, over the selected time period.
| Info |
|---|
...
The graph displays data in time slices. |
About the lock tree structure
To gain a better understanding of the lock tree let's take a 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.
...
Every time a change occurs, a new lock tree is opened, as for example, when a new session enters into the lock. It is therefore possible that there will be sub-trees of several locks that may appear identical. See “Focusing on information in overtime graphs” on page 32.
About viewing internal waits for session and blocker session entities
The Internal Waits view displays the following internal waits, indicating that the session is waiting for an internal resource to be freed:
- Other internal
...
- wait. Aggregates the following types of waits:
- Full text waits
- HTTP waits
- Query notifications
- Backup
...
- wait. Includes the wait type that commonly occurs when a Backup command is performed.
- Memory
...
- wait. Includes ten types of waits that indicate that a session is waiting for memory to be allocated to it.
- Profiler
...
- wait. Aggregates a number of states associated with the SQL Profiler and lets you see how much of the database resources it consumes.
- DB mirror
...
- wait. Aggregates the new waits that occur when DB mirroring is performed, such as the waits that occur if the communication layer used by DB mirroring becomes backlogged.
- DTC
...
- wait. Aggregates waits that occur when Distributed Transaction Coordinator sessions have to wait for each other.
- Parallel
...
- wait. The session is waiting for one of its sub-threads to complete its operation.
- Latch
...
- wait. The session is waiting for an internal lock to be released.
- Buffer pool
...
- wait. Groups together the events that show contention on pages in the buffer pool.
About viewing overall activity for Session and Blocker Session entities
The Overall Activity view displays the duration of the activity performed by the selected session within the specified time period, broken down into the following components:
- Waitfor Command—the Command. The amount of time applications were waiting because they were waiting for command.
- Parallel Exchange Wait—the Wait. The amount of time the thread in a parallel session is waiting for data exchange from another thread.
- Request Wait—the Wait. The amount of time applications were in a request wait state, defined as the state wherein the server is waiting for a client request.
- In MS-SQL—represents SQL. Represents total amount of time spent in SQL Server.
...
| Info |
|---|
The graph displays data in time slices. |
See “Focusing on information in overtime graphs” on page 32.
About the Session and Blocker Session entities Association area
The following tabs are displayed in the Association area of the Session and Blocker Session entities:
...
Clicking a tab shows additional information on the entities displayed in the Association area table. The rows of the table remain the same; the columns change to display additional details on the entities listed in the table. The information displayed in each tab is described below.
About viewing Performance data for Session and Blocker Session entities
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 6-12 Information displayed in the Performance tab
| Column | Description |
|---|---|
Column Description
...
| SPID | Displays the system process ID. The SPID is a unique integer assigned to each user connection when the connection is made. |
| Program |
...
| Displays the program that opened the session. |
| In MS- |
...
| SQL | Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown, over the specified time period. |
| Database |
...
| Displays the database used by session. |
| Login |
...
| Displays the SQL Server or Windows login that opened the session. |
| Start |
...
| At | Displays the session's login time. |
| Completed |
...
| At | Displays when the session was completed, within the selected time period |
...
| . | |
| Machine | Displays |
...
| the machine the program ran on. |
| Work |
...
| Type | Displays the group the session is related to, such as User Application or SAP Dialog. |
...
| User | Displays the database user that opened the session. |
| Duration (Summed) |
...
| Displays the time that has elapsed since the login time. | |
| Client Process |
...
| ID | Displays the process ID of the program. |
About viewing In MS-SQL data for Session and Blocker Session entities
This tab contains the In MS-SQL counter and the breakdown of the In MS-SQL into its states. The In MS-SQL is displayed with a normalized view while all the other counters display the time each state was consumed.
About viewing Statistics data for Session and Blocker Session entities
The following table describes the additional information displayed in the Statistics tab in the Association area.
Table 6-13 Information displayed in the Statistics tab
| Column | Description |
|---|---|
Column Description
...
| SPID | Displays the system process ID. The SPID is a unique integer assigned to each user connection when the connection is made. |
| CPU |
...
| Time | Cumulative CPU time for the entity over the selected time period. |
| Physical I/O |
...
| Operations | Total number of physical I/O requests performed by the selected session over the selected time period. |
| Memory |
...
| Usage | Displays the memory consumed (in MB) by the selected session over the selected time period. |
| Open |
...
| Transactions | Displays the number of transactions that were opened by the selected session over the selected time period. |
See “About session states” on page 36.
About the Locked Object entity
The Locked Object displays the time each object was locked. The collector determines that an object is locked according to its lock type and lock resources. The following lock types are displayed:
Table 6-14 Lock types
Type Description
...
types are displayed:
Table 6-14 Lock types
| Type | Description |
|---|---|
| Database | Lock on the entire database. |
| Row |
...
| In Table | Lock on rows in the table. |
| Key |
...
| In Index | Lock on rows within the index. |
| Page of Database |
...
| File | Lock on pages in the database file. |
| Extent of Database |
...
| File | Lock on extents in the database file. |
| Table |
...
| Lock on the entire table's data and indexes. |
| Application |
...
| Lock on application resource. |
| Compile of Stored |
...
| Procedure | Lock on stored procedure. |
| Compile of |
...
| Trigger | Lock on stored trigger. |
| Compile of |
...
| Function | Lock on stored function. |
| Index Database |
...
| File | Lock on the entire index. |
| Unknown |
...
| Precise for SQL Server is not able to identify the lock type. |
| Info |
|---|
...
If the Collector identifies a blocking situation but is not able to identify the lock type and lock resource, then the sum of the wait on all locked objects will not be identical to the lock wait of the parent. |
About getting an overview of a Locked Object
The Locked Object view displays the locked Object Name, Lock Type, and the Lock Wait Duration graph, over the selected time period, as follows:Properties Displays
- Properties. Displays the locked object's full name and lock type. For example, if the locked object's type is Table, the name will also include the database and owner of the table.
- Lock
...
- Wait. Displays the lock wait duration of the selected locked object, over the selected time period.
About Table and Index entities
The Table and Index entities display performance information regarding the selected table or index. Object performance is measured by how much the object contributes to the performance of the statements accessing the object. The contribution is calculated by multiplying the cost of the operation(s) that access the object (in percentage) with the In MS-SQL time of the statement. The sum of the results of all statements accessing the object represents the performance of the object. The I/O wait time of an object is calculated in the same way.
The following views are available:
- Overview
- Performance
About getting an overview of Table and Index entities
The Overview displays general details regarding the Table or Index such as which database the table exists in, who is its owner and the amount of time it spent in SQL Server, broken down into its index and table components, as follows:Properties Displays
- Properties. Displays the following table or index identifiers: Database, Table Owner, and Table name, and index name (if the entity is an index).
- In MS-SQL access breakdown
...
- . Displays the estimated resource consumption resulting from accessing the table and its indexes in a Table entity and estimated resource consumption resulted from accessing the index in an Index entity.
About viewing Table and Index performance
The Performance view displays the estimated In MS-SQL duration versus the I/O waits for the selected Table or Index over the selected time period, as follows:
- In MS-
...
- SQL. This graph displays the estimated In MS-SQL for the Table or Index over the selected time period. When the selected entity is a table, the graph shows the breakdown of both the Table and index In MS-SQL duration. When the selected entity is an Index, only the portion for the selected index is displayed.
- I/
...
- O. This graph displays the estimated I/O wait duration resulting from accessing the Table or Index over the selected time period. When the selected entity is a table, the graph shows the breakdown of both the Table and index I/O wait duration. When the selected entity is an Index, only the information for the selected index is displayed.
About the I/O Entity
The I/O entity displays the I/O wait duration logged for the selected entity. The I/O entity can be a logical file or a storage device.
About the I/O entity Overview view
This view displays general information regarding the I/O entity. For example, for a Logical file entity the information displayed is: Logical File, File Database and Physical File name (including the path). In addition, the I/O wait duration over the selected time period is displayed.
...
Table 6-15 Benefits of the Precise for SQL Server Interpoint extension
| Type of Application | Benefit |
|---|---|
...
SAP Drill down to the most resource-consuming SAP transaction, SAP user, SAP work type, and SAP application server name.
...