Speed Limit
When I’m blogging about performance tuning, most of it is from the perspective of Enterprise Edition. That’s where you need to be if you’re serious about getting SQL Server to go as fast as possible. Between the unrealistic …
Category: Batch Mode
Multiple Distinct Aggregates: Still Harmful (Without Batch Mode)
Growler
Well over 500 years ago, Paul White wrote an article about distinct aggregates. Considering how often I see it while working with clients, and that Microsoft created column store indexes and batch mode rather than allow for hash …
Database Context Is Everything
Full Frontal
…
Residual Predicates
We Will Talk About Things And Have Fun Now
USE StackOverflow;
EXEC dbo.DropIndexes;
/*
CREATE INDEX east
ON dbo.Posts
(PostTypeId, Score, OwnerUserId)
WITH ( MAXDOP = 8,
SORT_IN_TEMPDB = ON,
DATA_COMPRESSION = ROW );
*/
DROP TABLE IF EXISTS #t;
… Annoyances When Indexing For Windowing Functions
One Day
I will be able to not care about this sort of thing. But for now, here we are, having to write multiple blogs in a day to cover a potpourri of grievances.
Let’s get right to it!
First, …
An Overlooked Benefit Of Batch Mode In Parallel Plans
Make It Count
When queries go parallel, you want them to be fast. Sometimes they are, and it’s great.
Other times they’re slow, and you end up staring helplessly at a repartition streams operator.
Sometimes you can reduce the problem …
An Overlooked Benefit Of Batch Mode With Windowing Functions
Lavender
If you ask people who tune queries why batch mode is often much more efficient with windowing functions, they’ll tell you about the window aggregate operator.
That’s all well and good, but there’s another, often sneaky limitation of fully …
Batch Mode On Row Store vs Batch Mode Tricks
Quiet Time
I think Batch Mode is quite spiffy for the right kind of query, but up until SQL Server 2019, we had to play some tricks to get it:
- Do a funny join to an empty table with a
Query Tuning SQL Server 2019 Part 5: I’m Not Going Back
Butt Out Bag
There was one thing that I didn’t talk about earlier in the week.
You see, there’s a mystery plan.
It only shows up once in a while, like Planet X. And when it does, we get bombarded …