Interview Question of the Week #051 – Actual Execution Plan vs. Estimated Execution Plan

In one of the recent interview, I met quite a few interesting candidates. It is common for employees ask many questions during an interview, but there are times when candidates also ask questions back to the interviewee. I was asked following question recently about execution plan.

Question: Do you use Actual Execution plan or Estimated Execution plan?

Answer: I always use the Actual Execution Plan as it is relatively accurate.

Solarwinds

Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running the query. I just run a query and have correct and accurate plan.

Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M

You can use SSMS as well.

Interview Question of the Week #051 - Actual Execution Plan vs. Estimated Execution Plan ExecutionPlan

Here is another article which compares various different execution plans. In this article I discuss about how this feature is improved in SQL Server 2016. New tools bring new capabilities and sometimes these are ingrained deep into the product that we can easily miss them out. And SQL Server 2016 is no different. After installing the CTP, I was pleasantly surprised to see this addition to help our DBA’s who are doing performance tuning and query tuning activities. In SQL Server 2016 you can see that an interesting addition. The “Compare Showplan” needs to be selected. Now, select the plan that was generated from your test environment. This will make bring both the execution plans on a side-by-side view as shown below. Look at the Purple color on the nodes.

Let me know what you think about this blog post.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
Interview Question of the Week #050 – Query to Retrieve Second Highest Salary of Employee
Next Post
Interview Question of the Week #052 – Print String in Reverse Order

Related Posts

3 Comments. Leave new

  • You are not explaining the difference between estimated and actual (besides the obvious) and why they exists.

    Reply
  • The estimates execution plan is useful for the verity of purpose, such as viewing the query plan for a long running query without waiting for it to complete, viewing the query plan for the Insert, Update , delete without altering any state of database , exploring the effect of various optimization hints on a query plan without running the quesr..

    Reply
  • Actual execution plan does NOT always display full scenario. Suppose that you have IF clauses in your script. Actual plan shows only the IF that was realized. However, estimated plan would display all cases.

    Reply

Leave a Reply

Menu