The SQL Inspector identifies SQL statements with performance problems by extracting SQL statements and SQL performance statistics from the QP Metrics (Adaptive Server 15 or later). The SQL Inspector graphically displays and compares SQL activity statistics to diagnose performance bottlenecks. With the SQL Inspector you can identify the top N most resource intensive SQL statements.
With the SQL Inspector, you can schedule the date and time and the frequency that the SQL statistics are collected in order to identify the impact of SQL activities on database performance. By displaying the statistics on a graph you can see which SQL statements are consuming the most database resources, hence identifying problematic SQL statements quickly and efficiently.
The SQL Inspector window provides a work area for you to add and delete Inspectors, storing information on the Inspector name and description, number of SQL captured, start time, end time, and duration. It offers a way to view the statistics from the SQL statements captured in charts and a grid format. It provides a view of the top N worst performing SQL statements. SQL statements can be sorted by different AND/OR conditions of SQL performance statistics such as number of executions, CPU time, logical reads, physical reads, and others.
SQL statements collected in Inspector files can be also be analyzed with the SQL Scanner module. Once you have identified potentially problematic SQL statements you can send the SQL from the SQL Inspector to the SQL Optimizer, Index Advisor, the SQL Worksheet, or save them to the SQL Repository.
Note: This module is available for Adaptive Server 15 and later and requires that the Adaptive Server monitoring tables are installed.
Or, if you have Adaptive Server 15.0 or later, you can retrieve the SQL statements and performance statistics using QP Metrics if you have access to the sysquerymetrics view.
To use the SQL Inspector module you must have access to the sysquerymetrics view in Adaptive Server 15.0 or later.
You must also set up specific configuration parameters in Adaptive Server.
Each SQL statement captured from the monitoring tables has the following performance statistics:
Each SQL statement captured from the QP Metrics has the following performance statistics:
| Statistics | Description |
|
Execution time |
How long it takes to execute the SQL statement on the CPU displayed in milliseconds. |
|
Elapsed time |
This is a total of the Execution time and the time it takes to parse, compile, and optimize the SQL statement displayed in milliseconds. |
|
Logical IO |
Total logical IO reads during the execution of the SQL statement. |
|
Physical IO |
Total physical IO reads during the execution of the SQL statement. |
|
Execution count |
Number of times the SQL statement has been executed. |
|
Abort count |
Number of times the resource governor terminated the SQL statement because a resource limit was surpassed. |
The execution time, elapsed time, logical IO, and physical IO statistics each of have three values taken from the number of times the SQL statements was executed: the minimum, the maximum, and the average.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
