Determine the appropriate type of execution plan
In this we will learn to determine the appropriate type of execution plan.
Display an Actual Execution Plan
Actual execution plans are generated after the Transact-SQL queries or batches execute. Because of this, an actual execution plan contains runtime information, such as actual resource usage metrics and runtime warnings (if any). The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine used to execute the queries.
Further, to use this feature, users must have the appropriate permissions to execute the Transact-SQL queries for which a graphical execution plan is being generated. And they must be granted the SHOWPLAN permission for all databases referenced by the query.
To include an execution plan for a query during execution
- Firstly, on the SQL Server Management Studio toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query.
- Secondly, enter the query for which you would like to display the actual execution plan.
- Thirdly, on the Query menu, click Include Actual Execution Plan or click the Include Actual Execution Plan toolbar button.
- Then, execute the query by clicking the Execute toolbar button. The plan used by the query optimizer is displayed on the Execution Plan tab in the results pane.
- After that, pause the mouse over the logical and physical operators to view the description and properties of the operators in the displayed ToolTip. This include properties of the overall execution plan, by selecting the root node operator.
- Alternatively, you can view operator properties in the Properties window. If Properties is not visible, right-click an operator and click Properties. Select an operator to view its properties.
- Next, you can alter the display of the execution plan by right-clicking the execution plan and selecting Zoom In, Zoom Out, Custom Zoom, or Zoom to Fit. Here, Zoom In and Zoom Out allow you to zoom in or out on the execution plan, while Custom Zoom allows you to define your own zoom, such as zooming at 80 percent.
- Lastly, to navigate the display of the execution plan, use the vertical and horizontal scroll bars, or click and hold on any blank area of the execution plan, and drag your mouse. Alternatively, click and hold the plus (+) sign in the right lower corner of the execution plan window, to display a miniature map of the entire execution plan.
Display the Estimated Execution Plan
When estimated execution plans are generated, the Transact-SQL queries or batches do not execute. Because of this, an estimated execution plan does not contain any runtime information. Instead, the execution plan that is generated displays the query execution plan that SQL Server Database Engine would most probably use if the queries were actually executed. And also displays the estimated rows flowing through the several operators in the plan.
In order to use this feature, users must have the appropriate permissions to execute the Transact-SQL query. This is for generation of a graphical execution plan. And they must grant the SHOWPLAN permission for all databases referenced by the query.
To display the estimated execution plan for a query
- Firstly, on the toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query.
- Secondly, enter the query for which you would like to display the estimated execution plan.
- Thirdly, on the Query menu, click Display Estimated Execution Plan or click the Display Estimated Execution Plan toolbar button.
- After that, to view additional information, pause the mouse over the logical and physical operator icons and view the description and properties of the operator in the displayed ToolTip. Alternatively, you can view operator properties in the Properties window. However, iIf Properties is not visible, right-click an operator and click Properties. Select an operator to view its properties.
- Then, to alter the display of the execution plan, right-click the execution plan and select Zoom In, Zoom Out, Custom Zoom, or Zoom to Fit. Zoom In and Zoom Out allow you to magnify or reduce the execution plan by fixed amounts.
- Lastly, to navigate the display of the execution plan, use the vertical and horizontal scroll bars, or click and hold on any blank area of the execution plan, and drag your mouse. Alternatively, click and hold the plus (+) sign in the right lower corner of the execution plan window,. This is to display a miniature map of the entire execution plan.
Live Query Statistics
SQL Server Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another. Further, the live query plan displays the overall query progress and operator-level run-time execution statistics. This include the number of rows produced, elapsed time, operator progress. Because this data is available in real time without needing to wait for the query to complete. So, these execution statistics are extremely useful for debugging query performance issues.
To view live query statistics for one query
Firstly, to view the live query execution plan, on the tools menu click the Include Live Query Statistics icon.
Now, execute the query. The live query plan displays the overall query progress and the run-time execution statistics for the query plan operators. The query progress information and execution statistics are periodically updated while query execution is in progress. Further, use this information to understand the overall query execution process. Further, to debug long running queries, queries that run indefinitely, queries that cause tempdb overflow, and timeout issues.
Reference: Microsoft Documentation, Documentation 2, Documentation 3