Optimize Indexes Workflow
-
Define a new SQL workload from one of 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.
Foglight PA Repository Collect SQL from a Foglight Performance Analysis Repository. Supports time period evaluation. Spotlight Statistics Repository Collect SQL from Spotlight Statistics Repository. Note: Depending on the workload source you select, you may need to specify a database connection.
-
Specify criteria to collect SQL statements from the specified source or database connection.
-
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 the collected SQL statements to include or exclude SQL from the SQL workload.
- Click Advanced options to modify your SQL workload before starting the index evaluation process.
-
Click
to begin the index evaluation process.
Notes:
- You can begin the index evaluation process at any time. You do not need to wait for the SQL collection process to complete.
- You can schedule the index evaluation process on a specific date and time, so that you can exit SQL Optimizer.
- A virtual line displays when an index generates and improvement is found. To view the recommended index am the impacted SQL statements, click the virtual line.