An important skill in database performance tuning is capturing, reading, and understanding query execution plans. The query execution plan is a roadmap of the resources allocated and operations performed when the database engine gathers the results of your query.
Beyond just the operations, which are the logical steps performed when executing a query, the query execution plan contains metadata about the statistics the database engine used, the memory allocated to the query, and any trace flags or options set. Understanding query plans and how they get generated is key to understanding database performance.
How Do Query Plans Get Generated?
When you issue a query against SQL Server, a number of operations take place before your query gets executed. The first step is parsing the query, during which SQL Server confirms the syntax is valid and the user has access to the objects referenced in the query.
Next, the query gets optimized. The optimization process, for a new query, evaluates the statistics associated with the columns and indexes in the query and attempts to generate several execution plans. The optimizer then chooses what it thinks is the best of those plans and uses it for the query. Generating a query plan is very expensive from a CPU perspective, so SQL Server caches execution plans for reuse in an area of memory called the execution plan cache.
How Do I Capture a Query Execution Plan?
While every query executed in SQL Server has an execution plan associated with it, you’ll need to take steps to gather the plan to analyze it. Depending on your goals, you can either capture an execution plan before or after the query is executed.
Query plans can be captured several ways. In your development process, you might use SQL Server Management Studio (SSMS) to capture your execution plan at runtime, or even before you execute the query. In a production environment, you can use an Extended Events session to capture execution plans, so you can review them over time.
If you’re using SQL Server 2016 or newer, you can enable the query store feature on your databases to capture the plans for you. If you’re using SSMS, you have the option of capturing either an actual or estimated query plan.
An estimated execution plan is what the query optimizer generates and uses to the execute the query. This plan includes the estimated row counts used to generate the execution plan. The actual execution plan includes the actual row counts the query retrieved and shows any warnings that occurred during the execution of the query.
While the actual execution plans have useful information for troubleshooting the underlying statistics of the columns or indexes, actual execution plans are very expensive to capture. This can impact your performance, so you should capture them only when troubleshooting specific performance issues that require the execution-time information.
Query execution plans are stored as XML in the database—they can be viewed graphically in SSMS, or you can use the free Plan Explorer tool from SolarWinds.
Starting in SQL Server 2019, you can capture the most recent actual execution plan from the dynamic management function sys.dm_exec_query_plan_stats.
Reading an Execution Plan
Once you’ve captured an execution plan, opened it in either Plan Explorer (see Figure 1) or SSMS (shown in Figure 2).
Figure 1: Plan Explorer
Figure 2: SQL Server Management Studio
Read an execution from right to left and from top to bottom. Each operator has a lot of data associated with it. View the information as follows:
- In SSMS, right-click on the properties of each operator to view that operator’s properties in the properties pane.
- In Plan Explorer, choose the Plan Tree tab, which consolidates most of the information into one screen.
Another clue to performance you can check in both tools is the thickness of the lines. They grow thicker as the amount of data flowing from operator to operator grows. Thus, a very thick line might indicate an opportunity to filter data more efficiently.
Finally, if you right-click on the left-most operator and select properties in SSMS, or hover over it in Plan Explorer (the SELECT in Figures 1 and 2), you can get important information about the query, including its memory grants and any parameters. They’re shown in Figure 3.
Figure 3: Properties in Plan Explorer
Learning how to read execution plans is definitely a skill (there are several books on the topic), but it’s critical to understanding how to improve the performance of queries and in turn your overall performance.
Getting Help With Execution Plans
It can be intimidating for a new DBA or developer to try to read plans in SQL Server Management Studio. SolarWinds Plan Explorer consolidates the important information in the plan in a couple of easy views and lets you learn faster. You can download Plan Explorer for free here.
