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
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 Tip: Click |
Select Database and User | Description |
Database |
Click |
Set User |
Click |
Click .
Tip: Click to copy the SQL statement to execute in another Toad product.
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.
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
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 |
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
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:
|
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:
|
About SQL Optimization
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Nutzungsbedingungen Datenschutz Cookie Preference Center