I’ve noticed that when I run a query with the Display Estimated Execution Plan option enabled instead of the Include Actual Execution Plan option enabled, the plans look the same. I don’t fully understand the difference between these two plans and when I should use one over the other. In addition, when I compare the two plans, I see that their relative cost in the batch is 50 percent (each), but one query takes significantly longer to run. Why is that?
These are excellent questions because both issues create a lot of confusion. Although the estimated plan and the actual plan will have the same general plan details (e.g., index usage, join types), SQL Server doesn’t have to execute the query to generate the estimated plan. The estimated plan lets you see the plan that would have been chosen without having to wait the time it takes for the query to execute. Because the two plans will look the same, you might think it’s better to always use the estimated plan. Although it’s helpful to see the plan that SQL Server would have chosen, the estimated plan doesn’t tell you the effects (or efficiency) of this plan during runtime.
When you hover over something (e.g., a table) within the showplan output, you’ll get more information about the operation via the tooltip box. The actual execution plan lets you see all of the runtime characteristics. Figure 1 shows the estimated plan, and Figure 2 shows the actual plan used during execution.
Notice that the query plan (Index Seek to a Key Lookup in a clustered index) is the same in both the estimated plan and the actual plan. However, the tooltip box in the actual plan has a few more rows, which contain the actual runtime values. Although gathering the runtime values takes more time and will make your queries slower (they’re executed with statistics being gathered during the execution, which does impact query performance), it’s the most effective way to troubleshoot your query if you experience poor performance.
The most important of the runtime characteristics are the statistics on which the plan was based. You don’t directly see the statistics; however, you can see the factors that led up to the plan choice and begin to infer whether a plan was optimal, and why.
In the actual execution plan, the most important information is in the tooltip box. When query plans are generated, there are multiple phases that SQL Server goes through. During the optimization phase, SQL Server estimates the number of rows that will be processed by each operation. This estimate helps you determine the costs of various execution plans and ultimately the choice of execution plan. If the estimate is incorrect and off from the actual, especially by a large amount (e.g., the actual is more than two or three times the estimate), that implies SQL Server didn’t have an accurate picture of how much data was going to be affected.
When that’s the case, two things are problematic. First, the plan might not have been ideal and you might have suffered poor performance as a result. Second, the showplan results for percentages within the statement’s showplan, as well as between the batches (if multiple statements are executed), aren’t likely to be correct, and, in fact, they might be grossly inaccurate. This can be especially true with stored procedures—and answers the second part of your question. If two statements (or procedures) are executed and they have the same plan, then they’ll each have a cost of 50 percent of the overall plan cost. However, if one query or procedure takes more time than the other, then double-checking its runtime characteristics might provide the answer. For example, in Figure 3, you can see that the same SELECT statement was executed twice. The result shows 50/50 as the costs for each execution. And although the plan is the same (and therefore the costs would be equal), the second plan took more time. With a closer look, you can see that the second execution’s tooltip value for Actual Number of Rows is 4,999, whereas the estimate was only 1, which was shown in Figure 2. This is the cause of poor performance and the inaccuracy in the costs shown by the showplan.
I like the speed with which I can see and review an estimated plan via the Display Estimated Execution Plan option in SQL Server Management Studio (SSMS). However, I prefer the actual showplan displayed via the Include Actual Execution Plan option when I need more detail or when troubleshooting performance problems. Also, I always double-check the runtime characteristics to make sure they’re relatively accurate during analysis and before I can trust a showplan (and its batch results). If they aren’t accurate, then I know that I can’t trust the comparisons between the batches.
