Bad Guesses and Bad Choices: Does 2019 Batch Mode Help?

Bad Fit

In the last post, I tried to play some tricks on the optimizer to have Batch Mode make a query faster.

It didn’t quite work out the way I wanted.

That isn’t to knock Batch Mode. It’s not supposed to fix everything, and I played a silly trick to get some operators to use it.

But it did make me curious if 2019’s Batch Mode for Row Store would improve anything.

After all, no dirty tricks are involved, which means more operators can potentially use Batch Mode, and maybe we’ll avoid that whole skewed parallelism problem.

I mean, what are the odds that I’ll get the exact same plans and everything?

Spolier

50%

The Merge Join plan is still wack as hell.

The forced hash join plan got better, though.

Still Skewy?

The skewed parallelism isn’t as contagious going across without the separate Filter/Bitmap operator.

Uggo

With both the Hash Join and the Scan of Votes occurring in Batch Mode, the Bitmap is able to be pushed to the storage engine.

Note that there’s no explicit Bitmap filter anywhere in the plan, but one still shows up in the predicate of the Scan of the Votes table.

Something new

With 3 out of 4 threads doing… vaguely even work, we end up in a better place, especially compared to only one thread doing any work.

At least now I can show you the Batch Mode Sort lie about being parallel.

Crud

Three threads with rows at the Hash Join go into the Sort on a single thread.

You Know…

As much as I love these new bells and whistles, I’m gonna go back to good ol’ fashion query tuning next.

The problem here is still that a bad estimate causes a Merge Join to be chosen in the first place.

When we force the Hash Join, query runtime is reduced at the expense of a quite large memory grant.

This is likely preferable for most people (especially with Batch Mode Memory Grant Feedback), but it doesn’t address the root cause.

Thanks for reading!

1 thought on “Bad Guesses and Bad Choices: Does 2019 Batch Mode Help?”

Leave a Reply

Your email address will not be published. Required fields are marked *