SQL Optimizer for SQL Server 10.1.2 - User Guide

Compare Plan Cost

SQL Optimizer calculates the estimated cost for each SQL statement. Statements with high estimated cost use more system and database resources. SQL Optimizer calculates the cost for each step in the execution plan and totals the cost to provide a resource usage estimate for the entire statement. You can use this estimate to identify the most costly step in the execution plan.

Estimated cost can only be used to compare SQL statements that retrieve the exact same result. Comparisons of estimated cost for statements that do not retrieve the same results are not valid.

Important: The cost only provides an estimate of resource usage to execute a SQL statement. Since statements with higher cost may perform better, you should test generated alternatives to determine the best statements for your database environment.

Related Topics

Get Execution Plans

View Missing Indexes

You can use the Missing Indexes feature to help you determine which indexes improve the performance of a query. Missing index information displays in the Execution Plan.

Note: Missing index information is provided by Microsoft SQL Server^®.

To view missing indexes

»

Select .

Notes:

• The number of missing indexes recommended by SQL Server displays in the bottom of the Execution Plan panel.
• Hover over the Missing index(es) field to display plan information.

To create a user-defined index alternative

1. Right-click the plan and select View missing index details.
2. Click the Create indexes as User Defined Alternative icon.

To explore different index alternatives

Note: You can also click the Index icon on the main toolbar.

Set Bind Variables

You need to define the values of SQL statement variables before you optimize the statement. The Set Bind Variables window displays automatically when you use SQL Optimizer for a statement with variables.

Troubleshooting: If the Set Bind Variables window displays when you optimize a statement without variables, make sure you spelled the column and table names correctly, you selected the correct database or user, and you selected a table or column that exists in the database.

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

To set a bind variable

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

2. Enter a SQL statement with a bind variable.

   Note: SQL Optimizer currently does not support the following bind variables data type:

   • cursor

   • hierarchyid

   • table

   • geography

   • geometry

3. Click .

4. Review the following for additional information:

   Bind Variables List	Description
   Datatype	Click and select the variable datatype.
   Null	Select the checkbox if the value for the variable is null.
   Variable Value	Enter a value for the variable.
   Browse Data	Description
   SELECT	Click and select column references for the variables. Note: SQL Optimizer selects the All Columns checkbox by default.
   FROM	Click and select table references for the variables. Tip: Click to browse to tables in a different database.
   WHERE	Enter a WHERE clause or click to select a previously entered clause.
   ORDER BY	Enter a ORDER BY clause or click to select a previously entered clause.

Related Topics

About SQL Optimization

Optimize SQL Statements

Test Run SQL Alternatives

View Cursor Settings and Bind Variables

Retrieve Run Results

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

To retrieve run results

1. Right-click the Alternative Details or SQL Text pane and select Run Result.

2. Click .

   Tip: Click to copy the SQL statement to execute in another Quest Software product.