patternsqlMinor
Differences between estimated and actual execution plans
Viewed 0 times
actualdifferencesbetweenplansestimatedandexecution
Problem
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" ?
In SQL Server 20xx, what is the difference between "display estimated execution plan" and "include actual execution plan" ?
Solution
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.
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.
Context
StackExchange Database Administrators Q#21362, answer score: 7
Revisions (0)
No revisions yet.