SQL Optimizer for SQL Server 10.1 - User Guide

Send to Optimize SQL

You can optimize SQL statements extracted by SQL Scanner using SQL Optimizer. SQL Optimizer supports single INSERT, UPDATE, SELECT, DELETE, or MERGE SQL statements.

To send a statement to SQL Optimizer

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

2. Create a scan job using the Add Scanner Jobs wizard. See "Scan SQL" in the online help for more information.

3. Select a group in the Scanner node in the Task pane.

4. Select a scan job from the Group node.

5. Select the SQL statement in the Job List pane.

6. Click in the SQL Text pane.

Notes:

• If the selected SQL statement uses a temporary table and SQL Scanner finds a CREATE TABLE or SELECT INTO statement, the User-Defined Temp Table window displays automatically. See "Use Temporary Tables in Scan SQL" in the online help for more information.
• If the selected SQL statement is used within a cursor declaration, the Cursor Settings window displays automatically. See "Optimize SQL Statements" in the online help for more information.
• If the selected SQL statement uses a variable, SQL Scanner automatically assigns the CHAR datatype to the variable when it retrieves the execution plan. To assign a different datatype, click after you send the statement to SQL Optimizer and select the new datatype in the Set Bind Variables window that displays. See "Set Bind Variables" in the online help for more information.

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, select Click here to start find SQL and select a connection. Then select Click to start finding SQL in Plan Cache.

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.
   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.

   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 reviewing Find SQL results, see the online Help.

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, select Click here to start find SQL and select a connection. Then select Click to start finding SQL in SQL Trace.

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.
   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.

   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 reviewing Find SQL results, see the online Help.

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.

Optimize SQL Statements and Execute Alternatives