SQL Optimizer for SQL Server 10.1.2 - User Guide

Create Index Scripts

After Optimize Indexes finds indexes that optimize a SQL workload, you can create the scripts to use to generate the indexes in your database environment.

To create index scripts for new indexes

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.
3. Open the results page by clicking

4. Click to generate the index scripts. The Index Script page opens where you can modify scripts and index names. Review the following:

   Pane / Option	Description
   Naming Convention
   Prefix, Name, Suffix	Create a naming convention for SQL Optimizer to use to give the index a name in the script. You can include a prefix and a suffix. The naming convention is applied to all the indexes in this session.
   n Recommended Indexes
   Modify Filegroup	Click to select a filegroup for the selected index or indexes.
   Generated Index Scripts	SQL Optimizer automatically creates the index generating scripts. You can use this text editor to modify the scripts. Tip: Use the right-click menu or buttons at the bottom of the window to copy or to print the scripts.

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

Related Topics

View Optimize Indexes Results

About Optimize Indexes

Generate an Optimize Indexes Workload Report

After SQL Optimizer finds new indexes to optimize a SQL workload, you can generate a report that includes the workload summary, the recommended indexes, the index scripts, and the impacted SQL statements.

To generate an Optimize Indexes Workload Report

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.
3. Open the Results page by clicking .

4. On the Results page, in the Results Summary pane, click Publish Report. SQL Optimizer generates the report and opens the report page.

   

5. Use the report page to include/exclude content and to modify some layout and background options. Review the following toolbar icons and use them to modify and publish the report.

   	Click to specify the content to include or exclude from your report.
   	Click to modify margins and select page orientation.
   	Click to adjust the scale of the printed output.
   	Click to add a watermark to the report. To upload an image to use as the watermark, select the Picture Watermark tab.
   	Click to change the report background color.
   	Click to save the report to file using the default format. Click the arrow to select from several file format options (or to change the default setting). You can select from several different file formats, including PDF, HTML, and Excel.
   	Click to save the report to file using the default format and to also attached the file to an email. Click the arrow to select from several file format options (or to change the default setting).

To publish an Optimize Indexes Workload report as a PDF

1. On the report page, select File | Export Document | PDF File, or click and select PDF from the list.

   Tip: You can select from several different file formats for the exported (published) report, including HTML and Excel.

2. Configure the PDF Options and click OK.
3. Enter a file name and select a location.

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

Related Topics

View Optimize Indexes Results

About Optimize Indexes

Use Execution Plans

The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.

The following displays a sample execution plan in tree plan format:

Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.

SQL Optimizer executes each child operation before the parent operation. For some SQL statements, SQL Optimizer executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that SQL Optimizer retrieve all rows from the child operation before it executes the parent operation.

The annotated execution plan includes the following information for each step:

• Execution order number

• Join syntax (annotated)

• Filter syntax (annotated)

• Object name

• Table access

• Index scan

• Cost

• Partition name

About Execution Plans (Optimize Indexes)

The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.

The following displays a sample execution plan in tree plan format:

Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.

SQL Optimizer executes each child operation before the parent operation. For some SQL statements, SQL Optimizer executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that SQL Optimizer retrieve all rows from the child operation before it executes the parent operation.

The annotated execution plan includes the following information for each step:

• Execution order number

• Join syntax (annotated)

• Filter syntax (annotated)

• Object name

• Table access

• Index scan

• Cost

• Partition name