SQL Optimizer for SQL Server 10.1.2 - User Guide

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. See Connect to SQL Server for more information.
   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.

Related Topics

About Generating Index Alternatives

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 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. See Connect to SQL Server for more information.
   Select Database and User	Description
   Database	Click to select the database to match your SQL statement.
   Set User	Click to select your user name.

Tutorial: Find SQL from Plan Cache

Find SQL helps you locate the most resource-intensive SQL in your server. It evaluates existing SQL performance by different statistics (such as CPU time and elapsed time) from different areas (such as batches and database objects).

Note: The Find SQL from Plan Cache module supports only SQL Server 2005 (Service Pack 2) or later.

Additionally, you can:

• Extract from Plan Cache any SQL executed in your server
• Review execution statistics and query plans of the SQL
• Check resource consumption at SQL, Batch, and Database Object levels
• Optimize the performance of your SQL by sending them to Optimize SQL or Optimize Indexes
• Save a SQL and its XML plan for others to review

To create a new Plan Cache search session

1. Select Find SQL.

   Note: If you are creating your first search session, see Find SQL - the Homepage.

2. To conduct a search on a server for which you have previously conducted a search, select the server from the list. Or click Show all servers to view entire list and select the server.
   • Then click Start a new Plan Cache search.
   • Or select Click to start finding SQL in Plan Cache if you have never conducted a Plan Cache search on this server.
3. To conduct a search on a new server, click Find SQL in another server.
   1. Select a connection. See Select Connection for Plan Cache.
   2. Select Click to start finding SQL in Plan Cache in the Find SQL from Plan Cache panel that displays.

4. The Add Plan Cache Search dialog opens. Select criteria for collecting SQL. Review the following for additional information:

   Criteria to collect SQL	Description
   Databases	Click the link and select from the following options: All (excluding system databases) All (including system databases) Specific databases only—Select the databases to include in the search.
   Select the number of SQL to retrieve	Click the link to specify the number of SQL and the retrieval method. Note: The second option allows you to specify the number of SQL according to a specific criterion.

5. Click OK. The session name is added to the list of saved sessions and the search process starts.

6. A search progress bar displays next to the search name during the search process. The following information and options are displayed in the Find SQL from Plan Cache pane.

   Start a new search	Select to begin a new search and to display the Criteria to collect SQL dialog box.
   Search name	Name you have given to your search. Click to open the dashboard-style Search Result page for this search. Notes: The default format is the current date and time. Right-click the name to rename or delete the search.
   Status	Displays status of search: Collecting Note: Click to abort search. Complete
   Show all Plan Cache searches in this server	Select to display all the searches you have conducted on the selected server. Note: Right-click the search to delete the selected search.

7. When the search is finished, a dashboard-style Search Results page displays. See Search Results for more information.

   Click a pie chart to view the top-consuming SQL for that resource. Each pie chart represents a different view of the most-expensive (top-consuming) SQL. In each view, the SQL statements are filtered by a different resource consumed and by a different SQL location.

8. After viewing top-consuming SQL on the Summary Chart page, to return to the dashboard, click or click the session name in the breadcrumb.
9. For more information about the different views, see the following:
   • Top SQL - Summary Chart View
   • Top Batches - Summary Chart View
   • Top Database Objects - Summary Chart View

Tutorial: Find SQL from SQL Trace

Find SQL from SQL Trace allows you to extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profiler. You can identify the most resource-intensive SQL statements in your traces.

Additionally, you can:

• Extract SQL executed in your server through SQL Trace
• Review execution statistics and query plans of the SQL
• Check resource consumption in SQL, Batch and Database Object levels
• Optimize the performance of your SQL by sending them to Optimize SQL or Optimize Indexes
• Save a SQL and its XML plan for others to review

To create a new SQL Trace search session

1. Select Find SQL.

   Note: If you are creating your first search session, see Find SQL - the Homepage.

2. To conduct a search on a server for which you have previously conducted a search, select the server from the list. Or click Show all servers to view entire list and select the server.
   • Then click Start a new SQL Trace search.
   • Or select Click to start finding SQL in SQL Trace if you have never conducted a SQL Trace search on this server.
3. To conduct a search on a new server, click Find SQL in another server.
   1. Select a connection. See Select Connection.
   2. Select Click to start finding SQL in SQL Trace in the Find SQL from SQL Trace panel that displays.

4. The Add SQL Trace Search dialog opens. Select a trace file or table. Review the following for more information:

   Notes:

   • You can extract SQL statements and their performance statistics from trace files or trace tables.
   • You can use trace templates provided by SQL Optimizer or use your own templates.

   Add from Trace Files	Click to add SQL from trace files.
   Add from Trace Tables	Click to add SQL from trace tables. The Add Trace Table dialog opens. Select a connection. Connection—Select the connection where the trace table is located. Database—Specify the database where the trace table is located. Schema—Specifies the owner of the trace table. Then select a trace table. Note: Find SQL requires certain event columns in your trace in order to analyze it. For convenience, SQL Optimizer provides trace templates for you to use. These template files are located in the Find SQL - Trace Templates folder in the SQL Optimizer installation directory. See SQL Trace Templates for more information.
   Show Collect Criteria	Click to filter the SQL to collect. You can filter by: Application Name Database Name Login Name Host Name

5. Click OK. The session name is added to the list of saved sessions and the search process starts.

6. A search progress bar displays next to the search name during the search process. The following information and options are displayed in the Find SQL from SQL Trace pane.

   Start a new search	Select to begin a new search and to display the Add SQL Trace Search dialog box.
   Search name	Name you have given to your search. Click to open the dashboard-style Search Result page for this search. Notes: The default format is the current date and time. Right-click the name to rename or delete the search.
   Status	Displays status of search: Collecting Note: Click to abort search. Complete
   Show all SQL Trace searches in this server	Select to display all the searches you have conducted on the selected server. Note: Right-click the search to delete the selected search.

7. When the search is finished, a dashboard-style Search Results page displays. See Search Results (SQL Trace) for more information.

   Click a pie chart to view the top-consuming SQL for that resource. Each pie chart represents a different view of the most-expensive (top-consuming) SQL. In each view, the SQL statements are filtered by a different resource consumed and by a different SQL location.

8. After viewing top-consuming SQL on a Summary Graph page, to return to the dashboard click or click the Search session name in the breadcrumb.
9. For more information about the different views, see the following:
   • Top SQL - Summary Chart View (SQL Trace)
   • Top Batches - Summary Chart View (SQL Trace)
   • Top Database Objects - Summary Chart View (SQL Trace)

SQL Trace Templates

Find SQL requires certain event columns in your trace in order to analyze it. For your convenience, SQL Optimizer provides trace template files (.tdf) for you to use. The template files are located in the Find SQL - Trace Templates folder in the SQL Optimizer installation directory.

If you create your own template, make sure it defines the events and columns listed in the following table.