SQL Optimizer for SAP ASE 3.9 - Release Notes

SQL Inspector Tutorial

The SQL Inspector monitors SQL statements and extracts SQL performance statistics from the Adaptive Server monitoring tables. The SQL Inspector also extracts SQL statements from the QP Metrics (sysquerymetrics view). 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.

Create the Inspector for Monitor Tables

1. Click .

2. If this is the first time you have used the SQL Inspector, the Add Inspector wizard displays over the SQL Inspector window. Otherwise, click .

3. In the General Information page, enter an Inspector name and select Monitor Tables.

4. In the Monitor Process page, specify whether you want to monitor the entire database server, any specific process IDs, or a connection identity, which allows you to focus on a specific user or application.

5. In the Retrieval Criteria page, specify how to retrieve records from the Adaptive Server monitoring tables.

6. In the Schedule Setting page, specify the monitoring schedule.

7. Click Finish.

Note: To use the SQL Inspector to access the monitoring tables, your database logon must have mon_role privilege.

Create the Inspector for QP Metrics

8. Click .

9. If this is the first time you have used the SQL Inspector, the Add Inspector wizard displays over the SQL Inspector window. Otherwise, click .

10. In the General Information page, enter an Inspector name and select QP Metrics.

11. In the Retrieval Criteria page, select the Users and Group IDs for the SQL you would like to extract and the number of SQL statements you would like.

12. In the Schedule Time page, specify when to start the inspection.

13. Click Finish.

Start the Inspector

14. Click .

View the results

15. Review the Top-N SQL statements chart used for filtering statements and a table with detailed SQL performance statistics.

16. Click if you want to add multiple charts to filter captured SQL statements by specifying a Top-N SQL criteria of performance statistics.

17. To filter collected SQL statements, use the No. of Top Consumption SQL and Percentage of Total Consumption sliders.

18. Click the performance statistics line on the grid to view the SQL text in the bottom pane of the window.

19. Once you have identified potentially problematic SQL statements you can send the SQL using the Edit menu to the SQL Optimizer or Index Advisor.

Related Topic

SQL Inspector Overview