awks ox
…
Streaming Week: Query Weirdness In SQL Server 2019
…
Category: Execution Plans
…
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 …
Why You Can’t Always Rely On Missing Index Requests
Greentexting
The problem with relying on any data point is that when it’s not there, it can look like there’s nothing to see.
Missing indexes requests are one of those data points. Even though there are many reasons why they …
Sneaky Ordering With Row Numbers
Puzzle Pieces
I was investigating a query slowdown recently, and came across something kind of odd with windowing functions and order by.
Let’s talk about these three queries:
SELECT u.DisplayName,
ROW_NUMBER() OVER (ORDER BY u.UpVotes) AS UpVotesWhatever, --UpVotes first
ROW_NUMBER() … 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 …