立即与支持人员聊天
与支持团队交流

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

Execute Statements and View Results

Use SQL Optimizer to execute SQL statements and view run time results. You can execute your original SQL statement or a statement alternative that SQL Optimizer generates. You can also copy a SQL statement to execute in another Quest Software product such as Toad.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To execute a statement

  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. Right-click the Alternative Details pane and select Run Result.

    Tip: To execute a SQL statement alternative, select the alternative after you optimize your original SQL statement. See Optimize SQL Statements for more information.

  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 .

    Tip: Click to copy the SQL statement to execute in another Toad product.

  

Related Topic  

Create Virtual Indexes

Once you create a user defined alternative in Optimize SQL, you can create and test virtual indexes to compare SQL statement performance with different indexes.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To create a virtual index for a user defined alternative

  1. Select the Virtual Indexes tab in the Alternative Details pane.

    Note: The Virtual Indexes tab displays after you create a user defined alternative.

  2. Click to create and edit virtual indexes for the user defined alternative.

  3. Click if you want to configure advanced options. Click the following to display the options available for each version of SQL Server.

  4. Select the Index Script tab in the Alternative Details pane to view the SQL script for your virtual indexes.

    Note: The Index Script tab only displays after you create virtual indexes.

  

Related Topics  

 

 

Set Bind Variables

You need to define the values of SQL statement variables before you optimize the statement. The Set Bind Variables window displays automatically when you use SQL Optimizer for a statement with variables.

Troubleshooting: If the Set Bind Variables window displays when you optimize a statement without variables, make sure you spelled the column and table names correctly, you selected the correct database or user, and you selected a table or column that exists in the database.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set a bind variable

  1. Select the Optimize SQL tab in the main window.
  2. Enter a SQL statement with a bind variable.

    Note: SQL Optimizer currently does not support the following bind variables data type:

    • cursor

    • hierarchyid

    • table

    • geography

    • geometry

  3. Click .

  4. Review the following for additional information:

    Bind Variables List Description

    Datatype

    Click and select the variable datatype.

    Null

    Select the checkbox if the value for the variable is null.

    Variable Value

    Enter a value for the variable.

    Browse Data Description

    SELECT

    Click and select column references for the variables.

    Note: SQL Optimizer selects the All Columns checkbox by default.

    FROM

    Click and select table references for the variables.

    Tip: Click to browse to tables in a different database.

    WHERE

    Enter a WHERE clause or click to select a previously entered clause.

    ORDER BY

    Enter a ORDER BY clause or click to select a previously entered clause.

 

Related Topics

Test for Scalability

When you optimize a SQL statement, it is important to consider the scalability of the alternatives SQL Optimizer generates before you implement them in your production database. SQL performance and scalability are two important factors that determine a database application's ability to handle an increase user load. The performance of a SQL statement changes as the number of users increase. Performance problems can arise when you use certain SQL statements under a heavy user load.

To test for scalability

  1. Select the Optimize SQL tab in the main window.
  2. Enter a SQL statement in the Alternative Details pane.

  3. Click . The Select Connection window displays.

  4. Select a connection to use. 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 optimize the SQL statement or click to optimize the SQL statement and generate index alternatives in one step.

  6. Click .

  7. Review the following for additional information:

    Minimum number of virtual users

    Enter the minimum number of virtual users to use for scalability testing.

    Range: 1 to 1,000,000,000

    Maximum number of virtual users

    Enter the maximum number of virtual users to use for scalability testing.

    Range: 1 to 1,000,000,000

    Step value of virtual users

    Enter the number of virtual users to increase by for each step of scalability testing.

    Range: 1 to 1,000,000,000

    Distribution Model

    Select a latency think time distribution model:

    • None—Does not insert a latency think time between executions.
    • Absolute—Uses the value entered in the Duration field as the latency think time between executions.
    • Uniform—Uses a random duration between 1 millisecond and the value entered in the Duration field as the latency think time.
    • Negative Exponential—Uses a random duration based on a mathematical model weighted towards lower latency think times. The value entered in the Duration field is used for calculating think times.
    • Normal—Uses a random duration based on a mathematical model weighted towards average latency think times. The value entered in the Duration field is used for calculating think times.
    • Poisson—Uses a random duration based on a mathematical model weighted towards discrete latency think times. The value entered in the Duration field is used for calculating think times.
    Duration (milliseconds)

    Enter a duration in milliseconds.

    Note: How SQL Optimizer uses the value you enter depends on the distribution model selected.

    Execute Each SQL Statement by

    Select one of the following:

    • Duration (seconds)—Enter the duration of the scalability test in seconds.

    • Number of times—Enter the number of times to execute the SQL statement.

      Range: 1 to 100

  

Related Topics  

About SQL Optimization

Optimize SQL Statements

相关文档

The document was helpful.

选择评级

I easily found the information I needed.

选择评级