Most of the time, if you get the estimated plan for a query, the actual execution plan will look about the same.
You’ll get the additional information of course, but the shape and most of the other attributes will match up.
Other times, things will change between the two.
Here’s a silly example I came across recently while working on demos for some other stuff.
SELECT TOP (100) p.Id, vs.UpVotes, vs.DownVotes FROM dbo.VoteStats() AS vs JOIN dbo.Posts AS p ON vs.PostId = p.Id WHERE vs.DownVotes > vs.UpMultipier AND p.CommunityOwnedDate IS NULL AND p.ClosedDate IS NULL ORDER BY vs.UpVotes DESC;
Estimated Execution Plan
Here’s the estimated plan I get for this query:
SQL Server estimates that a serial execution plan will work best, here.
Actual Execution Plan
Here’s the actual execution plan for the same query:
At runtime, SQL Server decides that a parallel plan is the better solution.
SQL Server can totally estimate parallel plans. I’ve seen it a million times.
In this case, though, it doesn’t do that. If you get the estimated plan after executing the query, it’ll come back with the parallel version.
Why does this happen? Well, as SQL Server adds features to the Intelligent Query Processing, more and more decisions are made when a query executes.
Stuff like this can make estimated and actual plans look much different from each other:
- Adaptive joins
- Interleaved execution
- Table variable deferred compilation
- Batch mode on row store
Wonders of modern query optimizers, eh?
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.