Why You Can’t Always Rely On Estimated Query Plans In SQL Server

Simple Enough

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.

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

Estimated Execution Plan

Here’s the estimated plan I get for this query:

SQL Server Query Plan

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:

SQL Server Query Plan

At runtime, SQL Server decides that a parallel plan is the better solution.

Wild Nights

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!

Going Further

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.

2 thoughts on “Why You Can’t Always Rely On Estimated Query Plans In SQL Server

  1. Hi Erik,

    Thanks for the post. It really helps. I have a question on this, What would be the plan shape stored in query store for the above example ? Serial or Parallel ?

Comments are closed.