SQL Optimizer for SQL Server 11.0 - User Guide

About Generating Index Alternatives

SQL Optimizer analyzes the following in your original SQL statement and table references to generate index alternatives:

• SQL statement syntax
• Related tables and indexes
• Search arguments
• Table join conditions

Once SQL Optimizer generates alternatives, you can test them to evaluate improvements in database performance.

Note: SQL Optimizer does not physically create indexes on your database when generating alternatives.

Index Generation for SQL Server 2005 or later

When connected to Microsoft SQL Server 2005 or later, SQL Optimizer uses virtual indexes and Database Engine Tuning Advisor (DTA) information to provide index alternatives. SQL Optimizer generates virtual indexes and combines them into index sets. DTA information for the original SQL statement is then retrieved using each index set. If the DTA information indicates the indexes can be used by the SQL statement, the index set is saved. Index sets that cannot not be used are automatically eliminated. This ensures SQL Optimizer only provides index sets that impact performance.

Tips:

• When SQL Optimizer generates virtual index alternatives, it also creates virtual execution plans for each index set. You can create temporary indexes on your database to retrieve the actual execution plans for the index sets. See "Fill Missing Execution Plans" in the online help for more information.

• Once you retrieve the execution plans, you can use the plan cost to help you select alternatives to test with your database environment. See "Compare Plan Cost" in the online help for more information.

Index Generation for SQL Server 2000

When connected to Microsoft SQL Server 2000, SQL Optimizer provides you with individual index alternatives to evaluate, test or implement. Since SQL Server 2000 cannot create virtual indexes, DTA information is not used to eliminate alternatives that do not impact performance.

Tutorial: Index Alternatives

SQL Optimizer analyzes the following in your original SQL statement and table references to generate index alternatives:

• SQL statement syntax
• Related tables and indexes
• Search arguments
• Table join conditions

Once SQL Optimizer generates alternatives, you can test them to evaluate improvements in database performance.

To generate and benchmark an index alternative

1. Select the Optimize SQL tab in the main window.

2. Enter a SQL statement in the Alternative Details pane of the SQL Details tab.

3. Click . The Select Connection window displays.

4. 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.
   Select Database and User	Description
   Database	Click to select the database to match your SQL statement.
   Set User	Click to select your user name.

5. Click to generate index alternatives or click to generate index alternatives and optimize the SQL statement in one step.
6. Benchmark index alternatives with the original SQL statement. The Test Run function provides an efficient way to test index alternatives. It physically creates the index on the database, runs the SQL statement, and drops the indexes after the test is finished.

   Important: Since indexes are physically created on the database, this process may impact database performance and the performance of other SQL statements.

   1. Select the index alternative to test.

   2. Click . Execution statistics display in the Alternatives pane once the test is finished. Use the tabs available in the Execution Plan pane to view more information about the index alternative.

Tutorial: User-Defined Temp Tables

When your SQL statement uses a temp table, SQL Optimizer requires you to create the temporary table before optimizing the SQL statement or generating index alternatives. SQL Optimizer automatically drops all temp tables created when you close your session.

To create a temporary table

1. Select the Optimize SQL tab in the main window.

2. Click at the bottom of the Alternative Details pane. The Temp Table Manager window displays.

3. Enter a SQL statement to create a temporary table. Review the following for additional information:

   Temp Table Manager	Description
   SQL Script Editor	Allows you to enter SQL statements to create temporary tables. Tip: Click to open a file with your SQL statement.
   SQL Script	Displays the SQL script for the temporary table you select.
   Temp Table List	Displays a list of temporary tables for your current session. Tip: Click to drop all temporary tables for your current session.
   Note: If the selected SQL statement uses a variable, the Set Bind Variables window displays so you can define the variable. See "Set Bind Variables" in the online help for more information.

4. Click . The Select Connection window displays. 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.
   Select Database and User	Description
   Database	Click to select the database to match your SQL statement.
   Set User	Click to select your user name.

Compare SQL Alternatives

Use the Compare window to view the SQL text, execution plan, and execution statistics for your original SQL statement and all alternatives SQL Optimizer generated. The Compare window consists of the Alternatives window and the Comparison window. The Alternatives window displays execution statistics and the Comparison window displays SQL statements and execution plans. You can compare your original SQL statement with an alternative SQL Optimizer generated or compare two different alternatives.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To compare SQL statements

1. Select the Optimize SQL tab in the main window.

2. Click .

3. Select an alternative in the Alternatives window to compare the statement with your original SQL statement.

   Note: The Comparison window displays the SQL text and execution plan for the original SQL statement in both panes by default.

   Tip: The alternative you selected is shown under your original SQL statement in the Alternatives window by default. To unfreeze the alternative selected, right-click the alternative and select Unfreeze Comparing Rows.

4. Click to switch the location of the original SQL statement and selected alternative in the Comparison window.

   Note: Change the location of the original SQL statement to compare two alternative statements. Once you select the first alternative, click and select the second alternative.

5. Click the current layout option in the Comparison Window to change the layout for the statements you are comparing. Review the following for additional information:

   SQL and Plan (Left-Right)	Displays the SQL text and execution plan for the statements you are comparing side by side.
   SQL Only (Left-Right)	Displays the SQL text for the statements you are comparing side by side.
   Plan Only (Left-Right)	Displays the execution plan for the statements you are comparing side by side.
   SQL and Plan (Top-Bottom)	Displays the SQL text and execution plan for the statements you are comparing beneath each other.
   SQL Only (Top-Bottom)	Displays the SQL text for the statements you are comparing beneath each other.
   Plan Only (Top-Bottom)	Displays the execution plan for the statements you are comparing beneath each other.
   Tips: Click to maximize the comparison window. Click to restore the comparison window to its original size.