Chat now with support
Chat with Support

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

Find SQL - Homepage

The Find SQL homepage varies depending on whether or not you have used the Find SQL module before.

  • If you are using Find SQL for the first time, the homepage allows you to add a server connection and start you first session.
  • If you are creating a subsequent session, the homepage allows you to review previous search sessions, start a new search, and add servers.

Homepage - First Session

To start creating search sessions, add a server connection. Your new saved search sessions in Find SQL will be organized by server.

To add a server connection and start your first session

  1. After selecting the Find SQL tab, select Click here to start find SQL to create your first search session.
  2. Then select a connection.

After you create a new session, another window displays two panels. The left side displays the Find SQL from Plan Cache panel, and the right side displays the Find SQL from SQL Trace panel. Select which type of search to create for your first session.

To start first session - Find SQL from Plan Cache

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

To start first session - Find SQL from SQL Trace

Homepage - Subsequent Sessions

Upon subsequent sessions, the homepage displays a list of servers and recent searches—for both Plan Cache and SQL Trace sessions.

You can start a new search, add a server, or review a saved search. Review the following for additional information:

Left Panel

Use this panel to start a new search

My Servers

Displays list of your servers on which you have conducted searches.

From this panel you can do the following:

  • Start a new search on a server—Click the server name.
  • Start a search on a new server—Click Find SQL in another server.

Tip: Hover over a server name to display the connection profile.

Show all servers

Displays the full list of servers you have used. Click the link to start a new search from an existing server.

Note: Right-click the server name to delete the selected server.

Right Panel

Use this panel to review searches

Recent Searches

Displays most recent searches.

Tip: Click the search name to display search details.

Show all searches

 Display the full list of searches in each server.

For detailed information about creating new search sessions, see the following:

  

Related Topics

About Finding SQL

Select Connection for Plan Cache

Find SQL from Plan Cache

You can use Find SQL to inspect and collect SQL from the SQL Server Plan Cache.

Find SQL from Plan Cache evaluates existing SQL performance according to 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 only supports SQL Server 2005 (Service Pack 2) or later.

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:

  

Related Topics

Find SQL - the Homepage

Start finding SQL from Plan Cache

You can use Find SQL to inspect and collect SQL from the SQL Server Plan Cache.

Find SQL from Plan Cache evaluates existing SQL performance according to 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 only supports SQL Server 2005 (Service Pack 2) or later.

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:

  

Related Topics

Find SQL - the Homepage

Select Connection

Note: This topic may not include a description for every field in the screen or dialog.

To select a connection

  1. Select Find SQL.

  2. Select Click here to start Find SQL.

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

    Password

    		 Password
  4. Click OK.

  

Related Topics  

About Finding SQL

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating