This section includes the following topics:
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.
The Activity tab is also very useful for a scale-up exercise. Too frequently companies add CPUs to an existing server in their attempt to improve performance. Although occasionally successful, step one of any scale up activity should be to identify the major latencies in the SQL Server Instance and resolve them. Only once the major wait states are resolved, will a scale-up actually be capable of improving performance. Otherwise, you may be simply wasting money and resources.
The information displayed in the Activity tab is derived from historical data and enables you to access information for different time intervals at varying levels of detail. You can analyze what happened at a specific time, pinpoint problematic time periods, and drill down to analyze a bottleneck that caused a performance problem.
In addition, if you are running SQL Server 2005, you can now view information collected by the SQL Server 2005 procedure cache. This data contains information on each plan cached and provides information on their statements and statistics on the execution of the plan's statements. This information allows you to pursue a wider range of new performance tuning scenarios.
The Activity tab was designed to answer questions such as, "What happened yesterday, as well as a month ago and a year ago?" and "What has been the performance level of my applications over time."
Data in the Activity tab is available up to the last time slice that ended (information on the last minute is available in the Current tab). The Activity tab is usually the place to identify and investigate tuning problems.
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.
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.
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. |
Figure 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.
For example, when you launch the Activity tab from the Dashboard tab, one of the following occurs, depending upon what you selected in the All SQL Server Instances table:
When you access the Activity tab from other tabs, the entity, time frame, views, and associations that were in place the last time you visited the tab are displayed (similar to clicking the Back button in the Precise toolbar).
When you launch to another tab, from within the Activity tab, the entity and time frame you currently selected are taken into account. The tab you launched to will automatically display information in reference to the entity and time frame (if the entity exists in the tab you launched to). This is called in-context navigation and enables you to focus on other aspects of the selected entity to further your analysis.
The information on the selected entity and its associated entities are displayed in two areas of the tab—the Main area and the Association area.
When you perform an action on an element in the lower area (in this case the Association area) the information displayed in the upper area (in this case the Main area) will change to reflect the action you requested. Accordingly, you can control the tab by:
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, About the Precise bar, About the Main area, About the Association area, and Focusing on information in overtime graphs.
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.
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:
If you want to view additional information on an associate entity, drill down to it, by selecting the entity's row. A drilldown affects the entire tab. When you drill down to another entity, the Tab heading reflects the new selection, the Main area displays information on the selected entity, and the Association area displays the entities that are now associated with the newly selected entity.
For example, when you want to drill down to an associated entity, such as a specific program, to view additional information, choose Programs from the Association controls. Note that the Tab heading and the Main area remain unchanged. Click the row of the program you want to view detailed information for. The Tab heading indicates the newly selected program; the Main area displays In MS-SQL information on the program you drilled down to, and the Association area shows information on statements executed by this program.
See About drilling down in context, About the Precise bar, About the Main area, About the Association area, and Focusing on information in overtime graphs.
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.
The following entities can be examined in the Activity tab:
Some of these entities have been grouped together logically because they can be described similarly. They appear separately when viewed in the GUI. |
A Tier entity displays the resource consumption of all the instances in the application in the following views:
The Overview displays a summary of the performance and resource consumption of all instances in the application, as follows:
In MS-SQL breakdown table. Displays the resource consumption breakdown of all the instances over the selected time period, as follows:
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. |
This view displays an overtime graph of the total time spent In MS-SQL for all instances in the SQL Server Tier.
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.
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.
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.
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 1 Collector entities that can be examined in the Activity tab
| Collector Entity | Description |
|---|---|
| Database | Displays information on an activity in reference to a specific database. |
| 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:
The following groups are only displayed if InterPoint for ERP is installed: In SAP:
In PeopleSoft:
In Siebel:
|
| 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.
The following views are available for the Instance and Collector entities:
The Overview displays information on the performance and resource consumption of the collector entities. The following information is displayed:
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 and Focusing on information in overtime graphs.
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:
See Focusing on information in overtime graphs.
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.
The following information is displayed in the Lock tree:
Focusing on a blocked session branch in the lock tree will expose the SQL text performed by the session. The lock type is displayed in the Lock Breakdown table, which is displayed to the right of the Lock Trees.
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.
The Internal Waits view displays the following internal waits, indicating that the session is waiting for an internal resource to be freed:
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:
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:
The Scalability view displays two overtime graphs that allow you to examine the scalability of the selected collector entity, as follows:
See Focusing on information in overtime graphs.
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:
See Focusing on information in overtime graphs.
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.
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:
See Focusing on information in overtime graphs.
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.
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 2 Information displayed in the Performance tab
| 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. |
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.
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 3 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. |
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.
The following table describes which performance and resource consumption information is displayed for the statement, batch, collapsed statement and collapsed batch entities.
Table 4 Statement, Collapsed Statement, Batch and Collapsed Batch entities
| 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 displays, "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. |
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.
The following views are available for the Statement, Batch, Collapsed Statement 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:
ecid column in sysprocesses. Check this counter to verify that the SQL Server is implementing the best execution plan for the statement.ecid column in sysprocesses. Check this counter to verify that the SQL Server is implementing the best execution plan for the statement.The Text view displays the SQL Text of the statement or batch.
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.
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:
See Focusing on information in overtime graphs.
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.
The following information is displayed in the Lock tree:
Focusing on a blocked session branch in the lock tree will expose the SQL text performed by the session. The lock type is displayed in the Lock Breakdown table, which is displayed to the right of the Lock Trees.
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.
The Internal Waits view displays the following internal waits, indicating that the session is waiting for an internal resource to be freed:
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:
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:
The System view displays two overtime graphs that allow you to examine the scalability of the selected entity when analyzing OS metrics.
This information enables you to evaluate the correlation between the activities performed by the selected entity and the general load. For example, is the SQL Server CPU Usage mostly consumed by the selected statement?
The following overtime graphs are available:
See Focusing on information in overtime graphs.
The following tabs are displayed in the Association area of the Statement and 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 Actual Plan column displays if the plan for statement or batch is an actual plan. This is available for SQL Server 2005 only.
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 5 Information displayed in the Performance tab
| 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 | 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). |
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.
The following table describes the additional information displayed in the Statistics tab in the Association area.
Table 6 Information displayed in the Statistics tab
| 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 ecid column in sysprocesses. This enables you to verify that SQL Server is using the best execution plan for the current statement. |
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.
The following table describes the information displayed for execution plans in the Procedure Cache tab
Table 7 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. |
The following table describes the additional information displayed in the Plan tab in the Association area.
Table 8 Information displayed in the Plan tab
| Column | 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. |
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.
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 9 Information displayed in the Performance tab
| 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. |
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.
The following table describes the additional information displayed in the Statistics tab in the Association area.
Table 10 Information displayed in the Statistics tab
| Column | 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 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 This enables you to verify that SQL Server is using the best execution plan for the current statement. |
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 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. |
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.
The Blocker Session entity displays information on the holding locks that cause other sessions to wait for resources to be free.
The following views are available:
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.
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.
The graph displays data in time slices. |
See About session states and Focusing on information in overtime graphs.
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:
The graph displays data in time slices. |
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.
The following information is displayed in the Lock tree:
Focusing on a blocked session branch in the lock tree will expose the SQL text performed by the session. The lock type is displayed in the Lock Breakdown table, which is displayed to the right of the Lock Trees.
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.
The Internal Waits view displays the following internal waits, indicating that the session is waiting for an internal resource to be freed:
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:
The graph displays data in time slices. |
See Focusing on information in overtime graphs.
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.
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 12 Information displayed in the Performance tab
| 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. |
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.
The following table describes the additional information displayed in the Statistics tab in the Association area.
Table 13 Information displayed in the Statistics tab
| 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.
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 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. |
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. |
The Locked Object view displays the locked Object Name, Lock Type, and the Lock Wait Duration graph, over the selected time period, as follows:
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:
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:
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:
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.
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.
You can identify a performance problem by doing one or more of the following:
When performing a tuning audit it is very important to analyze and understand the performance behavior of your instances and databases. You may have already drilled down to analyze the performance of a single query or program. However, examining the entire instance's behavior can alert you to the health of your system. The Instance (or Database) represents the performance of the average application. This helps you answer questions such as: "This query suffers from Log wait, but does the entire system suffer from lock wait?" If, for example, you discover that your system suffers from I/O wait and you tune a query that performs memory operations, you most likely will not improve the entire system's I/O wait problem. So examining the entire instance can provide a quick overview of the dominant resources consumed.
To examine the resource consumption of an entire instance and database
As a rule of thumb, a healthy system should have a high Using CPU value, a 10–15% I/O Wait, a Log Wait based on the nature of the application, and the remaining states should show values that are as minimum as possible. |
Precise for SQL Server enables you to drill down to application components (such as Logins, Databases, Programs, Machines, and Statements) to determine which application component consumes the most resources. This is accomplished by clicking on an application component in the Association area. This process is iterative and you may continue to drill down until you discover the application component that you want to tune.
There are several reasons to drill down and focus on different application components:
To identify heavy resource consumers
On the View controls in the Main area, click In MS-SQL to view a breakdown of the In MS-SQL time.
One of the components of In MS-SQL time that you can observe in the In MS-SQL graph is Internal Wait. You can view additional information on that particular component, over the selected time frame, by clicking Internal Waits on the View controls in the Main area. This view lets you analyze a breakdown of the Internal Wait parameter and determine which internal wait parameter is consuming the most time and resources. |
When you analyze and tune the performance of an application component (such as, database, program, or query) it is important to take into consideration its performance over time. Precise for SQL Server allows you to easily view the component's performance over a selected time period.
There are several reasons to study a component's performance over time:
To examine resource consumption over time
If you have determined that the application is suffering from I/O waits, you may want to examine how the I/O waits are divided amongst the various storage devices. You will want to balance the I/O waits among all the storage devices.
To examine I/O waits of different storage devices connected to an SQL Server instance
Certain obstacles make it difficult to investigate ERP data in the SQL Server Tier. Most activity is registered in SQL Server under generic names. For example, in SAP, all activities are listed under a single login (SAP\SAPServiceSQ1) and some generic program names (R3D01(1)sp crea OLEDB).
Installing the Precise for SQL Server Interpoint extension lets you correlate SQL Server information with ERP information. For example, if you install Interpoint for SAP, you will be able to display the breakdown of your SQL Server activity into entities such as, SAP User and SAP Transactions.
The Precise for SQL Server with SAP Interpoint extension must be installed before you can correlate SQL Server data and ERP/COM+ data. For information on how to install the Precise for SQL Server with an SAP Interpoint extension, see the Precise Installation Guide.
The following table shows the benefits of installing the Precise for SQL Server Interpoint extension.
Table 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. |
| PeopleSoft | Identify the most resource-consuming PeopleSoft Client Machines, PeopleSoft User IDs, Tuxedo OS Users, Tuxedo domains, and work types. |
| Siebel | Identify the most resource-consuming Siebel business object, Siebel application, Siebel component, and Siebel server. |
| COM+ | Determine the most resource-consuming COM+ application, COM+ component, COM+ method, and COM+ interface. |
To correlate SQL Server data with ERP/COM+ data
While many think that locking is bad, the truth is that locking is good—it maintains data integrity. The situations to avoid are contentions. Contentions are situations in which one application waits for a resource that is being held by another application.
Locking can become problematic if the portion of time the application is blocked lasts too long. If you identify a lock wait time that seems too long, further investigation is required. The Lock wait time is reported as part of the In MS-SQL breakdown.
Analyzing the blocking situation is much easier when the lock is currently active, but this is usually not the case. For this reason it is important to understand why the locks occurred in the past.
There are several areas you can focus on when resolving a blocking situation:
To examine locking over time of a database entity
Having identified a major resource-consuming entity, you can further drill down to the statements and batches executed by that entity. These may include long running SQL statements and SQL statements that use few resources but were executed frequently.
In many cases analyzing the queries executed by the entity and tuning them will result in the most performance gain. For each statement, you can do the following:
To identify offending SQL Statements
Many statements may look the same but differ in their literals. When you want to identify the most resource-consuming statements you would want to treat them as the same statement. Precise for SQL Server lets you identify the most resource-consuming collapsed statements or batches.
Once you focus on a collapsed statement you can examine the different statements having the selected collapsed form to examine the performance of each individual instance.
If you observe different performance patterns for the same statements you can also examine the distribution of their execution plans. If they have different execution plans this may explain the different performance patterns.
The batch hash value for a collapsed batch in a stored procedure is calculated according to the header or definition. This lets you can observe the performance of a batch over time, even if changes were made to a statement in the batch. |
To identify resource-consuming collapse statements or batches
Because Precise for SQL Server maintains a constant connection with your SQL Server instance, it can easily report on the availability of the instance and databases.
The availability report displays the percentage of time the instance was up and the amount of time the instance was unavailable, over the selected time period.
This view is accurate only if the Precise for SQL Server collector is up and running. |
To examine the availability of an instance and database
Applications are called upon to support the addition of more and more users, and data, over the years. One of today's tasks is ensure that our application and servers are scalable for tomorrow's tasks. Precise for SQL Server enables you to examine if the application is scalable, or if the SQL servers are scalable.
To analyze scalability of an application you need to compare the number of sessions and the impact of the average session duration. In a well scalable instance, growth in the number of sessions will not impact the average session duration. This can be observed in the Scalability View in the Session Duration (Avg) vs. Sessions graph. The same applies to I/O and CPU, as you can see in the Physical I/O Operations vs. I/O Wait graph, which shows how more and more I/O requests affect the I/O waits.
When examining if the server is scalable, check if SQL Server CPU consumption growth affects the server CPU consumption and I/O wait times. You will need to answer if growth in the I/O requests increases the time I/Os are waiting in the Queue to be served.
The first example below shows that the application is not scalable. The Session Duration (Avg) decreases when the number of sessions increase.
To examine the scalability of your application or SQL servers
Tempdb is the most heavily shared resource across the entire instance and can affect the performance of all applications. It is therefore important to monitor its usage. A high tempdb wait value may indicate a bottleneck in tempdb.
To identify tempdb contentions
|