There are multiple types of query plans you may hear about when it comes to SQL Server. Today, I want to go over the two that I would argue are most important when query tuning: estimated execution plans and actual execution plans.
Estimated Execution Plan
An estimated execution plan can be obtained without actually executing a query to get results. In SSMS, there is a button that you can click to display the estimated execution plan. See the yellow highlighted icon below:
Once clicked, SSMS will display an outline of how results will be gathered when the query in your query window is executed based on the query, schema, and database statistics.
We can hover over the icons for more details. If we hover over the Clustered Index Scan for this plan we will see various estimates:
For the most part, this will always show the same steps that you would see in the actual execution plan but it’s possible to get some variation if the schema changes or statistics have updated in the time between viewing the plans.
Even with estimated execution plans, you’ll see missing index recommendations for you to consider. To take a look at these recommendations you may want to view the XML format of the execution plan.
There are a few options if you want to see the plan in XML format instead of icons in the screenshot above. One option is to right click on the execution plan you are viewing and click Show Execution Plan XML.
Another option is to run:
SET SHOWPLAN_XML ON;
Once SHOWPLAN_XML is turned ON, you can then highlight your scripts, execute them, and the result will be an XML file that you can open to view the plan.
Actual Execution Plan
The actual execution plan also contains the steps of execution but will also include better details of how the results were gathered. In addition to the “estimated” details, we can now see “actual” details to compare against.
We can tell SSMS to include the actual execution plan by selecting this button:
We can then run our query, select the Execution plan tab, and see the actual execution plan:
Hovering over the Clustered Index Scan again, we’ll see much more information compared to the estimated execution plan:
Plan Accordingly
If you’re running an UPDATE or a DELETE statement in a production environment and want to see plan steps without executing changes then the estimated plan can be helpful. On the other hand, if you’re doing that level of testing then you’re likely going to be much better off testing in a non-prod environment first.
The estimated execution plan is a good starting point when you are working at a high level with a troublesome query that needs improvement because you can quickly get information without waiting on the query to complete. Yes, you’re going to eventually want to view the actual execution plan to get more detailed information but I think it’s more efficient to view the estimated plan first to get an idea of what needs attention. If you’re testing with a query that’s taking a long time to run, it’s going to take even longer to run when you’re getting the actual execution plan.
Thanks for reading!
Chad Callihan 