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.

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.

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)