Optimize Indexes analyzes a SQL workload or any group of SQL statements and determines the best index set for the workload or group of statements. You can instruct SQL Optimizer to gather SQL from an application workload during a specified time period. SQL Optimizer evaluates your SQL workload and provides you with the best set of indexes to optimize database performance for that workload. After this evaluation process is complete, you can review the results and then select the new indexes you want to save and test.
You can instruct SQL to gather a SQL workload from any of the following sources:
After collecting the statements, SQL Optimizer provides you with the execution plans and run-time statistics. Select the statements for which you want to optimize indexes. After running the evaluation process, review the results to determine the performance improvements.
Things to consider when using Optimize Indexes:
When evaluating your SQL workload, SQL Optimizer creates virtual indexes. No indexes are created, so this process does not significantly affect database performance.
Note: To optimize indexes for a single SQL statement, use Optimize SQL. See About Optimizing SQL (SQL Rewrite) for more information.
To select a connection
Review the following for additional information:
Click to select a previously created connection.
Tip: Click to open the Connection Manager to create a new connection. See Connect to SQL Server for more information.
|Select Database and User||Description|
|Click to select the database to match your SQL statement.|
|Click to select your user name.|
You can instruct SQL Optimizer to gather a SQL workload from one of several different types of SQL sources/locations. The first step in defining a new SQL workload is to identify the type of SQL workload source.
To select a SQL source type and open a new workload session
Select the type of SQL workload source from which you want to collect SQL. Select from the following:
|SQL Workload Source||Description|
|Scan Code||Scan SQL statements from file, database object source, or clipboard.|
|Plan Cache||Collect SQL from the Plan Cache.|
Collect SQL with built-in SQL Profiler or load SQL from a trace file or a trace table.
|Spotlight Statistics Repository||Collect SQL from Spotlight Statistics Repository.|
Specify the criteria for collecting a SQL workload. For specific instructions on specifying criteria for collecting SQL and for optimizing indexes for the type of SQL source you selected, see one of the following:
To scan code for a new workload
Depending on the method you select, either browse to a file or a database object, or paste SQL from the clipboard.
In the SQL Selector dialog, you can select which SQL statements to include and which to exclude.
Note: Enter a value for the Relative Execution Frequency (R.E.F.). This is an approximation of the number of times a SQL statement is executed relative to the other statements.
Tip: You can collect statements from more than one source.
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.
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.
- 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.
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.
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.