Punching
…
CTEs Are Useful For Rewriting Scalar Functions
…
Category: Parallelism
…
Partitioning And Parallelism
Jammed, Not Jellied
UPDATE 2021-04-14: Microsoft has updated the documentation for all 2016+ versions of SQL Server to indicate that parallelism is available for partitioned tables in non-Enterprise versions.
There’s been a note in the documentation since SQL Server 2016 …
How Useful Is Column Store In Standard Edition?
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 …
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 …
What’s Really Different About In Memory Table Variables?
Kendra, Kendra, Kendra
My dear friend Kendra asked… Okay, look, I might have dreamed this. But I maybe dreamed that she asked what people’s Cost Threshold For Blogging™ is. Meaning, how many times do you have to get asked a …
When Index Sort Direction Matters
Ever Helpful
I got a mailbag question recently about some advice that floats freely around the internet regarding indexing for windowing functions.
But even after following all the best advice that Google could find, their query was still behaving poorly.…
Mind Your OUTPUT Targets
Browser History
I’ve blogged about OUTPUT a couple times, and those posts are Still Accurate™
But it’s worth noting that, for the second post OUTPUT forced the query to run serially …
When Does UDF Inlining Kick In?
The Eye
UPDATE: After writing this and finding the results fishy, I reported the behavior described below in “Somewhat Surprising” and “Reciprocal?” and it was confirmed a defect in SQL Server 2019 CU8, though I haven’t tested earlier CUs to …
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 …