The SQL Collector for Monitor Serverallows you to capture from the ASE Monitor Server any currently executing SQL statements according to your user-defined criteria. Each SQL statement captured is categorized according to suspected levels of performance problem.
Create the Collector
Click . The SQL Collector window displays, followed by the Add Collector wizard if you have not created a Collector before. If the Add Collector wizard does not display, click .
Enter a Collector name and the Adaptive Server Enterprise Monitor Server name. Check that the other settings satisfy your requirements. Define a monitoring end time on the Schedule page on the Add Collector wizard.
Start the Collector
Click to begin capturing executing statements.
Details display in the SQL Collector grid as the information accumulates.
The monitoring process stops at the end time you define under the Schedule page in the Add Collector wizard.
View the results
To view the retrieve SQL statements, select the newly added Collector row.
In the SQL Text pane, click the tabs, SQL1, SQL2, and so forth to see the captured SQL statements. The SQL Information pane displays the query plan information.
Select one SQL statement you want to analyze for performance improvement. Click to optimize the SQL statement in the SQL Optimizer. Alternatively, you can also send the SQL statement to the Index Advisor. Click .
Use the SQL Scanner to analyze SQL statements embedded within database objects, text/binary files, Abstract Plan Groups, SQL Collector for Monitor Server files, SQL Inspector files, and application source code. The SQL Scanner extracts each SQL statement embedded within the scanned database objects and files, retrieves their respective query plans from Adaptive Server, and then performs an analysis that determines which of these SQL statements may be a performance bottleneck. You can copy the SQL statements analyzed as problematic (top priority) or complex (second priority) into the SQL Optimizer, or Index Advisor, and/or examine the extracted SQL statements with their query plans.
When you scan the database objects or the application files, you first create a Group to store the items you want to scan.
If this is the first time you have used the SQL Scanner, the Create Group window displays. Otherwise, click Create in the Group Manager window.
Enter a new Group name, e.g. "Test." Click OK to close the Create Group window.
Check that your new Group name is highlighted in the list field. Click Open.
The selected group is opened in the SQL Scanner window. For a new Group, the Add Jobs wizard is automatically opened so you can select what files or database objects you want to scan.
Note: If you are using an existing Group, click .
In the Add Jobs wizard, select the page for the item that you want to scan. You can select Database Objects, Abstract Plan Groups, SQL Collector for Monitor Server files, SQL Inspector files, and application Source Code in text or binary format. Click Finish.
Details are filled in the Job List as the scanning process completes each job. It will show you how many SQL statements found in the Job and how each SQL statement is classified.
To view the scanned SQL statements, highlight the job by clicking the row.
The first SQL statement found is shown in the SQL Text pane. Click the tabs, e.g. SQL1, SQL2, SQL3, etc., at the bottom left of the pane to view the other SQL statements.
Notice the buttons on the top of the SQL Information pane. These buttons display in the, the query plan, the abstract plan, Trace On information, the SQL classification and connection information, the DDL for temporary tables used by the SQL, and the Checked SQL information about SQL statement that you are reviewing.
You can narrow the number of original SQL statements to view only the problematic and/or complex statements with View | Problematic SQL and/or View | Complex SQL.
Select one SQL statement you want to analyze for performance improvement. Click to copy the SQL statement to the SQL Optimizer window and start the optimization process. Alternatively, you can also send the SQL statement to the Index Advisor. Click to copy the SQL statement to the Index Advisor window and generate index options.
The Performance Monitor monitors and retrieved database performance statistics and performance diagnostics metrics from the Adaptive Server (15.0 and later) monitoring tables. The collected statistics are presented graphically and provides a Top-N view that displays the overall database performance health. The graphical view makes it possible to drill down to get the details of different performance statistics such as engines, CPU, devices, processes, cache, lock, and others.
In the Performance Monitor window enable on the left tree view the detailed performance statistics that you want to monitor. By default, general performance statistics are enabled for monitoring.
Click to start the monitoring process.
Click to stop the monitoring process.
Note: Monitor options and refresh intervals can be changed by in File | Preferences.
Due to complex nature SQL, there may be many SQL statements that return the same result set, but only a few that may be efficient. The SQL Optimizer applies advanced SQL transformation technology to generate a list of semantically equivalent SQL statements. To identify the most efficient SQL statement for your database environment you can benchmark test the SQL alternatives in your database.
Open the SQL Optimizer window by clicking .
After entering a SQL statement in the SQL Editor pane of the window, click . This step launches the SQL Optimizer that automatically transforms the SQL statement.
- The use of forces and other optimization options such as temp table generation, ANSI JOIN syntax are optional and configurable in the Preferences.
- The intensity of the SQL transformation process is controlled by the Intelligence Level in the Preferences. The Intelligence Levels control how many forces are applied to transformed SQL and how many SQL alternatives are created.
- If your SQL statement uses a temporary table, see section User-Defined Temp Table for the steps to create a temporary table in the User-Defined Temp Table module.
After optimization, the Optimization Details window shows the total number of semantically equivalent SQL statements, the number of alternative statements with query plans different from your original SQL statement, and a warning message if the number of SQL transformations reaches any of the optimization quotas set in the Preferences.
Click OK to close the Optimization Details window.
In the SQL Optimizer window, look at the tabs which are labeled ALT1, ALT2, ALT3, etc. By clicking the tabs you can see the alternative SQL statements that were created by the SQL optimization process. The query plan for each SQL statement displays beside the SQL text.
At the bottom left of the SQL Optimizer window are three tabs, Time, Statistics, and Charts, which display the statistics for each SQL statement after it is executed. At this point since you have not yet run the SQL statements, it displays only the Estimated I/O Cost values. These are only estimations of how each statement will perform. You need to test each statement to obtain its actual run time statistics.
In the SQL Optimizer window, look at the right pane to see the query plan for the SQL statement.
To see how an alternative SQL statement differs from your original SQL, you can compare these statements side-by-side.
- Click . Your original SQL statement displays in one pane of the window and an alternative statement in another pane. Blue highlighted items in one pane show the area where there is a difference from the SQL statement displayed in the other pane.
- At the bottom of the SQL Comparer window, click the Show query plan checkbox to display the query plan for both SQL statements.
- Select File | Close SQL Comparer.
To prepare to execute the original and the alternative SQL statements, click .
In the Batch Run Criteria window, select the SQL statements that you would like to run in batch. Notice the tabs at the top of the Batch Run Criteria window.
Selected SQL Tab
- Select which SQL statements are to be executed. The blue checkmark in the left column indicates that the SQL statement is selected. By default, all statements are executed.
- To deselect a statement, click that SQL statement in the SQL column, for instance click ALT1.
- To deselect all the SQL statements, right-click and select Unselect All.
- To save time when testing run all generated alternatives, a filter function is provided to help you to precisely select the alternatives to test run. Click the Apply SQL Selection filter checkbox. Click to select and apply criteria to narrow down the SQL alternatives to test run.
SQL Termination Tab
Select the option for terminating the execution of your original and the alternative SQL statements. The SQL Optimizer generated all the alternative SQL statements in order to find the optimal SQL. Some of those alternatives may run faster than the original SQL, others may run longer. Therefore, you can set the termination criteria to cancel the longer running SQL statements and save database-processing time for the overall batch test. You have these options for terminating your SQL.
- Original SQL: Terminate the SQL statement when it has run as long as the original SQL.
- Best running time SQL: Run the first SQL statement and use the time from that statement as the termination time. When a SQL statement runs faster than this time, use the faster time as the new termination time, so you are always using the fastest run time as the termination time for the next SQL statement.
- User-defined time: Set your own termination time. If your original SQL statement takes a long time to execute and there are many alternative statements, executing all statements may take considerable time. In that event, consider setting an aggressive user-defined termination time. If the original takes 1 hour, try a 5-minute termination time. If no alternative statements execute in under that period, raise the termination time to 10 minutes, etc.
You can also combine User-defined time with Original SQL or Best running time SQL by clicking the Or User-defined time checkbox next to each one.
Batch Termination Tab
Select the option for terminating the Batch Run.
Specify to terminate the Batch Run when a specified number of SQL statements are found that meet the following requirements for terminating the Batch Run.
Number of SQL (excluding Original): Specify how many SQL statements must be found that a show performance improvement over the Original SQL.
Count the SQL if it elapsed time is faster than: Specify one of the following criteria to determine how the performance improvement is determined.
Original SQL: Count all SQL statements that run faster than the run time from the Original SQL.
Original SQL with a percentage of improvement: Count all SQL statements where the run time for the alternative SQL statement is the specified percentage faster than time for the Original SQL statement.
User-defined time (mins/secs): Count all SQL statements that run faster than a specified number of minutes and/or seconds.
Run Time Mode Tab
Note: The Batch Run function provides an efficient way of benchmarking SQL. It runs the selected SQL statements in the database and the SQL statements that exceed the termination time are cancelled. The Batch Run retrieves the time the SQL statement executes in the database and does not retrieve the result set from the database server to the client; so it does not create additional network traffic. For SQL statements such as SELECT...INTO, INSERT, DELETE and UPDATE, each statement is run in a transaction that is ROLLBACK, therefore maintaining the consistency of your data.
Batch Run Schedule Tab
To execute the original and the selected alternative SQL statements, click OK. The Batch Run window opens enabling you to view the results as each statement executes.
At the completion of the entire job, the Batch Run Details window replaces the Batch Run window. This window provides greater detail about each SQL statement. Click OK.
Click the Time tab in the SQL Optimizer window to see the columns in the SQL Run Time pane which contain the time to execute the entire statement (all records) and/or the time to retrieve only the first record for each SQL statement from the Batch Run.
Once you have identified the most-efficient alternative SQL statement to deploy it: