Plan Control
Plan Control uses the Plan Guides Management feature introduced in SQL Server 2005. You can use this feature to optimize execution plans and to deploy plan guides for SQL statements without changing the original source code. This feature is particularly useful for third-party applications where you do not have access to the source code.
The process begins with SQL Optimizer analyzing your original SQL statement to generate execution plan alternatives. SQL Optimizer then applies a set of SQL Server hints to each execution plan alternative to generate additional alternatives. Once SQL Optimizer completes this process, you can execute the alternatives to retrieve run time statistics for each execution plan alternative and compare the alternatives to identify the best one for your database environment. You can then deploy the selected alternative as a plan guide to the Manage Plan Guides module.
About Optimizing SQL (Plan Control)
Plan Control uses the Plan Guides Management feature introduced in SQL Server 2005. You can use this feature to optimize execution plans and to deploy plan guides for SQL statements without changing the original source code. This feature is particularly useful for third-party applications where you do not have access to the source code.
The process begins with SQL Optimizer analyzing your original SQL statement to generate execution plan alternatives. SQL Optimizer then applies a set of SQL Server hints to each execution plan alternative to generate additional alternatives. Once SQL Optimizer completes this process, you can execute the alternatives to retrieve run time statistics for each execution plan alternative and compare the alternatives to identify the best one for your database environment. You can then deploy the selected alternative as a plan guide to the Manage Plan Guides module.
Select Connection
This topic may not include a description for every field in the screen or dialog.To select a connection
- Select the Optimize SQL tab in the main window.
-
Click
-
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. See Connect to SQL Server for more information.
Select Database and User Description Database
Click Set User
Click
Generate Execution Plan Alternatives
Plan Control sessions optimize your SQL statement by generating execution plan alternatives for the statement. You can then select an alternative execution plan for your SQL statement and deploy it as a plan guide to improve database performance.
To generate execution plan alternatives
-
Select the Optimize SQL tab in the main window.
-
Select Plan Control from the Optimize SQL start page.
Note: If the start page does not display, click the arrow beside
and select New Plan Control Session.
-
Enter a SQL statement in the Original SQL pane.
- Click
to retrieve the execution plan for your SQL statement. The Select Connection window displays.
- Select a connection. See Select Connection for more information.
-
Click
to generate execution plan alternatives.
Tip: Click
to abort the process.
- When the generation process is finished, select a plan alternative in the Plans pane and review the plan in the Execution Plan pane.
-
You can then test run the generated plan alternatives. See Test Run Execution Plan Alternatives for more information.
To clear the Plan Control window
To clear the results, right-click inside the Original SQL pane and do one of the following:
- Select Clear Optimization Results | Keep Original Scenario to clear only the results and retain both the original SQL statement and the database connection.
- Select Clear Optimization Results | Clear Original Scenario to clear the Plan Control window, but retain the database connection.