Page History
...
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.
...
| Anchor | ||||
|---|---|---|---|---|
|
Figure 6- 1 Precise for SQL Server Activity tab structure
...
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 About drilling down in context” on page 27, “About context, About the Precise bar” on page 24, “About bar, About the Main area” on page 25, “About area, About the Association area” on page 25area, and “Focusing Focusing on information in overtime graphs” on page 32graphs.
About navigating in the Activity tab
...
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 About drilling down in context” on page 27, “About context, About the Precise bar” on page 24, “About bar, About the Main area” on page 25, “About area, About the Association area” on page 25, “Focusing area, and 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 46graphs.
| Anchor | ||||
|---|---|---|---|---|
|
...
The Overview displays a summary of the performance and resource consumption of all instances in the application, as follows:
- 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.
...
- Buffer Pool
- Latch
- Parallel
- Distributed Transaction Coordinator (DTC)
- DB Mirror
- Profiler
- Memory
- Backup
- Other Internal waits
See “About internal waits” on page 37 About internal waits.
About viewing Instance Grouping for All Instances
...
For an explanation on how to define instance groups, see the Precise Administration Guide.
About Instance and Collector entities
...
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 |
|---|---|
| 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 Precise for SQL Server agents” on page 19agents.
The following views are available for the Instance and Collector entities:
...
- 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 About session states.
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 and “Focusing on See About session states and Focusing on information in overtime graphs” on page 32graphs.
About viewing Instance and Collector locking data
...
- 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 Focusing on information in overtime graphs” on page 32graphs.
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.
...
- Waitfor Command. The amount of time applications were waiting because they were waiting for command.
- Parallel Exchange Wait. The amount of time the thread in a parallel session is waiting for data exchange from another thread.
- Request 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 total amount of time spent in SQL Server. See “Focusing Focusing on information in overtime graphs” on page 32graphs.
About viewing Instance and Collector Scalability
...
- 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 Focusing on information in overtime graphs” on page 32graphs.
About viewing Instance and Collector System Scalability
...
- 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 Focusing on information in overtime graphs” on page 32graphs.
About viewing Instance and Collector Availability
...
- 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 Focusing on information in overtime graphs” on page 32graphs.
About the Instance and Collector entities Association area
...
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 6- 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. |
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 36See About session states.
About viewing procedure cache data for Instance and Collector entities
...
The following table describes the information displayed for execution plans in the Procedure Cache tab.
Table 6- 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. |
About Statement, Batch, Collapsed Statement or Collapsed Batch entities
...
The following table describes which performance and resource consumption information is displayed for the statement, batch, collapsed statement and collapsed batch entities.
Table 6- 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. |
| 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 About Statement identifiers.
The following views are available for the Statement, Batch, Collapsed Statement and Collapsed Batch entities:
...
- 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
ecidcolumn insysprocesses. 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
ecidcolumn insysprocesses. 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 ad-hoc 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.
...
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 Focusing on information in overtime graphs” on page 32graphs.
About viewing Statement, Collapsed Statement, Batch and Collapsed Batch entities locking
...
- 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 Focusing on information in overtime graphs” on page 32graphs.
About the lock tree structure
...
- Load. Elapsed time Execution count Avg average duration.
- Statistics.
- Recompilations
- CPU time
- CLR time
...
- 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 Focusing on information in overtime graphs” on page 32graphs.
About the Statement and Batch entities Association area
...
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
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 | 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 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 |
|---|---|
| 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. |
About viewing Procedure Cache information for Statement and Batch entities
...
The following table describes the information displayed for execution plans in the Procedure Cache tab
Table 6- 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. |
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 |
|---|---|
| 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 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 |
|---|---|
| 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
...
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 | 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. |
About viewing Procedure Cache data for Collapsed Statement and Collapsed Batch entities
...
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
...
| Info |
|---|
The graph displays data in time slices. |
See “About session states” on page 36 and “Focusing See About session states and Focusing on information in overtime graphs” on page 32graphs.
About viewing Session and Blocker Session Locking
...
| Info |
|---|
The graph displays data in time slices. |
...
About the lock tree structure
...
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 Focusing on information in overtime graphs” on page 32graphs.
About viewing internal waits for session and blocker session entities
...
| Info |
|---|
The graph displays data in time slices. |
See “Focusing Focusing on information in overtime graphs” on page 32graphs.
About the Session and Blocker Session entities Association area
...
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 |
|---|---|
| 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
...
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 |
|---|---|
| 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 36See About session states.
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 |
|---|---|
| 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. |
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click Overview. Examine the entire instance and determine which are the dominant resources that are consumed by your system.
- On the Association controls in the Association area, click Databases.
- On the Performance tab, place the cursor in the In MS-SQL column of the top database and view the information displayed on its resource consumption.
| Info |
|---|
...
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. |
| Anchor | ||||
|---|---|---|---|---|
|
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
On the View controls in the Main area, click In MS-SQL to view a breakdown of the In MS-SQL time.
Note:Info 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.
- On the Association controls in the Association area, click Programs.
- On the Performance tab, place the cursor in the In MS-SQL column of the top program entity and view the information displayed regarding its resource consumption.
- Drill down on the entity to focus on additional components.
- Go back to the overview of the instance you originally selected.
- In the Association area, use the Association controls to select different entities associated with the instance, and analyze the average duration of each entity.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click In MS-SQL.
- Move the cursor over the bars in the graph to view the instance's resource consumption over time.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click In MS-SQL.
- On the Association controls in the Association area, click Storage Devices.
- Observe the I/O waits for each storage device unit.
- If you locate storage devices suffering from I/O waits, you can check which logical files reside on the storage device and move a few to a different storage device. Drill down from the storage device and analyze the Logical Files entities. Once you have identified which logical file(s) should be moved to a different storage device, check the I/O pattern on that storage device, to ensure that the move will not cause the I/O pattern on the new storage device to deteriorate.
...
The following table shows the benefits of installing the Precise for SQL Server Interpoint extension.
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. |
| 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
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click Scalability.
- In the Session Duration (Avg) vs. Sessions graph, observe the number of sessions and the impact of the average session duration, over the selected time period. For example, if you observe that average session duration increases when the number of sessions decrease, this may indicate that the application has a scalability problem.
- On the View controls in the Main area, click System System Scalability.
- Analyze the In MS-SQL vs. SQL Server CPU usage graph to determine if SQL Server CPU consumption growth affects the server CPU consumption and I/O wait times. Check the Physical I/O Operations vs. Queue Length graph to determine if a growth in I/O requests increases the time I/Os have to wait in the queue to be served.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click In MS-SQL to view a breakdown of the In MS-SQL time. Check the Tempdb Wait Time component of the In MS-SQL counter.
- On the Association controls in the Association area, click Programs.
- On the Performance tab, place the cursor in the In MS-SQL column of the top program entity and check which portion of resource consumption can be attributed to tempdb wait.
- Drill down on the entity to focus on additional components.
Precise. Performance intelligence from click to storage. Learn more > >
...
...