Skip to main content

Get SQL Performance Help

InterSystems Worldwide Response Center (WRC)Opens in a new tab offers customer support for analyzing query performance. The WRC can help you finely tune your optimizations to increase the efficiency of your queries. To contact the WRC for support for query analysis, run the Generate Report tool from the Management Portal using either of the following:

  • Select to System Explorer > Tools > SQL Performance Tools > Generate Report.

  • Select to System Explorer > SQL > Tools > Generate Report.

Generate Report

To use the Generate Report tool, perform the following steps:

  1. You must first get a WRC tracking number from the WRC. You can contact the WRC from the Management Portal by using the Contact button found at the top of each Management Portal page. Enter this tracking number in the WRC Number area. You can use this tracking number to report the performance of a single query or multiple queries.

  2. In the SQL Statement area, enter a query text. An X icon appears in the top right corner. You can use this icon to clear the SQL Statement area. When the query is complete, select the Save Query button. The system generates a query plan and gathers runtime statistics on the specified query. Regardless of the system-wide runtime statistics setting, the Generate Report tool always collects with Collection Option 3: record statistics for all module levels of the query. Because gathering statistics at this level may take time, it is strongly recommended that you select the Run Save Query process in the background check box. This check box is selected by default.

    When a background job is started, the tool displays the message "Please wait...", disables all the fields on the page, and show a new View Process button. Clicking the View Process button will open the Process Details page in a new tab. From the Process Details page, you can view the process, and may "Suspend", "Resume" or "Terminate" the process. The status of the process is reflected on the Save Query page. When the process is finished, the Currently Saved Queries table is refreshed, the View Process button disappears, and all the fields on the page are enabled.

  3. Perform Step 2 with each desired query. Each query will be added to the Currently Saved Queries table. Note that this table can contain queries with the same WRC tracking number, or with different tracking numbers. When finished with all queries, proceed to Step 4.

    For each listed query, you can select the Details link. This link opens a separate page that displays the full SQL Statement, the Properties (including the WRC tracking number and the InterSystems IRIS software version), and the Query Plan with performance statistics for each module.

    • To delete individual queries, check the check boxes for those queries from the Currently Saved Queries table and then click the Clear button.

    • To delete all queries associated with a WRC tracking number, select a row from the Currently Saved Queries table. The WRC number appears in the WRC Number area at the top of the page. If you then click the Clear button, all queries for that WRC number are deleted.

  4. Use the query check boxes to select the queries you wish to report to the WRC. To select all queries associated with a WRC tracking number, select a row from the Currently Saved Queries table, rather than using the check boxes. In either case, you then select the Generate Report button. The Generate Report tool creates a xml file that includes the query statement, the query plan with runtime statistics, the class definition, and the sql int file associated with each selected query.

    If you select queries associated with a single WRC tracking number, the generated file will have a default name such as WRC12345.xml. If you select queries associated with more than one WRC tracking number, the generated file will have the default name WRCMultiple.xml.

    A dialog box appears that asks you to specify the location to save the report to. After the report is saved, you can click the Mail to link to send the report to WRC customer support. Attach the file using the mail client's attach/insert capability.

FeedbackOpens in a new tab