Редактиране

Споделяне чрез


Query Plan Viewer in Azure Data Studio

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Azure Data Studio supports capturing both estimated and actual query plans. Query plans provide a graphical display of the data retrieval (or modification) method chosen by the SQL Server Query Optimizer. This visual approach helps understand the performance characteristics of a query.

Display - estimated plan

To view the estimated plan for a query, highlight the specific query and select the Estimated Plan button.

Note

If you do not select a query and select the Estimated Plan button, it will display the estimated plans for all queries in the query window.

Display - actual plan

To view the Actual Plan for any query in the editor, select the Enable Actual Plan button or CTRL + M. With this option enabled, executing any query (via the Run button or F5) will result in the actual plan displayed in the Query Plan tab.

Note

If you do not select a query and select Run or F5, it will display the actual plans for all queries in the query window.

Output

When viewing an estimated or actual plan, three tabs are available in addition to the Results and Messages tab. These tabs are Query Plan, Plan Tree, and Top Operations. The Query Plan tab displays the plan(s) and provides multiple menu options, available on the right side menu bar and via the right-click menu. The Plan Tree displays the plan in text format, including sortable columns. The Top Operations pane lists the operators in the plan, and sorting is available to identify the most expensive operators based on different attributes.

Query plan menu options

From the context of a query plan there are multiple options available for further analysis of a plan. These options are accessed by right-clicking anywhere in the plan, or from the right menu bar.

Icon Option Description
Screenshot of a Screenshot of a Save plan icon. Save Plan File Save the query plan as a .sqlplan file
Screenshot of a Show query plan XML icon. Show Query Plan XML View the query plan in XML format in a new window
Screenshot of the Open query icon. Open Query Open the plan's query in a new window
Screenshot of a Zoom in icon. Zoom In Zoom in within plan
Screenshot of a Zoom out icon. Zoom Out Zoom out within the plan
Screenshot of a Zoom to fit icon. Zoom to fit Size the plan to that of the window
Screenshot of a Zoom to custom size icon. Custom Zoom Specify a custom zoom level for the plan
Screenshot of a Find node icon. Find Node Search for nodes in a plan based on specific attribute values
Screenshot of a Properties pane icon. Properties Display the properties pane
Screenshot of a Compare plan icon. Compare execution plan Compare the current execution plan to another plan in a new window
Screenshot of a Highlight expensive operator icon. Highlight expensive operators Identify the most expensive operator in the plan based on the specified attribute
Screenshot of a Tooltips icon. Tooltips Toggle to enable or disable tooltips when mousing over operators in the plan
Screenshot of a Top operations icon. Top Operations Switch to the Top Operations pane

Top operations

Within the Top Operations tab, in addition to sorting columns, the Filter window on the right can be used to find operators with a common field value. For example, it may be helpful to find all Nested Loop operators in a plan that access a specific database object, or filter on specific values for a given attribute.

Screenshot of a Top Operations tab after filtering for clustered.

For any operator in the list, select the operator name to jump to that operator's location in the query plan (a green box will surround the operator).

Plan comparison

Comparing query plans can be useful when troubleshooting differences in plan performance, and this can be accomplished in Azure Data Studio by right-clicking on the plan and selecting Compare execution plan. The current plan will open in the top half of a new window, with the Add execution plan button in the bottom half. Select the Add execution plan button and browse out to the saved plan to which you want to compare.

Screenshot of Two plans compared.

The compare plans menu offers several of the same icons as in the query plan menu, but it also provides the ability to toggle the orientation from a horizontal display to a vertical display, using the Toggle Orientation button. The Properties pane in the plan comparison window provides the ability to review attribute values for an operator from each plan side-by-side. Select the Properties button, then select the operator in each plan to compare. While any operators can be selected, selecting the same operators in similar locations within the plan is typically helpful. The Select operator is one example, but the Index Seek operator is selected in the screenshot below.

Screenshot of Two plans compared with the properties window displayed.

The default view of the Properties plan for plan comparison lists attributes with different values at the top, and attributes with the same values are collapsed under the Equivalent Properties heading. This view can be changed using the sort icons, and the attribute lists can be expanded or collapsed to aid in displaying and finding information. The Properties pane also has a filter menu, providing the ability to quickly navigate to the attribute of interest.

Next steps