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 Summary Chart view displays a summary of resources consumed by the top n SQL. This page also includes statistics for each SQL statement in the group.
Resource Consumption of SQL
You can use the editable options on this page to change the number of SQL selected and the cost type to measure when selecting top-consuming SQL.
To modify editable options
-
Click each editable option to modify your selection criteria for Top SQL. Review the following for additional information:
Editable Option Description Top n SQL with the highest resource type
Click the editable number to choose a method of selecting top-consuming SQL. A drop-down pane displays.
To select numeral or percentage method
- Select Top n SQL — specify the number of top-consuming SQL to select.
- Select Top n% SQL — specify the percentage of top-consuming SQL to select.
- Select enough SQL that consumes n% — specify a consumption percentage. SQL Optimizer selects the top-consuming SQL whose total consumption equals this value.
To modify n or n%, do one of the following
- In the drop-down pane, click the n or n% link and enter a number.
- In the graph, move the green vertical line left or right. The n or n% number will adjust accordingly.
Top n SQL with the highest resource type
Click the editable resource type to choose another resource. SQL Optimizer selects the top-consuming SQL based on this resource.
You can select to view total or average statistics in the graph.
To Compare Statistics
- Click Compare Statistics to display a comparison of two or more resources for the Top n SQL.
- Selected Statistics—Click the link to select which resources to compare. You always compare against the resource selected in "Top n SQL with the highest resource type."
To View SQL details
- Click View Details to open the Details page where you can view SQL text and execution plans of the SQL statements in the list. You can also access the batch or database object details for each SQL statement. See Top SQL - Details View for more information.
Graph
The graph in the middle of the upper pane displays information based on the criteria you selected above. Hover over points on the graph to view detailed information.
Colored bars correspond to bars in the Top SQL grid.
|
Green vertical line |
All SQL to the left of the green line are included in the Top n SQL group (the SQL on the line is included). Move this line left or right in the graph to decrease or increase this number. |
| Gray vertical lines | The gray lines represent other Top n SQL breakpoints suggested by SQL Optimizer. Depending on the number of SQL collected and the statistics of resources consumed, SQL Optimizer provides one or more suggested Top n SQL breakpoints. |
Tip: Click the arrow beside the session name (breadcrumb) to change your view to Top Batches or Top Database Objects.
Summary
The highlighted box in the lower part of the upper pane displays a summary of the results for this view. The information also 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 all resources consumed for this particular Top n SQL group.
Top SQL List
Statistics for the top-consuming SQL display in the Top SQL list/grid. Use this grid to compare two types of resource consumption statistics: totals and averages. Review the following for additional information:
|
View Statistics |
Click the link to change the view of statistics. Select one of the following:
|
|
SQL |
Right click a SQL to:
|
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 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 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 resource 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 server.
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 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 server.
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 |
|
Name |
Right click an object to:
|