Page History

Wiki MarkupAs you add SQL statements to the job on the Overview tab of the tuner, tuning-supported DML statements (SELECT, INSERT, DELETE, and UPDATE as well as MERGE on SQL Server 2008 and above) are parsed from the statements and added to the Overview tab in preparation for the tuning function execution. \\

Each tuning source statement is listed by Name, Schema, Text, Tables and Views. For SQL Server and Sybase platforms, there is also a Catalog column. Additionally, each statement will have Time and Analysis values that approximate how efficiently they execute on the specified data source. \\

In the Generated Cases area of the Overview tab of a tuning job, the Cost and Execution Statistics columns let you compare the relative efficiency of SQL statements or statement cases. While the explain plan Cost for a statement or case is calculated when you add SQL to a tuning job, the Elapsed Time and Execution Statistics (and Other Execution Statistics columns, if available) columns are not populated until you execute that statement or case. \\

If the Tuning Status Indicator indicates that a statement or case is ready to execute, you can execute one or more statements on the * Overview * tab. Alternatively, the Tuning Status Indicator may show that you have to correct the SQL or set bind variables before you can execute. \\

Once the tuning job has run, the Overview tab provides a series of cases, per statement, that you can select and modify based on your results. \\

In some cases, automatic case generation might be disabled (via the Preferences panel). If this is true, or if you have otherwise modified the Generated Cases table and can no longer generate a specific case, you can instead explicitly generate a case for specific statements. \\ !worddavf95e8fe3fe9ee8255df9f0a58e664e7d.png|height=40,width=405! \\ \\ \\ *To* *execute* *a* *tuning* *job:* 1Ensure you have registered and selected a data source. For more information, see [Register Data Sources|REGISTER DATA SOURCES] and [Specify a Data Source|SPECIFY A DATA SOURCE]. \\ 2Ensure you are connected to the database by double clicking the database name in the Data Source Explorer. \\ 3Click the tuning icon on the toolbar, or click *File* *>* *New* *>* *Tuning* *Job{*}. \\ 4On the *Overview* tab, specify the SQL you want to tune: \\ 5Modify the number of times to execute each statement in the *Execute* *each* *generated case* field at the top right of the tuner, as needed. \\ 6Click the execution button \[ !worddav6b860bce0f529e3b09a98170a9eea8d0.png|height=31,width=30! \] on the right side of the case generation field. \\ The tuning job runs, exacting and analyzing each statement and providing values in the appropriate columns. \\ \\ *To* *explicitly generate* *a* *case* *for* *a* *specific* *statement:* \\ 1Ensure you are connected to the database by double clicking the database name in the Data Source Explorer. \\ 2Click the *Overview* tab. \\ 3In the *Generated* *Cases* area, right-click in the *Name* field of a statement or transformation case and select *Generate* *Cases* from the context menu, or click the *Overview Run* *Job* icon. The specified case is generated. \\ *To* *view* *the* *generated cases* *for* *a* *specific* *statement* \\ 1In the *Tuning* *Statements* area, click the checkbox to the left of the tuning source statement name. \\ A check mark appears in the checkbox and the cases displayed in the Generated Cases area are filtered to display only those cases related to the selected source statement. \\ !worddavc4f3f47c13603c9ea666a8a23167f07e.png|height=367,width=622! \\ \\statements.

Image Added

To execute a tuning job

1. Ensure you have registered and selected a data source. For more information, see Register Data Sources and Specify a Data Source.
2. Ensure you are connected to the database by double clicking the database name in the Data Source Explorer.
3. Click the tuning icon on the toolbar, or click File > New > Tuning Job.
4. On the Overview tab, specify the SQL you want to tune:
   • Modify the number of times to execute each statement in the Execute each generated case field at the top right of the tuner, as needed.
5. Click the execution button [Image Added ] on the right side of the case generation field.
6. The tuning job runs, exacting and analyzing each statement and providing values in the appropriate columns.

To explicitly generate a case for a specific statement

1. Ensure you are connected to the database by double clicking the database name in the Data Source Explorer.
2. Click the Overview tab.
3. In the Generated Cases area, right-click in the Name field of a statement or transformation case and select Generate Cases from the context menu, or click the Overview Run Job icon. The specified case is generated.

To view the generated cases for a specific statement

1. In the Tuning Statements area, click the checkbox to the left of the tuning source statement name.
   A check mark appears in the checkbox and the cases displayed in the Generated Cases area are filtered to display only those cases related to the selected source statement.
   Image Added