About Optimize Indexes
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:
- Scan Code — Scan SQL statements from file, database object source, or clipboard.
- Plan Cache — Collect SQL from the Plan Cache
- SQL Profiler — Collect SQL from a trace table or trace file.
- Spotlight Statistics Repository — Collect SQL from Spotlight Statistics Repository.
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.
Considerations
Things to consider when using Optimize Indexes:
- When analyzing workloads, SQL Optimizer uses proprietary, modified genetic algorithms (patent pending) to continuously improve its results. Therefore, the longer the evaluation process runs, the better the result.
- While performing a search for new indexes to optimize performance, SQL Optimizer updates its recommendations over time to reflect any changes in data distribution or configuration.
- You can schedule the evaluation process to run once, continuously, or periodically at defined intervals.
- You can stop the process manually or configure it to stop automatically when specific goals are met.
- The evaluation process can be paused and resumed.
- Optimize Indexes' evaluation of SQL and search for new indexes is a low-overhead process.
- Select, update, delete, insert, and merge statements are supported.
- Only one index search process can be active at any one time. However, multiple SQL workloads can be opened and reviewed concurrently.
About the Evaluation Process
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.
Optimize Indexes for Spotlight Statistics Repository Workload
Optimize Indexes for Plan Cache Workload
Optimize Indexes from Scanned Code Workload
Select Connection
This topic may not include a description for every field in the screen or dialog.To select a connection
- Select the Optimize SQL tab in the main window.
-
Click
-
Review the following for additional information:
Select Connection Description Connection
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 Database
Click Set User
Click
Select a SQL Workload Source
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 Optimize Indexes.
- Click Define New SQL Workload.
-
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. SQL Profiler 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:
Optimize Indexes from Scanned Code Workload
To scan code for a new workload
- Select Optimize Indexes.
- Click
.
- After connecting to a database source, select one of the Scan from methods for collecting SQL.
-
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.
- Click
.
-
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 /
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.
-
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.
- Click Start to begin searching for new indexes to improve the workload performance.
