지금 지원 담당자와 채팅
지원 담당자와 채팅

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

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

  1. 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:

  • Value and Percentage in Server
  • Value
  • Percentage in Server

SQL

Right click a SQL to:

  • Send to Optimize Indexes
  • Save As
  • Print

 

  

Related Topics

Top SQL - Details 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 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:

  • Select Top n Batches — specify the top number of batches with the highest consumption according to the performance method selected.
  • Select Top n% Batches — specify the percentage of batches with the highest consumption according to the performance method selected.
  • Select enough Batches that consumes n% — specify batches that consume the top percentage of the resource selected .

Resource

Click and to specify resource.

Compare Statistics

Click to display a comparison of selected statistics.

  • Selected Statistics — Click the link to select which resources to compare. You always compare against the resource selected in "Top n Batches with the highest resource."

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:

  • Value and Percentage of Server
  • Value
  • Percentage of Server
Columns Description

Batch

Right click a batch to:

  • Save As
  • Print

  

Related Topics

Top Batches - Details View

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.

  • Select Top n objects — specify the top number of objects with the highest consumption according to the performance method selected.
  • Select Top n% objects — specify the percentage of objects .with the highest consumption according to the performance method selected.
  • Select enough objects that consumes n% — specify objects that consume the top percentage of the performance method selected .

Resource

Click and to specify resource.

Compare Statistics

Click to display a comparison of selected statistics.

  • Selected Statistics — Click the link to select which resources to compare. You always compare against the resource selected in "Top n Objects with the highest resource."

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:

  • Value and Percentage in Server
  • Value
  • Percentage in Server
Columns Description

Name

Right click an object to:

  • Save As
  • Print

  

Related Topics

Top Database Objects - Details View

관련 문서

The document was helpful.

평가 결과 선택

I easily found the information I needed.

평가 결과 선택