Chat now with support
Chat with Support

SQL Optimizer for SQL Server 11.0 - 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/Extended Events allows you to extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profiler. It also allows you to extract SQL statements and their performance statistics from Extended Event files. You can identify the most resource-intensive SQL statements.

Additionally, you can:

  • Extract SQL executed in your server through SQL Trace or Extended Event
  • 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
    Note: This functionality is not available with extended events.
  • Save a SQL and its XML plan for others to review

To create a new SQL Trace/Extended Event 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/Extended Event search.

  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/Extended Event search.
    • Or select Click to start finding SQL in SQL Trace/Extended Event search 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/Extended Event search in the Find SQL from SQL Trace/Extended Event search panel that displays.

  4. The Add SQL Trace/Extended Event Search dialog opens. Select a trace file, table, or extended events. Review the following for more information:

    Notes:

    • You can extract SQL statements and their performance statistics from trace files, trace tables, or Extended Event.
    • You can use 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.

    Add from Extended Events Click to add SQL from Extended Event files

    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/Extended Event pane.

    Start a new search

    Select to begin a new search and to display the Add SQL Trace/Extended Event 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/Extended Event 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.

Methods for setting up SQL Trace Description

Use trace templates provided by SQL Optimizer

 Use trace templates provided by SQL Optimizer to collect SQL. You can locate the templates in the installation directory.

Use your own templates

Ensure that the following trace events and columns are captured.

Events Columns
RPC: Completed EventClass
RPC: Starting TextData
SP:Completed CPU
SP:Starting Writes
SP:StmtCompleted Reads
SQL:BatchCompleted Duration
SQL:BatchStarting SPID
SQL:StmtCompleted DatabaseName
EventSequence
LineNumber
ObjectID
ObjectName
Offset
SourceDatabaseID

 

Extended Events Templates

Find SQL requires certain event columns in your extended events in order to analyze it. For your convenience, SQL Optimizer provides Extended Event template files (.tdf) for you to use. The template files are located in the Find SQL - Extended Event 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.

Methods for setting up Extended Events Description

Use extended event templates provided by SQL Optimizer

 Use extended event templates provided by SQL Optimizer to collect SQL. You can locate the templates in the installation directory.

Use your own templates

Ensure that the following trace events and columns are captured.

Events Actions Event Fields
existing_connection Client app name Batch text
module_end Client hostname statement
module_start Database name Object type
rpc_completed Event sequence Object name
rpc_starting Server principal name Object id
sp_statement_completed Server principal id duration
sql_batch_completed Session id Logical reads
sql_batch_starting Session server principal name writes
sql_statement_completed User name Cpu time
Row count
Source database id
Nt domainname
Nt username
Line number
offset

 

Optimize SQL Statements and Execute Alternatives

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating