Page History
...
Table 6-2 Information displayed in the Performance tab
Column Description
...
| Column | Description |
|---|---|
| Entity name (such as instance, database, etc.) | Displays the name of the entity. |
| 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. |
| Sessions |
...
| Number of sessions that occurred during the selected time period of the selected entity. |
| Duration (Avg) |
...
| Average duration for a single session, in the selected entity, during the selected time period, calculated by dividing the Duration by the number of Sessions. | |
| Statement |
...
| Executions | Number of statements executed within the selected time period that were generated by the selected entity's sessions. |
| CPU |
...
| Time | Cumulative CPU time for the entity during the selected time period. |
This value is calculated by the Collector as follows (this value is an approximation)—the CPU Time field in sysprocesses is sampled at the start point and end point. The value sampled at the start point is subtracted from the value sampled at the end point resulting in the CPU Time reported. The start and end points are determined according to values that have changed. For example, a session is sampled by the Collector for five seconds, but the start and end points were calculated according to when a value has changed. As a result, the calculated CPU Time in this example is only three out of the sampled five seconds since this is takes into consideration when the value has changed.
About viewing In MS-SQL data for Instance and Collector 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. See “About session states” on page 36.
About viewing procedure cache data for Instance and Collector 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.
...
Table 6-3 Information displayed in the Procedure Cache tab
Column Description
| 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 Statement, Batch, Collapsed Statement or Collapsed Batch entities
When an application component (such as Database or Login) consumes resources in SQL Server, it is actually executing a statement that is consuming resources. Tuning performance problems typically involves finding and tuning offending statements.
...
Table 6-4 Statement, Collapsed Statement, Batch and Collapsed Batch entities
| Entity | Description |
|---|---|
Entity Description
...
| Statement | Displays information on the activity of a specific TSQL statement. A statement is always part of a batch. When an encrypted stored procedure is encountered the statement will display “Encrypted Text”. |
...
| Batch | Displays information on the activity of a specific batch. A batch can either be a stored procedure, stored function, trigger, or an ad hoc batch. If it is a stored object, the name of the stored objects are displayed. |
| Collapsed |
...
| statement | Represents similar statements that differ in their parameters (literals). In a collapsed statement, the literals are replaced with parameter markers. The collapsed statement lets you analyze the overall resource consumption of similar statements. |
| Collapsed |
...
| batch | Similar to collapsed statement a collapsed batch represents similar batches that differ in their parameters (literals). In a collapsed batch, the literals are replaced with parameter markers. The collapsed batch lets you analyze the overall resource consumption of similar batches. |
| Info |
|---|
...
The information displayed for certain entities may differ slightly than that described, as is relevant for the specific entity. For example, if you have navigated to a storage entity (such as a database file), only the I/O wait information for the selected entity is displayed. If you have navigated to a locked object, only the Lock wait information is displayed. |
See “About Statement identifiers” on page 38.
...
- Overview
- Text
- In MS-SQL
- Locking
- Internal Waits
- Procedure Cache
- Scalability
- System Scalability
About getting an overview of Statement, Collapsed Statement, Batch and Collapsed Batch entities
The Overview displays information on the entity's activity, such as its number of executions and average statement duration. In addition the resource consumption breakdown and the text of the statement or batch are also displayed, as follows:Load Displays
- Load. Displays information on the following session-related items:
...
- Database. Identifies the statement's database.
...
- User. Identifies the statement's parsing user.
...
- Statement Executions. Number of times the Collector identifies that the statement was executed during the selected time period.
...
- Statement Duration (Avg)
...
- . Displays the average duration of each statement execution within the selected time period, calculated by dividing the Duration (Summed) by the number of Statement Executions.
...
- Statement Duration (Summed)
...
- . Total elapsed time required to run all executions of the selected statement or batch during the selected time period.
...
- Statistics. Displays the following statement-related statistics:
...
- CPU Time. Cumulative CPU time for the entity during the selected time period. This value is calculated by the Collector as follows (this value is an approximation)—the CPU Time field in sysprocesses is sampled at the start point and end point. The value sampled at the start point is subtracted from the value sampled at the end point resulting in the CPU Time reported. The start and end points are determined according to values that have changed. For example, a statement is sampled by the Collector for five seconds, but the start and end points were calculated according to when a value has changed. As a result, the calculated CPU Time in this example is only three out of the sampled five seconds since this is takes into consideration when the value has changed.
...
- Physical I/O
...
- Operations. Total number of physical I/O requests during statement execution, within the selected time period.
...
- Parallel Sessions (Min)
...
- . Certain statements are executed using parallelisms. This counter displays the minimum number of threads used to execute the statement and is calculated by examining the ecid column in sysprocesses. Check this counter to verify that the SQL Server is implementing the best execution plan for the statement.
...
- Parallel Sessions (Max)
...
- . Certain statements are executed using parallelisms. This counter displays the maximum number of threads used to execute the statement and is calculated by examining the ecid column in sysprocesses. Check this counter to verify that the SQL Server is implementing the best execution plan for the statement.
- In MS-SQL breakdown
...
- . Displays resource consumption broken down into states, such as, Using CPU, I/O Wait, Lock Wait, etc., within the specified time period. This allows you to identify the highest resource consumers.
...
- Text. Displays statement or batch text (formatted for easy readability).
When viewing a batch entity, the entire text of the adhoc batch or stored object is displayed.
When viewing a statement, only the part of the batch that generates the statement is displayed.
When viewing a collapsed statement or batch, the literals are replaced with parameter markers.
About viewing Statement, Collapsed Statement, Batch and Collapsed Batch text
The Text view displays the SQL Text of the statement or batch.
About viewing In MS-SQL data for statements, collapsed statements, batches and collapsed batches
The In MS-SQL view displays the resource consumption of the selected entity over the selected time period. This graph enables you to analyze performance trends for the entity, over time.
See “Focusing on information in overtime graphs” on page 32.
About viewing Statement, Collapsed Statement, Batch and Collapsed Batch entities locking
The Locking view displays the blocking tree and Lock Wait duration of the selected entity over a specified time period broken down into lock levels such as, Table Lock, and Page Lock. 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.
See “Focusing on information in overtime graphs” on page 32.
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.
About viewing internal waits data for statements, collapsed statements, batches and collapsed batches
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 procedure cache data for statements, collapsed statements, batches and collapsed batches
The Procedure Cache is the part of the SQL Server memory pool that stores execution plans for batches, stored procedures and triggers. The Procedure Cache view provides the following information on an execution plan:Load Elapsed
- Load. Elapsed time Execution count Avg duration
- Statistics.
...
- Recompilations
- CPU time
- CLR time
- I/
...
- O. Logical reads Logical writes Physical reads
- Avg Duration vs. Execution Count graph. Graph showing the amount of time it took to complete the execution of a plan versus the number of
...
Count graph
- times the plan was executed.
- I/O
...
- graph. Graph showing the date and time that physical reads, and logical read/writes occurred.
About viewing scalability for statements, collapsed statements, batches and collapsed batches
The Scalability view displays two overtime graphs that allow you to examine the scalability of the selected collector entity over the selected time period. For example, you can determine whether the average statement duration of an entity's activity increased as a result of an increase in number of executions.
The following overtime graphs are displayed:
- Statement duration (Avg) vs. executions.
...
- Compares the average duration of the selected entity with the number of executions, over the selected time period. For example, if the selected entity is Statement, the graph will show the number of executions of the selected statement versus the average duration of each execution over time. Use this graph to analyze if the average statement duration increases when the number of executions increase.
- I/O wait vs. physical I/O
...
- Operations. Compares the amount of time the entity waited for the I/O to be completed with the actual physical I/O requests performed on behalf of the entity. Use this graph to analyze how an increase of physical I/O requests affects the I/O wait of the entity.
About viewing system scalability for statements, collapsed statements, batches and collapsed batches
The System view displays two overtime graphs that allow you to examine the scalability of the selected entity when analyzing OS metrics.
...
The following overtime graphs are available:
- In MS-SQL vs. SQL server CPU usage (%)
...
- . Compares the In MS-SQL of the selected entity with SQL Server CPU usage.
For example, if the selected entity is Statement the graph will show the In MS-SQL time consumed by the selected Statement and SQL Server CPU utilization over time. Use this graph to evaluate the correlation between entity performance and the SQL Server. CPU utilization represents an average of the utilization of all processes.
- Physical I/O Operations vs. queue length (Avg)
...
- . Compares the physical I/O requests performed on behalf of the entity with the average queue length of the available disks. Since an increase in queue length affects application performance, this graph enables you to analyze how an increase in physical I/O requests affects queue length.
See “Focusing on information in overtime graphs” on page 32.
About the Statement and Batch entities Association area
The following tabs are displayed in the Association area of the Statement and Batch entities:
...
The Actual Plan column displays if the plan for statement or batch is an actual plan. This is available for SQL Server 2005 only.
About viewing performance data for Statement and Batch entities
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 6-5 Information displayed in the Performance tab
Column Description
...
Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown, over the selected time period.
| 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 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.
...