Page History
...
- About the Activity tab
- How the Activity tab is structured
- About the entities you can examine
- How the Activity tab can help you identify performance problems
About the Activity tab
The Activity tab allows you to analyze performance behavior over time or within a given time period. When investigating a performance issue, the Activity tab is an excellent place to start. You can easily identify the performance profile of your SQL Server instance and determine who is executing which application, what they are waiting for and how many resources are they consuming. This is the critical information you need to identify the top consumers of your SQL Server Instance and its major latencies. By focusing on the top consumers and latencies (wait states) you can maximize the tuning impact on your system while minimizing the time it takes to obtain results.
...
When working in the Activity tab, you can choose to work on a specific instance or to view information on all your instances, in the application.Note:
| Info |
|---|
You usually switch to the Activity tab, after focusing on an instance in the Dashboard tab, or by launching Precise for SQL Server from another product (Precise for SAP, Precise for Microsoft .NET, Insight, Alerts, or Report Manager). In these cases, the product automatically focuses on the relevant instance. |
When you don't select a specific instance to view in-context, a summary of the performance information of All Instances in the application is displayed. You can analyze the average behavior of the entire SQL Server Tier. You can then identify the instance which consumes the most resources in your system and focus on it to further your investigation.
When you focus on a specific instance (either as a result of launching or from the SQL Server Tier view) you can also identify which is the dominant resource consumed, in terms of CPU, I/O lock, etc. By identifying your system's wait type you can formulate an idea as to what kind of solutions will help you improve your system.
| Info |
|---|
...
If you are working with SAP, PeopleSoft, or Siebel, and ERP support is installed, the Activity tab displays information on the historical SQL Server activity in relation to the ERP application and users, and specific information on executed views and transactions. |
How the Activity tab is structured
Figure 6-1 Precise for SQL Server Activity tab structure
The Activity tab displays information on various entities and their associated entities. The information displayed when you launch the Activity tab depends upon where you are launching from.
...
- Keeping the entity in the Main area but selecting a different set of entities to view in the Association are, by selecting an entity from the Association controls list.
- Switching to a different entity by drilling to another entity from the Association area.
- Switching to a different view in the Main area, thereby displaying additional information the selected entity.
About the Main area in the Activity 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, you can focus on an In MS-SQL overtime graph for an Instance entity to observe its resource consumption over time or you can focus on the Locking view to investigate all the locks performed on the instance.
See “About drilling down in context” on page 27, “About the Precise bar” on page 24, “About the Main area” on page 25, “About the Association area” on page 25, and “Focusing on information in overtime graphs” on page 32.
About navigating in the Activity tab
The name of the entity you selected is displayed in the tab heading, which serves as a point of orientation. The highest level entity that you can view information for in the Activity tab is the Tier.
Moving the pointer over the Tab heading displays a ToolTip with navigational information that traces the path you have already taken in your investigation.
About the Association area in the Activity tab
The Association area displays relevant information on the entities associated with the selected entity (displayed in the Tab heading in the Main area) in a table format. For example, it is possible to associate to Databases, Statements and Logins that are related to a specific Instance, by selecting an entity from the Association controls list. Notice that some entities, show additional information on the associated entities in different tabs. The tabs are located above the Association area table. Clicking on a tab displays different table columns showing different information for the associated entities. For example, the following tabs show additional information for the Statement entity:
...
See “About drilling down in context” on page 27, “About the Precise bar” on page 24, “About the Main area” on page 25, “About the Association area” on page 25, “Focusing on information in overtime graphs” on page 32. See “How most tabs are structured” on page 23, “Tasks common to most tabs” on page 28, and “About the Dashboard tab” on page 46.
About the entities you can examine
The Activity tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views. By default, all entities open in the In MS-SQL view, with the exception of the Statement, Batch, Collapsed Statement and Collapsed Batch entities which open by default in the Overview view.
...
- All Instances
- Instance and Collector Entities
- Statement, Batch, Collapsed Statement or Collapsed Batch Entities
- Session and Blocker Session Entities
- Locked Object Entity
- Table and Index Entities
- I/O Entities
| Info |
|---|
...
Some of these entities have been grouped together logically because they can be described similarly. They appear separately when viewed in the GUI. |
About the All Instances entity
An A Tier entity displays the resource consumption of all the instances in the application in the following views:
- Overview
- In MS-SQL
- Internal wait
- Instance grouping
About viewing a summary of system performance and resource consumption for All Instances
The Overview displays a summary of the performance and resource consumption of all instances in the application, as follows:Instances Displays
- Instances. Displays the number of Instances defined in the Tier.
In MS-SQL breakdown
...
table. Displays the resource consumption breakdown of all the instances over the selected time period, as follows:
Info
...
The table only displays the states the selected entity was in. For example, if the selected entity was not waiting for an I/O wait during the selected time period, the table will not display I/O wait parameters.
About viewing In MS-SQL data for All Instances
This view displays an overtime graph of the total time spent In MS-SQL for all instances in the SQL Server Tier.
About viewing the Internal Wait time experienced by All Instances
The Internal Waits view shows a breakdown of the internal wait counter in an overtime graph. The internal wait counters are broken down into the following subsets:
...
See “About internal waits” on page 37.
About viewing Instance Grouping for All Instances
Displays instance grouping information for the selected SQL Server Tier. It is possible to categorize instances into groups and observe their tuning counters, such as their In MS-SQL breakdown, according to the groups you defined. This option is useful if there are many instances in the same application.
For an explanation on how to define instance groups, see the Precise Administration Guide.
About Instance and Collector entities
The Instance entity and the common Collector entities display the resource consumption of the entire instance (or the entity selected). When focusing on an instance, or collector entity, such as a program, you can examine how it is performing within the selected time period.
| Info |
|---|
...
The information displayed for certain entities may differ slightly than that described, according to what is relevant to 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. |
The following table describes which Collector entities can be examined in the Activity tab.
Table 6-1 Collector entities that can be examined in the Activity tab
| Collector Entity |
|---|
...
| Description |
|---|
Collector entity Description
...
| Database | Displays information on an activity in reference to a specific database. |
Collector entity Description
...
| Program | Displays information on an activity generated from a specific program. Displays ‘N/A' if the program_name column in sysprocesses is null. If ERP is installed, the program is updated with the ERP data. For more information, see extended ERP data. |
| Login |
...
Displays information on an activity generated by a specific login. Displays ‘N/A' if the login name column in sysprocesses is null. If ERP is installed, the login is updated with the ERP data. For more information, see extended ERP data. |
| User |
...
| Displays information on an activity generated by a specific user in the database in use. |
| Machine |
...
Displays information on an activity generated from a specific workstation. Displays ‘N/A' if the hostname column in sysprocesses is null. If InterPoint for ERP is installed, the machine is updated with the ERP data. For more information, see extended ERP data. |
| Work |
...
| Type | The Precise for SQL Server Collector divides SQL Server activities into several groups. The Work Type entity displays performance information on each group. The following groups are available: |
...
|
...
|
...
|
...
In SAP: |
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
| COM+ |
...
| For more information, see extended ERP/COM+ documentation. | |
| ERP |
...
| Data | For more information, see extended ERP/COM+ documentation. |
| Job |
...
| Displays information on an activity, with reference to a specific job. |
See “Precise for SQL Server agents” on page 19.
...
- Overview
- In MS-SQL
- Locking
- Internal Waits
- Procedure Cache
- Overall Activity
- Scalability
- System Scalability
- Availability
About getting an overview of Instance and Collector data
The Overview displays information on the performance and resource consumption of the collector entities. The following information is displayed:Load Displays
- Load. Displays information on the following session-related items, as follows:
...
- Sessions. Number of sessions that occurred during the specified time period of the selected collector entity. For example, if you are viewing the Login entity, this value represents the number of sessions that are connected to the selected Login.
...
- Duration (Avg)
...
- . Average duration for a single session, in the selected collector entity, during the selected time period, calculated by dividing the Duration by the number of Sessions.
...
- Duration (Summed)
...
- . Total amount of time that elapsed for all of the selected collector entity's sessions, during the selected time period.
...
- Statement Executions. Number of statements executed within the selected time period that were generated by the selected collector entity's sessions.
...
- Statement Duration (Avg)
...
- . Average duration for a single statement execution, in the selected collector entity, during the selected time period. This value is calculated by dividing the Duration (Summed) parameter by the number of statement executions.
...
- Statistics. Displays the following session-related statistics, as follows:
...
- CPU Time. Cumulative CPU time for the collector entity or instance, during the selected time period. This counter is not accurate when SQL Server is configured to use fiber mode (lightweight pooling option).
...
- Physical I/O
...
- Operations. Total number of physical I/O operations performed by all sessions connected to the database during the selected time period.
- In MS-SQL
...
- breakdown. Displays collector entity resource consumption (table format) and shows instance breakdown, within the selected time period, as follows:
...
- State. Displays the In MS-SQL state.
...
- In MS-
...
- SQL. Displays a graphical representation of the Time column.
...
- Time. Amount of time the selected collector entity was in this state.
...
- %. Percentage of time the selected collector entity was in this state.
See “About session states” on page 36.
- %. Percentage of time the selected collector entity was in this state.
About viewing Instance and Collector In MS-SQL data
The In MS-SQL view displays the resource consumption of the selected collector entity over the selected time period. This graph enables you to analyze performance trends for the collector entity, over time.
See “About session states” on page 36 .See and “Focusing on information in overtime graphs” on page 32.
About viewing Instance and Collector locking data
The Locking view displays the blocking tree and the lock wait duration broken down into lock levels (such as, Table, Page, and Key), for the selected entity, over the selected 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 another session 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 information for Instance and Collector 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 (DTC) 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 information 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. The Procedure Cache view provides the following information on an execution plan:
- 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 times the plan was executed.
- I/O
...
- graph
- Graph showing the date and time that physical reads, and logical read/writes occurred.
About viewing overall activity of Instance and Collector entities
The Overall Activity view displays the duration of the activity performed by the selected collector entity over the selected 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. See “Focusing on information in overtime graphs” on page 32.
About viewing Instance and Collector Scalability
The Scalability view displays two overtime graphs that allow you to examine the scalability of the selected collector entity, as follows:
- Sessions duration (Avg) vs. sessions.
...
- Compares the average duration of the selected collector entity with the number of sessions, over the selected time period. For example, if the selected collector entity is Login the graph will show the number of sessions connected with the selected login versus the average duration of each session over time. Use this graph to analyze if the average session duration increases when the number of sessions increase.
- I/O wait vs. physical I/O
...
- Operations. Compares the amount of time the collector entity waited for the I/O to be completed with the actual physical I/O requests performed on behalf of the collector entity. Use this graph to analyze how an increase of physical I/O requests affects the I/O wait of the collector entity.
See “Focusing on information in overtime graphs” on page 32.
About viewing Instance and Collector System Scalability
The System Scalability view displays two overtime graphs that allow you to examine the scalability of the selected collector entity when analyzing OS metrics, as follows:
- In MS-SQL vs. SQL server CPU usage (%)
...
- . Compares the In MS-SQL of the selected collector entity with SQL Server CPU usage over the selected time period. For example, if the selected collector entity is Login the graph will show the In MS-SQL time consumed by the selected login and SQL Server CPU utilization over time. Use this graph to evaluate the correlation between collector entity performance and the SQL Server CPU usage. For example, you can determine if high CPU utilization is a result of collector entity's activity.
- Physical I/O Operations vs. queue length (Avg)
...
- . Compares the physical I/O requests performed on behalf of the collector entity with the average queue length of the available disks over the selected time period. 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 viewing Instance and Collector Availability
The Availability view displays the percentage of time the SQL Server instance was available, that is, it met the SLA Availability requirements configured in the SLA settings. A green bar represents the amount of time the instance availability met with SLA requirements and a Red bar represents the amount of time the instance availability was lower than SLA requirements.
| Info |
|---|
...
During planned downtime periods the instance availability is displayed as if it was available. This view is available only for instance and database entities. |
The following information is displayed in this view:Availability Displays
- Availability. Displays the percentage of time the SQL Server was available (including planned down time; for more information see the Precise Administration Guide).
- Unavailability
...
- times. Displays how long the SQL Server was down. If the Precise for SQL Server Collector was down, the status will be labeled "Unknown Time".
...
- Log. Provides general information regarding each period that the SQL Server was down.
Provide information on the status of SQL Server instance, over the selected time period. If the instance was down or if the status was unknown (the Collector was down) during the selected time period, only information on the unavailability of the instance is displayed (time range of the unavailability period).
See “Focusing on information in overtime graphs” on page 32.
About the Instance and Collector entities Association area
Additional information on performance, In MS-SQL activity, and the procedure cache, for the instance and collector entities, is displayed in the Association area tabs.
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.
About viewing performance data for Instance and Collector entities
The following table describes the additional information displayed in the Performance tab in the Association area.
...