SQL Optimizer for SQL Server 10.1.2 - User Guide

Optimize Indexes for Plan Cache Workload

To define a new Plan Cache workload

1. Select Optimize Indexes.
2. Click .
3. Enter a SQL workload name and select a database connection from which to collect SQL.

4. Specify SQL filter criteria.

   Notes:

• Use the cursor to hover over an option (bar) in the graph to display option details.
• Click List statistics data to display the options for the currently selected filter criterion in a grid format
• You can display additional data in the graph (or in the grid). To do this, select/deselect data options from the group of options at the top of the page.

5. To begin collecting SQL, click .

6. Optimize Indexes immediately asks you to choose a second database connection. Select a database connection to use to retrieve execution plans and evaluate index alternatives.

   Note: This step allows you to select an alternate database (other than the one used to collect SQL) to run the index optimization process. In other words, you can collect your SQL from one database, but run the optimize indexes process on a second database. The second database must have the same application environment and data volume statistics as the first.

   The Search Process page opens. Before specifying search process criteria, you can review and modify your SQL workload.

   Note: To restart the SQL collection process, click Collected from <source> to return to the SQL Criteria page. Then click restarted.

7. Review collected SQL. To review and modify your SQL workload, click Review/adjust workload SQL. Review the following for additional information:

   SQL Tuning Set	This pane displays the list of SQL in the workload. Include—To include a SQL statement in the optimize indexes process, select the checkbox in the Include column.
   SQL Text	This pane displays the SQL text of the selected SQL statement.
   Parsed Execution Plan	This pane displays the execution plan of the selected SQL statement. SQL Statistics—Select this tab to display statistics of the selected statement.
   	Select to send your SQL to Optimize SQL. Click the arrow for more options.

Notes:

• Click to return to the SQL Collection page. (On the SQL Collection page, click Review SQL to go back to the SQL Review page).

• To collect a new SQL workload, return to the SQL Collection page and click reset the workload.

  Caution: If you click reset the workload, the SQL workload will be lost, as well as any analysis data already gathered for the current session.

To return to the Search Process page, select the Search Process tab.

8. Search process. Specify search process criteria in the Search Process Control pane. Review the following for additional information:

   Search Process Control pane	Description
   Primary goal (minimize)	Select a primary goal. SQL Optimizer evaluates indexes to optimize workload based on this primary goal.
   Enable advanced process control	Click the plus sign to enable scheduling options. Scheduled to run on—Click the link to schedule a time to run the optimization process on this workload. Select a start time, end time, duration, and interval. Stop condition—Select criteria to determine when to end the optimization process. Note: If you do not specify a Stop condition or an End date, the optimization process will run indefinitely.
   Advanced options	Click to select the number of columns in a composite index, the maximum number of indexes to recommend, and decide whether to generate clustered indexes.

9. Click Start to begin searching for new indexes to improve the workload performance.

Related Topics

View Optimize Indexes Results

About Optimize Indexes

Optimize Indexes for SQL Profiler Trace Files or Trace Tables Workload

To define a new SQL Profiler workload

1. Select Optimize Indexes.

2. Click .

   Note: SQL Optimizer provides trace templates for different versions of SQL Server. Use the cursor to hover over to display a hint on the location of a Trace Template.

3. Enter a SQL workload name and select a database connection from which to collect SQL.

4. Specify SQL filter criteria. Review the following for additional information:

   Select SQL filter criteria	Description
   Database	You must select an Database before selecting other filter criteria.
   Login Name	Use this field to filter the available SQL before selecting snapshots.
   Application Name	Use this field to filter the available SQL before selecting snapshots. Default = All

5. Select the SQL snapshots from which you want to collect SQL statements. Click a snapshot in the graph to select it. Click the selected snapshot again to deselect it.

   Notes:

• Use the cursor to hover over a snapshot to display snapshot details.
• Click List snapshot data to display snapshots in a grid format. Right-click a column heading to select options for filtering, sorting, or displaying.
• You can display additional data in the graph (or in the grid). To do this, select/deselect data options from the group of options at the bottom of the page.
• Select Total in the data options to display the total activity (the activity for all modules, actions, etc.).

6. To begin collecting SQL, click .

7. Optimize Indexes immediately asks you to choose a second database connection. Select a database connection to use to retrieve execution plans and evaluate index alternatives.

   Note: This step allows you to select an alternate database (other than the one used to collect SQL) to run the index optimization process. In other words, you can collect your SQL from one database, but run the optimize indexes process on a second database. The second database must have the same application environment and data volume statistics as the first.

The Search Process page opens. Before specifying search process criteria, you can review and modify your SQL workload.

Note: To restart the SQL collection process, click Collected from <source> to return to the SQL Criteria page. Then click restarted.

8. Review collected SQL. To review and modify your SQL workload, click Review/adjust workload SQL. Review the following for additional information:

   SQL Tuning Set / SQL Workload	This pane displays the list of SQL in the workload. Include—To include a SQL statement in the optimize indexes process, select the checkbox in the Include column.
   SQL Text	This pane displays the SQL text of the selected SQL statement.
   Parsed Execution Plan	This pane displays the execution plan of the selected SQL statement. SQL Statistics—Select this tab to display statistics of the selected statement.
   	Select to send your SQL to Optimize SQL. Click the arrow for more options.

   Notes:

• Click to return to the SQL Collection page. (On the SQL Collection page, click Review SQL to go back to the SQL Review page).

• To collect a new SQL workload, return to the SQL Collection page and click reset the workload.

  Caution: If you click reset the workload, the SQL workload will be lost, as well as any analysis data already gathered for the current session.

To return to the Search Process page, select the Search Process tab.

9. Search process. Specify search process criteria in the Search Process Control pane. Review the following for additional information:

   Search Process Control pane	Description
   Primary goal (minimize)	Select a primary goal. SQL Optimizer evaluates indexes to optimize workload based on this primary goal.
   Enable advanced process control	Click the plus sign to enable scheduling options. Scheduled to run on—Click the link to schedule a time to run the optimization process on this workload. Select a start time, end time, duration, and interval. Stop condition—Select criteria to determine when to end the optimization process. Note: If you do not specify a Stop condition or an End date, the optimization process will run indefinitely.
   Advanced options	Click to select the number of columns in a composite index, the maximum number of indexes to recommend, and decide whether to generate clustered indexes.

10. Click Start to begin searching for new indexes to improve the workload performance.

Related Topics

View Optimize Indexes Results

About Optimize Indexes

Optimize Indexes for Spotlight Statistics Repository Workload

To define a new Spotlight Statistics Repository Workload

1. Select Optimize Indexes.
2. Click .

3. Select a database connection (with a Spotlight Statistics repository) from which to collect a SQL workload.

   Note: The database must have a Spotlight Statistics repository. If no Spotlight repository is found, you will see a warning message.

4. Specify SQL filter criteria. Review the following for additional information:

   Select SQL filter criteria	Description
   Monitored Instance	You must select an Instance before selecting other filter criteria.
   Collected Date	The date when the data is collected
   Time Collected	The time when the data is collected.
   Database	The database in the instance which is monitored.

5. Select the workload from which you want to collect SQL statements. Click a workload in the graph to select it. Click the selected workload again to deselect it.

   Notes:

   • Use the cursor to hover over a workload to display details.
   • Click List statistics data to display workload statistics in a grid format. Right-click a column heading to select options for filtering, sorting, or displaying.

   • You can display additional data in the graph (or in the grid). To do this, select/deselect data options from the group of options at the top of the page.

6. To begin collecting SQL, click .

7. Optimize Indexes immediately asks you to choose a second database connection. Select a database connection to use to retrieve execution plans and evaluate index alternatives.

   Note: This step allows you to select an alternate database (other than the one used to collect SQL) to run the index optimization process. In other words, you can collect your SQL from one database, but run the optimize indexes process on a second database. The second database must have the same application environment and data volume statistics as the first.

The Search Process page opens. Before specifying search process criteria, you can review and modify your SQL workload.

Note: To restart the SQL collection process, click Collected from <source> to return to the SQL Criteria page. Then click restarted.

8. Review collected SQL. To To review and modify your SQL workload, click Review/adjust workload SQL. Review the following for additional information:

   SQL Tuning Set / SQL Workload	This pane displays the list of SQL in the workload. Include—To include a SQL statement in the optimize indexes process, select the checkbox in the Include column.
   SQL Text	This pane displays the SQL text of the selected SQL statement.
   Parsed Execution Plan	This pane displays the execution plan of the selected SQL statement. SQL Statistics—Select this tab to display statistics of the selected statement.
   	Select to send your SQL to Optimize SQL. Click the arrow for more options.

   Notes:

• Click to return to the SQL Collection page. (On the SQL Collection page, click Review SQL to go back to the SQL Review page).

• To collect a new SQL workload, return to the SQL Collection page and click reset the workload.

  Caution: If you click reset the workload, the SQL workload will be lost, as well as any analysis data already gathered for the current session.

To return to the Search Process page, select the Search Process tab.

9. Search process. Specify search process criteria in the Search Process Control pane. Review the following for additional information:

   Search Process Control pane	Description
   Primary goal (minimize)	Select a primary goal. SQL Optimizer evaluates indexes to optimize workload based on this primary goal.
   Enable advanced process control	Click the plus sign to enable scheduling options. Scheduled to run on—Click the link to schedule a time to run the optimization process on this workload. Select a start time, end time, duration, and interval. Stop condition—Select criteria to determine when to end the optimization process. Note: If you do not specify a Stop condition or an End date, the optimization process will run indefinitely.
   Advanced options	Click to select the number of columns in a composite index, the maximum number of indexes to recommend, and decide whether to generate clustered indexes.

10. Click Start to begin searching for new indexes to improve the workload performance.

Related Topics

View Optimize Indexes Results

About Optimize Indexes

View Optimize Indexes Results

You can review the results or the progress of your search for new indexes in Optimize Indexes.

To View Optimize Indexes Search Results

1. Select Optimize Indexes.

2. Click Open a Saved SQL Workload and select a workload session. Right-click and select Open to open the session.

   Note: You can view a summary of the results, or the progress of the search if it is ongoing, in the bottom pane of the Open SQL Workload window.

3. Search Process tab. In the open session, select the Search Process tab. Use this page to review details related to the search process. Each line on the graph indicates a new indexing recommendation.

   Notes:

   • Use your cursor to hover over a line on the graph to display a summary of the recommendation’s improvements (in a tool tip). Then click View details (in the tool tip) to open the Results page for the selected recommendation.
   • Select All in the Display Range box, to display all index recommendations in the graph.
   • Click List all results to display all index recommendations and their cost improvement values in a grid format. Click a recommendation in the list to open the results page for the selected recommendation.

4. Latest Results. On the Search Process page, click to view the final recommendation (or the last recommendation if the search process is ongoing).

   Review the following for additional information:

   Recommended Indexes pane	Displays a list of the indexes for the recommendation. Index Name—Click an Index name to see the index details. Tip: Select (highlight) an index in the Recommended Indexes list to highlight the SQL statements in the Impacted SQL list that are impacted by that recommended index.
   Impacted SQL pane	SQL Name—Click a SQL name to review the SQL statement and to compare the recommended execution plan to the original execution plan. Tip: Select (highlight) a SQL statement in the Impacted SQL list to highlight the indexes in the Recommended Indexes list that contributed to the performance improvement of that statement.
   Results Summary pane	Displays workload improvement results. Note: If the links at the bottom of the Results Summary pane are not in view, click and drag the pane's bottom edge down to resize.
   Publish Report	Click to view the Optimize Indexes Workload Report which you can customize and then export, print, or email.
   	Click to create a script to generate the new indexes recommended by Optimize Indexes.

   Notes:

   • To save the contents of a grid (list) as a file, right-click the grid and select Save As.
   • An open database connection is not required when opening a completed session, reviewing results, or creating and publishing (printing) a report.

   Tip: Use and to navigate back and forth between the pages of a tab.

    

   Related Topics

   About Optimize Indexes

   Create Index Scripts

   Generate an Optimize Indexes Workload Report