Computed Columns and Cardinality Estimates

:thinking_face:

When most people think about computed columns, they don’t think about cardinality estimates.

Heck, I’m not sure most people think about cardinality estimates. At all. Ever.

One of the few people who has ever responded to my emails does, and I didn’t even have to threaten him.

Do you think about computed columns?

If you’re here, you might just be thinking about lunch, avoiding jail time, or how much you hate Access.

But seriously, they’re great for a lot of things. Just don’t put functions in them.

One thing they can help with, without you needing to persist or index them, is cardinality estimates.

Demo Block

If I run this query:

I get this plan:

Dead wrong

The first thing that jumps out to most people is the warning on the Hash Join — indeed, it’s grail overfloweth.

Choosing poorly

The root cause of this bad guess is from the Users table. Our WHERE clause is just awful. How on earth would SQL Server know how many Upvotes + Downvotes = 0? In fact, it’s guess is just plan goofy.

[Boos Internally]

The guess is off by about 470k rows. If your concern is with cardinality estimates, you may try the Advanced Dead End known as multi-column statistics.
But that doesn’t help!

You might even try Advanced-Advanced Dead End known as filtered multi-column statistics.

And that still won’t help.

Even if you RECOMPILE. Even if you free the proc cache. Even if you rebuild indexes. Even if you restart SQL.

Magically, I can add this computed column:

I’m not persisting it, and I’m not even indexing it. But my plan changes! Kind of.

Have you seen me?

The Hash Join is no longer spilling!

Before you go accusing me of taking advantage of Batch Mode Memory Grant Feedback — I’m not using any cOLU MNstor3 indexes in here. Everything is plain old row mode.

It’s just a result of the cardinality estimate improving.

Crazy Eddie

The much improved cardinality estimate leads the optimizer to ask for a larger memory grant.

Oh, you did that.

Rather than a small, crappy memory grant (for this query).

Don’t like you.

Is this as good as it gets?

Obviously not. We could make some other changes to improve things, but I think this is pretty cool.

We didn’t have to change our query, even, for the optimizer to make this adjustment.

If you’ve got this kind of stuff in your queries, a computed column might be a good way to improve performance.

Thanks for reading!

Previous Post
Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views
Next Post
Fifteen Things I Hate About ISNUMERIC

4 Comments. Leave new

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}