SQL Server friend and excellent webmaster Mr. Erik C. Darling showed me a query plan gone wrong:
48 billion rows for a single operator is certainly a large number for most workloads. I ended up completely missing the point and …
Category: Query Tuning
IS NULL vs IS NOT NULL
Drop The Verse
Why is IS NULL (not to be confused with ISNULL, the function) considered an equality predicate, and IS NOT NULL considered an inequality (or range) predicate?
It seems like they should be fairly equivalent, though opposite. One …
Should The Optimizer Use Lookups More?
Frangible Tuning
Right now, the optimizer’s costing algorithm’s cost lookups as being pretty expensive.
Why? Because it’s stuck in the 90s, and it thinks that random I/O means mechanical doo-dads hopping about on a spinning platter to fetch data.
And …
SQL Server 2019: When Batch Mode On Rowstore Isn’t Better
Um Hello
WHAT DO YOU MEAN YOU’RE NOT ON SQL SERVER 2019 YET.
Oh. Right.
That.
Regressed
Look, whenever you make changes to the optimizer, you’re gonna hit some regressions.
And it’s not just upgrading versions, either. You can have …
More CTE Myths: Persistent Expressions
I Got Five On It
I wish I had a dollar for every wrong thing I’ve heard about CTEs in my life. I’d buy a really nice cigar and light it with fire made by the gods themselves.
Or, you …
More Local Variable Nonsense: ORDER BY
Local Disasters
I see this kind of pattern a lot in paging queries where people are doing everything in their power to avoid writing dynamic SQL for some reason.
It’s almost as if an entire internet worth of SQL Server …
More Local Variable Nonsense: TOP
Number One
In case you missed it for some reason, check out this post of mine about local variables. Though it’s hard to imagine how you missed it, since it’s the single most important blog post ever written, even …
Lookups Are Just Joins With No Choices
Such Optimize
At this point in your life, you’ve probably seen, and perhaps even struggled with how to fix a key lookup that was causing you some grief.
This post isn’t going to go terribly deep into anything, but I …