Collecting Optimizer Diagnostic Data

Use SAP Control Center to collect diagnostic data to send to SAP Technical Support to perform detailed analyses in optimizing SQL queries and improving performance.

Prerequisites

Register and authenticate the agent to enable the collection of diagnostic data.

Make sure Job Scheduler is installed, enabled, and running. You must also be logged in to Job Scheduler with a login that has sa_role permissions and either js_user_role or js_admin_role before you collect optimizer diagnostic data; otherwise, you see an error message on the Introduction page, and the Next button becomes disabled.

Task

SAP Control Center obtains diagnostic data to optimize complex SQL queries by executing the sp_opt_querystats system procedure. See sp_opt_querystats in Reference Manual: Procedures.

  1. From the Administration Console, select ASE Servers.
  2. From the servers on the summary list, click the server to collect diagnostic data for, and select Properties.
  3. In the Server Properties window, select Diagnostic Data.
  4. From the Diagnostic Data window, click Collect Optimizer Diagnostic Data.
  5. In the Diagnostic Data page, specify:
    • Record name – is the name associated with the diagnostic data package in the SCC repository. This record name is not the name of the diagnostic file, which is long and complex, but uses a short default format that is easy to associate with your server. The default format is servername_number, where servername is the name of your server, and number is the lowest unused number. You can change the record name.
    • Output directory – is the path in which to store the diagnostic data file. The path can be either absolute or relative to the release directory, but must point to the location of the remote server node. The default value is the release directory.
  6. In the text field of the Query SQL page, specify the SQL statement to collect diagnostic information for. You can either:
    • Type a SQL query directly into the text field, or,
    • Click Import to display a window, from which you can navigate to, and select your saved file.

    Click Clear to remove text you added in the field.

  7. On the Diagnostic Options page, if you select Customize diagnostic options, you see a number of options. Equivalent to the diagnostic_option parameter of sp_opt_querystats, each option performs a set command behavior. By default, all but the last three items—execute the query, show data, and use debug mode—are selected:
    OptionDescription
    Enable statistics io
    Collects information about physical and logical I/O and the number of times a table has been accessed. The output follows the query results and provides actual I/O performed by the query.
    Enable statistics time
    Is the query execution time generated by set statistics time.
    Enable showplan
    Is the estimated plan cost calculated by the optimizer.
    Use option show_missing_stats long
    Collects information about missing statistics found for any of the tables involved in the query.
    Enable statistics resource
    Displays the compilation and execution resources used, such as procedure cache, sorting, and temporary databases.
    Enable statistics plancost
    Displays the estimated values for logical I/O, physical I/O, and row counts compared to the actual ones evaluated at each operator, and reports on CPU and sort buffer cost.
    Execute "show switches"
    Shows enabled trace flags and switches.
    Use option show long
    Is the logical operator tree for the query generated by the set option show long command. If you unselect this option, SCC uses the shorter set option show instead. The default is set option show long.
    Execute the query
    After you execute the query, the query execution time generated by set statistics time.
    Show data
    When selected, suppresses the set nodata on option.
    Use debug mode
    Collects enhanced progress information.

  8. If you selected Customize diagnostic options, clickingNext displays the Optimization Goals window. Choose the strategy that best fits your query environment:
    OptionDescription
    allrows_mix
    (Default) Instructs the query processor to allow both nested-loop joins and merge joins. The query processor measures the relative costs of each join type to determine which to use.
    allrows_oltp
    Instructs the query processor to use the nested-loop join operator.
    allrows_dss
    Instructs the query processor to use nested-loop, merge-, or hash-joins. The query processor measures their relative costs to determine which join it uses.
  9. On the Upload Options page, specify whether to upload the diagnostic data to Sybase Technical Support immediately after SCC collects the data. You can perform an upload as a separate step later.
    If you select Upload the package to SAP Customer Support, the Upload Options page displays these options:
    • (Default) Upload diagnostic data package via FTP site – if you select this option and click Next, the FTP Server page appears, and you see FTP Server on the left pane.
    • Upload diagnostic data package via e-mail notification – if you select this option and click Next, the E-mail Notification page appears, and you see E-mail Notification on the left pane.
    By default, the Upload Options page lets you delete the diagnostic data package after a successful upload. SAP Control Center skips this operation if the upload task fails.
  10. Click Next to start the collection process on the Summary page.
Related tasks
Collecting Field Diagnostic Data
Uploading Diagnostic Data Via FTP
Submitting Diagnostic Data via E-mail Message
Deleting a Diagnostic Data File
Uploading Diagnostic Data Via FTP
Submitting Diagnostic Data via E-mail Message