I'm new to SQL Server and I want to tune my first query.
In SQL Server 20xx, what is the difference between "display estimated execution plan" and "include actual execution plan" ?
2 Answers
The estimated execution plan is generated based solely on the statistics that SQL Server has - without actually executing the query. The query optimizer is just asked what it would most likely do with this query, based on all the information it has on the query and the data distributions etc.
This works OK, the query doesn't need to run (which could take a long time), but if the statistics are out of date, the plan might be severely skewed.
The actual execution plan is just that - the actual execution plan that was used when actually running the query. This will show you things that might hint at "out-of-date" statistics etc. But to get this, you must run the query - which can take a long time.
-
1I would have thought that the only real question here is in which cases the actual plan isn't the estimated one. The rest is obvious, isn't it?– JohnJul 7, 2017 at 7:42
-
one quick question after passing thru query optimizer and on query execution, if the cached plan is outdated will the query optimization step re-execute updating stats? or is there another way to generate execution plan? Jan 11, 2019 at 11:18
Estimated plan will provide you how your query will execute on the server without actually running it. This will gives you idea whether you need to tune to create a better plan or the estimated plan looks ok.
Actual plan shows what actually happened when the server executed the query. It includes details like actual number of rows from a table,how much memory it used if there was sort.What degree of parallelism it has used during execution.
But best thing would be to look at the diff between "estimated number of rows" and "actual number of rows" which you can see in the actual exec plan. Closer these two values are means the stats are up to date and for given stats this is the best plan.But if these values have a lot of diff then it could be possible that stats are not up to date and thus this might not be the best plan.In this case, updating stats will help in generating better estimated plan and thus actual plan.
