I was recently invited to participate in big discussion on one of the online forum, the topic was Actual Execution Plan vs. Estimated Execution Plan. I refused to participate in that particular discussion as I have very simple but strong opinion about this topic. I always use Actual Execution Plan as it is accurate.
Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running query. I just run query and have correct and accurate Execution Plan.
Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M
You can use SSMS to include the execution plan as well.
What the Actual Execution Plan Shows That the Estimate Cannot
The actual plan adds numbers that only exist after the query runs. The most useful ones are the actual number of rows and the number of executions for each operator. Put them next to the estimated rows. When the two are far apart, SQL Server had wrong information when it built the plan, often because of stale statistics or a filter that is hard to estimate.
There are times when the estimated plan is the right tool, though:
- A query that runs for hours. You can see its plan in seconds without waiting.
- An
UPDATEorDELETEon real data. Getting the actual plan means the change really happens. - A quick look to see whether an index will be used at all.
If you must capture the actual plan of a data change, run it inside a transaction on a test copy and roll it back afterward. Also keep in mind that capturing the actual plan adds a little overhead, so turn it off when you are timing a query.
From T-SQL you can get the same plans without the toolbar. SET SHOWPLAN_XML ON returns the estimated plan without running the query, and SET STATISTICS XML ON runs the query and returns the actual plan with it. Both work in SQL Server 2005 and later. You can save any plan as a .sqlplan file and open it later, which is handy when you want a second opinion.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
30 Comments. Leave new
Can someone please tell me if I want to figure out difference the costs of actual and estimate executions around how much data records do I need to have? I have about 30 records but it does not show any difference?
hi dave,
thanks alot for sharing a lot of information.
after the query is executed . it will show that 1 row(s) is effected (this is after the actual number of rows effected).
what for this is coming?
what is happening there?
can u please explain this?
this is when i am using actual execution plan in ssms.
Thanks in advance.
Hi Dave,
I like your forum and I have been referring it for the past 2 years.
My question to you is..
How can we find in an execution plan if a where clause is non-sargable(functions on columns) and it is not making use of the indexes.
Thanks in advance.
It will show you Index Scan instead of Index seek
How can use “display estimated query plan” and “Include actual execution plan” in MySQL?
Could anyone guide me how to achieve these two in MySQL?
Great, short & compact information. Thank man!
estimated EP and actual EP is same.
actual EP is just added i/o cnt.
plz show me different case…
Hi Pinal sir, I want to fire a small query frequently, that returns 1-2 rows only, individually not using any Execution plan.
How can I prevent a query from re-using existing ExecutionPlan or recompiling ExecutionPlan or creating new ExecutionPlan during it’s excution.
Thanks,
Shovan Mukherjee
dear sir
but Query Store uses only Estimated Execution plan , who then you will now clarify your statement in 2020