SQL Optimizer for SQL Server 10.1 - User Guide

Optimize Indexes Tutorial

To optimize indexes

1. Select the Optimize Indexes tab.

2. Click Define New SQL Workload.

3. Select the type of SQL workload source from which you want to collect SQL.

   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.

   Note: Depending on the workload source you select, you may need to select a database connection from which to collect SQL.

4. Specify the SQL filter criteria.

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 during the collection process. Before specifying search process criteria, you can review and modify your SQL workload.

   Notes:

   • To restart the SQL collection process, click Collected from <source> to return to the SQL Criteria page. Then click restarted.
   • Click Advanced options to modify your SQL workload before starting the index evaluation process.

7. Click to begin the index evaluation process.

   Note: You can schedule the index evaluation process on a specific date and time.

8. Select the Search Process tab to review details related to the search process. Each line on the graph indicates a new indexing recommendation.
9. Select the Results tab to view the final recommendation (or the last recommendation if the search process is ongoing).