Take the 2-minute tour ×

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" ?

share|improve this question

migrated from stackoverflow.com Jul 24 '12 at 2:46

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

