Search Results
In Find SQL, the Search Results page is a dashboard-style summary of search results from either Plan Cache or SQL Trace.
This page allows you to see at-a-glance the top-consuming SQL for each cost (resource) type. The top row shows the total top-consuming SQL for each cost type. The next two rows show top-consuming SQL batches and database objects for each cost type.
Click a pie chart to go directly to the list of top-consuming SQL for that cost type (for either total SQL, most-expensive batches, or most-expensive database objects).
The dashboard-style Search Results page allows you to:
- See a summary of all results on one page.
- See a side-by-side comparison of resource utilization for the most resource-intensive SQL.
- Easily compare other resources consumed by the collected SQL.
- Easily compare resource consumption at SQL Batch and Database Object levels.
- Go directly to a results page already filtered by resource consumed or by SQL location (click the pie chart of interest).
Resources Consumed
Use the Resources Consumed pane to find which SQL statements consume the most resources according to different cost types.
How Pie Charts Are Calculated
The dashboard displays a summary of results based on statistic totals (not averages).
The number of SQL used in each graph is the Top n SQL suggested by SQL Optimizer.
- Click a pie chart to open the Summary Chart page where you can adjust this number (Top n SQL) to fine-tune your results view.
Using the Dashboard
To use the Resources Consumed dashboard
- Click a pie chart to view the top-consuming SQL statements for that cost/resource type and SQL location.
- Most-Expensive SQL Statements: Top-consuming SQL, for each cost type, out of the total SQL.
- Most-Expensive Batches: Top-consuming batches for each cost type.
- Most-Expensive Database Objects: Top-consuming database objects for each cost type.
- After viewing the top-consuming SQL on a Summary Chart page, return to the dashboard by clicking
, or you can click the Search session name in the breadcrumb.
- Return to the dashboard to easily navigate to another Summary Chart of top-consuming SQL.
-
The Search Results page remembers your last view and provides a link for easy return. One custom view link is provided for each row (statements, batches, or database objects).
Note: Each new view you select overwrites the last view link.
SQL Classification
Use the SQL Classification panel to view the collected SQL statements grouped by SQL classification (problematic, complex, simple, or invalid). Click a section of the pie chart to view the list of SQL in that classification category.
To use the SQL Classification panel
- The SQL by Classification pie chart helps you visualize the percentage of SQL statements in each classification category.
- The legend reports the number of SQL statements in each category.
-
Click a color-coded category in the SQL classification pie chart or the legend to view the list of SQL in that category. The SQL by Classification page opens. See SQL by Classification Page for more information.
Tip: For descriptions of the SQL classification categories, see SQL Classification Categories.
- After viewing a list of SQL by classification, return to the dashboard by clicking
-
The Search Results page remembers your last view and provides a link for easy return.
Note: Each new view you select overwrites the last view link.
Top SQL - Summary Chart
The upper grid of the Top SQL Statements page displays the resource consumption of SQL according to criteria you select. The lower grid displays statistics according to criteria you select.
Features of the upper grid
Select criteria from across the top of the upper grid to display the resource consumption of SQL. Review the following for additional information:
| Resource Consumption of SQL | Description |
|
Top n SQL |
Click and select from the following criteria to specify the number of SQL:
|
|
Performance method |
Click and select to specify performance method. |
|
Compare Statistics |
Click to display comparison of selected statistics. |
|
Selected Statistics |
Click and select statistics to which you want to compare with criteria previously selected. |
The chart in the middle of the upper grid displays information based on all the criteria you selected.
The lower part of the upper grid displays a complete summary of the consumption of SQL. based on the criteria you selected in Top n SQL and the performance method.The information includes the total number of SQL collected out of the number of SQL in the server. A graphic on the right side displays a visual representation of the total resource consumption.
Features of the lower grid
Statistics for your top SQL display in the lower grid. Review the following for additional information:
| Top SQL | Description |
|
View Statistics |
Select one of the following:
|
| Columns | Description |
|
SQL |
Right click a SQL to:
Select a SQL and click View Details in the upper-right of window to review SQL details. See Top SQL - Details View (SQL Trace). |
Note: Click the drop-down arrow beside the search name in the breadcrumb navigation to select another view.
Top Batches - Summary Chart
The Summary Chart view displays a summary of resources consumed by the Top Batches. This page also includes statistics for each batch in the group.
To view SQL statement details, click View Details. See Top Batches - Details View (SQL Trace) for more information.
Resource Consumption of Batches
Select criteria from across the top of the upper grid to display the resource consumption of batches. Review the following for additional information:
| Resource Consumption of Batches | Description |
|
Top n batches |
Click and select from the following criteria to specify the number of batches:
|
|
Resource |
Click and select to specify resource. |
|
Compare Statistics |
Click to display a comparison of selected statistics.
|
The chart in the middle of the upper grid displays information based on the criteria you selected above.
The lower part of the upper grid displays a complete summary of the consumption of batches based on the criteria you selected in Top n batches. The information includes the total number of batches collected out of the number of batches in the whole trace.
A graphic on the right side displays a visual representation of the total resource consumption.
Top Batches
Statistics for your Top Batches display in the lower grid. Review the following for additional information:
| Top Batches | Description |
|
View Statistics |
Select one of the following:
|
| Columns | Description |
|
Batch |
Right click a batch to:
|
Top Database Objects - Summary Chart
The Summary Chart view displays a summary of resources consumed by the Top Database Objects. This page also includes statistics for each SQL statement in the database object.
To view SQL statement details, click View Details. See Top Database Objects - Details View (SQL Trace) for more information.
Resource Consumption of Database Objects
Select criteria from across the top of the upper grid to display the resource consumption of database objects. Review the following for additional information:
| Resource Consumption of Database Objects | Description |
|
Top n objects |
Click and select criterion to specify the number of objects.
|
|
Resource |
Click and to specify resource. |
|
Compare Statistics |
Click to display a comparison of selected statistics.
|
The chart in the middle of the upper grid displays information based on all the criteria you selected.
The lower part of the upper grid displays a complete summary of the consumption of SQL based on the criteria you selected in Top n objects and the resource selected. The information includes the total number of objects collected out of the number of objects in the whole trace.
Top Database Objects
Statistics for your Top Database Objects display in the lower grid. Review the following for additional information:
| Top Database Objects | Description |
|
View Statistics |
Select one of the following:
|
| Columns | Description |
|
Object |
Right click an object to:
|