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
- Select the Optimize SQL tab in the main window.
-
Enter a SQL statement in the Alternative Details pane of the SQL Details tab.
-
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.
-
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 Set User
Click -
Click
.
Tip: Click
to copy the SQL statement to execute in another Toad product.
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
-
Select the Virtual Indexes tab in the Alternative Details pane.
Note: The Virtual Indexes tab displays after you create a user defined alternative.
-
Click
to create and edit virtual indexes for the user defined alternative.
-
Click
if you want to configure advanced options. Click the following to display the options available for each version of SQL Server.
Option Description FILLFACTOR (%)
Enter a percentage for the fill factor to use for virtual indexes. PAD_INDEX
Select this checkbox to use the PAD_INDEX option when creating virtual indexes.
Note: This option is only available when you enter a value for the fill factor.
IGNORE_DUP_KEY
Select this checkbox to ignore the insert of duplicate keys. ALLOW_PAGE_LOCKS
Select this checkbox to allow page locks. ONLINE
Select this checkbox to allow access to other tables and indexes during index creation. Filter Conditions
Select this checkbox and enter the filter conditions you want to apply. MAXDOP
Enter a value for the maximum degree of parallelism. STATISTICS_NORECOMPUTE
Select this checkbox if you do not want to automatically re-compute index statistics. ALLOW_ROW_LOCKS
Select this checkbox to allow row locks. Option Description FILLFACTOR (%)
Enter a percentage for the fill factor of the virtual index. PAD_INDEX
Select this checkbox to use the PAD_INDEX option when creating virtual indexes.
Note: This option is only available when you enter a value for the fill factor.
IGNORE_DUP_KEY
Select this checkbox to ignore the insert of duplicate keys. ALLOW_PAGE_LOCKS
Select this checkbox to allow page locks. ONLINE
Select this checkbox to allow access to other tables and indexes during index creation. MAXDOP
Enter a value for the maximum degree of parallelism. STATISTICS_NORECOMPUTE
Select this checkbox if you do not want to automatically re-compute index statistics. ALLOW_ROW_LOCKS
Select this checkbox to allow row locks. Option Description FILLFACTOR (%)
Enter a percentage for the fill factor of the virtual index. PAD_INDEX
Select this checkbox to use the PAD_INDEX option when creating virtual indexes.
Note: This option is only available when you enter a value for the fill factor.
IGNORE_DUP_KEY
Select this checkbox to ignore the insert of duplicate keys. STATISTICS_NORECOMPUTE
Select this checkbox if you do not want to automatically re-compute index statistics. Option Description IGNORE_DUP_KEY
Select this checkbox to ignore the insert of duplicate keys. ONLINE
Select this checkbox to allow access to other tables and indexes during index creation. Filter Conditions
Select this checkbox and enter the filter conditions you want to apply. MAXDOP
Enter a value for the maximum degree of parallelism. STATISTICS_NORECOMPUTE
Select this checkbox if you do not want to automatically re-compute index statistics. -
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.
SQL Server 2008, 2008 R2 and 2012
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
- Select the Optimize SQL tab in the main window.
-
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
-
-
Click
.
-
Review the following for additional information:
Bind Variables List Description Datatype
Click
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
Note: SQL Optimizer selects the All Columns checkbox by default.
FROM
Click
Tip: Click
WHERE
Enter a WHERE clause or click ORDER BY
Enter a ORDER BY clause or click
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
- Select the Optimize SQL tab in the main window.
-
Enter a SQL statement in the Alternative Details pane.
-
Click
. The Select Connection window displays.
-
Select a connection to use. Review the following for additional information:
Select Connection Description Connection
Click
Tip: Click
Select Database and User Description Database
Click Set User
Click -
Click
to optimize the SQL statement or click
to optimize the SQL statement and generate index alternatives in one step.
-
Click
.
-
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
About SQL Optimization