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.
Reference : Pinal Dave (https://blog.sqlauthority.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