SQL Optimizer classifies the SQL statements extracted in Scan SQL and in Find SQL, as well as the SQL you enter in Optimize SQL. The SQL are classified into one of the following categories: Simple, Complex, Problematic, or Invalid.
For a better understanding of each of these categories, review the following category descriptions.
Classification | Description |
---|---|
Simple | SQL statements are classified as simple when the number of tables referenced in the execution plan is less than the lower limit of the complex table scan operations range. |
Complex | SQL statements are classified as complex when the number of tables referenced in the execution plan exceeds the lower limit of the complex table scan operations range. |
Problematic |
SQL statements are classified as problematic when SQL Optimizer determines they can be optimized. Problematic SQL statements satisfy one or more of the following criteria:
|
Invalid |
SQL statements are classified as invalid for one of the following reasons:
|
Note: You can specify SQL classification rules in the Options dialog. See SQL Classification Options for more information. |
Find SQL helps you locate the most resource-intensive SQL in your server. You can use Find SQL to inspect SQL captured by Plan Cache and SQL Profiler.
Find SQL from Plan Cache evaluates existing SQL performance to find the most resource-intensive SQL statements. Resource usage such as CPU Time and Elapsed Time are evaluated. You can view the top consuming SQL statements from different areas, such as batches or database objects.
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:
You can extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profile to identify the most resource-intensive SQL statements in your traces.
For more information about trace templates, see SQL Trace Templates.
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.
|
Note: This topic may not include a description for every field in the screen or dialog.
To select a connection
Select Click here to start Find SQL.
Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Password |
Password |
To select a connection when using a trace table
A separate connection window displays when you add trace tables.
Review the following for additional information:
Add Trace Tables | Description |
Connection |
Specify the connection where the trace table is located. |
Database |
Specify the database where the trace table is located. |
Schema |
Specifies the owner of the trace table. |
© ALL RIGHTS RESERVED. Feedback Nutzungsbedingungen Datenschutz Cookie Preference Center