Page History
...
| Anchor | ||||
|---|---|---|---|---|
|
Figure 6- 1 Precise for SQL Server Activity tab structure
...
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 for SQL Server agents” on page 19.
...
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 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 will display “Encrypted Text”. |
| Batch | Displays information on the activity of a specific batch. A batch can either be a stored procedure, stored function, trigger, or an ad hoc batch. If it is a stored object, the name of the stored objects are displayed. |
| Collapsed statement | Represents similar statements that differ in their parameters (literals). In a collapsed statement, the literals are replaced with parameter markers. The collapsed statement lets you analyze the overall resource consumption of similar statements. |
| Collapsed batch | Similar to collapsed statement a collapsed batch represents similar batches that differ in their parameters (literals). In a collapsed batch, the literals are replaced with parameter markers. The collapsed batch lets you analyze the overall resource consumption of similar batches. |
| Info |
|---|
The information displayed for certain entities may differ slightly than that described, as is relevant for the specific entity. For example, if you have navigated to a storage entity (such as a database file), only the I/O wait information for the selected entity is displayed. If you have navigated to a locked object, only the Lock wait information is displayed. |
...
The following table describes the additional information displayed in the Performance tab in the Association area.
Table 6- 5 Information displayed in the Performance tab
| Column | Description |
|---|---|
 | 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 ecid column in sysprocesses. This enables you to verify that SQL Server is using the best execution plan for the current statement. |
| Parallel Sessions (Max) | Maximum number of threads used to execute the statement or batch in parallel. This counter is calculated from the ecid column in sysprocesses. This enables you to verify that SQL Server is using the best execution plan for the current statement. |
About viewing Procedure Cache data for Collapsed Statement and Collapsed Batch entities
...
The following table describes the information displayed for execution plans in the Procedure Cache tab.
Table 6- 11 Information displayed in the Procedure Cache tab
| Column | Description |
|---|---|
| Entity name (such as Instance, Database) | Displays the name of the entity. |
| Elapsed Time | Amount of time it took to complete the executions of the plan. |
| Execution Count | Number of times the plan was executed. |
| Avg Duration | Average amount of time it took to execute the plan, calculated as total elapsed time divided by the execution count. |
| Recompilations | Number of times a plan was recompiled while it remained in the procedure cache. |
| CPU Time | Amount of CPU time consumed by the executions of the plan. |
| CLR Time | Time consumed inside CLR objects by the executions of the plan. |
| Logical I/O | Number of logical reads and writes performed by the executions of the plan. |
| Physical Reads | Number of physical reads performed by the executions of the plan. |
About Session and Blocker Session entities
...
The 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 36.
...
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. |
...
| 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 | ||||
|---|---|---|---|---|
|
...
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 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 > >
...