Identify problem areas in execution plans
In this tutorial, we will learn how to identify problem areas in execution plans.
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 number of rows, resource usage metrics and runtime warnings (if any).
However, Query performance troubleshooting requires significant expertise in understanding query processing and execution plans, in order to be able to actually find and fix root causes. And, SQL Server Management Studio includes functionality that implements some degree of automation in the task of actual execution plan analysis, especially for large and complex plans. Here, the goal is to make it easier to find scenarios of inaccurate Cardinality Estimation and get recommendations on which possible mitigations may be available.
To analyze an execution plan for a query
- Firstly, open a previously saved query execution plan file (.sqlplan) using the File menu and clicking on Open File. Or drag a plan file to Management Studio window. Alternatively, if you just executed a query and chose to display its execution plan. Then, move to the Execution Plan tab in the results pane.
- Secondly, right-click in a blank area of the execution plan and click Analyze Actual Execution Plan.
- Thirdly, the Showplan Analysis window opens on the bottom. The Multi Statement tab is useful when analyzing plans with multiple statements, by allowing the right statement analysis.
- Lastly, select the Scenarios tab to see details on the issues found for the actual execution plan. For each listed operator on the left pane, the right pane shows details about the scenario. This is in the Click here for more information about this scenario link. And there, the possible reasons to explain that scenario are listed.
Reference: Microsoft Documentation