Getting recommendations for tuning query workloads

Run the workload advisors to get recommendation for improving the performance of SQL statements in a query workload.

Before you begin

About this task

The Workload Index Advisor and Workload Query Advisor require up-to-date statistics for the database objects that are in the access plans for the SQL statements that are in the workload. Before running the Workload Index Advisor and Workload Query Advisor, run the Workload Statistics Advisor and run the RUNSTATS command that it generates.

Procedure

To get recommendations from the workload advisors:

  1. From the Manage and Tune Workloads page or the Schedule page of the Manage section, click Invoke Advisors and Tools to open the Run Workload Advisors page for the selected or current workload.
  2. Optional: Change default values for options of the Workload Statistics Advisor. Under Workload, click Set Advisor Options on the left side of the Invoke section.
  3. Follow one of these steps:
    • To get recommendations from the Workload Statistics Advisor immediately, follow these steps:
      1. Click the Select What to Run button.
      2. Select Workload Statistics Advisor and deselect the other options.
      3. Click OK.
    • To schedule the Workload Statistics Advisor to run at a specific time, follow these steps:
      1. On the Schedule page of the Manage section, click Schedule Workload Advisors to open the Advisors window.
      2. Select and schedule the Workload Statistics Advisor, and deselect the other options.
      3. Set a schedule for running the advisor.
      4. Click OK.
  4. Run the RUNSTATS command that the Workload Statistics Advisor recommends.
  5. Click the Invoke tab.
  6. Optional: Change default values for options of the Workload Index Advisor.
  7. Follow one of these steps:
    • To get recommendations from the Workload Index Advisor and Workload Query Advisor immediately, follow these steps:
      1. Click the Select What to Run button.
      2. Select Workload Index Advisor and Workload Query Advisor, and deselect the other options.
      3. Click OK.
    • To schedule the Workload Index Advisor and Workload Query Advisor to run at a specific time, follow these steps:
      1. On the Schedule page of the Manage section, click Schedule Workload Advisors to open the Advisors window.
      2. Select and schedule the Workload Index Advisor and Workload Query Advisor, and deselect the other options.
      3. Click OK.

Feedback