SQL Optimizer analyzes the following in your original SQL statement and table references to generate index alternatives:
Once SQL Optimizer generates alternatives, you can test them to evaluate improvements in database performance.
To generate and benchmark an index alternative
Select the Optimize SQL tab in the main window.
Enter a SQL statement in the Alternative Details pane of the SQL Details tab.
Click . The Select Connection window displays.
Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Database and User | Description |
Database |
Click |
Set User |
Click |
Important: Since indexes are physically created on the database, this process may impact database performance and the performance of other SQL statements.
Select the index alternative to test.
Click . Execution statistics display in the Alternatives pane once the test is finished. Use the tabs available in the Execution Plan pane to view more information about the index alternative.
When your SQL statement uses a temp table, SQL Optimizer requires you to create the temporary table before optimizing the SQL statement or generating index alternatives. SQL Optimizer automatically drops all temp tables created when you close your session.
To create a temporary table
Select the Optimize SQL tab in the main window.
Click at the bottom of the Alternative Details pane. The Temp Table Manager window displays.
Enter a SQL statement to create a temporary table. Review the following for additional information:
Temp Table Manager | Description |
---|---|
SQL Script Editor |
Allows you to enter SQL statements to create temporary tables. Tip: Click |
SQL Script | Displays the SQL script for the temporary table you select. |
Temp Table List |
Displays a list of temporary tables for your current session. Tip: Click |
Note: If the selected SQL statement uses a variable, the Set Bind Variables window displays so you can define the variable. See Set Bind Variables for more information. |
Click . The Select Connection window displays. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Database and User | Description |
Database |
Click |
Set User |
Click |
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:
To create a new Plan Cache search session
Select Find SQL.
Note: If you are creating your first search session, see Find SQL - the Homepage.
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:
|
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. |
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:
|
Status |
Displays status of search:
|
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. |
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.
Find SQL from SQL Trace allows you to extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profiler. You can identify the most resource-intensive SQL statements in your traces.
Additionally, you can:
To create a new SQL Trace search session
Select Find SQL.
Note: If you are creating your first search session, see Find SQL - the Homepage.
The Add SQL Trace Search dialog opens. Select a trace file or table. Review the following for more information:
Notes:
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.
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. |
Show Collect Criteria |
Click to filter the SQL to collect. You can filter by:
|
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 pane.
Start a new search |
Select to begin a new search and to display the Add SQL Trace 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:
|
Status |
Displays status of search:
|
Show all SQL Trace 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. |
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.
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.
|
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Nutzungsbedingungen Datenschutz Cookie Preference Center