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.

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:

   Option	Description
   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:

   Option	Description
   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.