Keep’em Comin!
While helping someone tame a bunch of rather unfortunate scalar valued functions, we eventually hit a point where they were able to do some of the rewrites themselves. During testing, they ran into a situation where performance got …
Category: Execution Plans
UDF Inlining And String Aggregations
Quatro Enemigos
This post is really four different posts. Maybe five. The main points are:
- STRING_AGG can’t be inlined if a UDF
- Getting DISTINCT values into STRING_AGG is more obtuse than it should be
- The documentation for UDF inlining is
I/O Bound vs CPU Bound Queries
Handcuffed
When you’re looking for queries to tune, it’s important to understand which part is causing the slowdown.
That’s why Actual Execution plans are so valuable in newer versions of SQL Server and SSMS. Getting to see operator timing and …
Why You Should Stop Looking At Query Cost
Spare No Expense
Over the years, there have been a lot of requests to get sp_BlitzCache to sort results by query cost. I understand why. It’s assumed that the optimizer is never wrong and that cost is directly associated with …
Why Comparing Columns Doesn’t Always Seek
Adjoining
When writing queries, sometimes you have the pleasure of being able to pass a literal value, parameter, or scalar expression as a predicate.
With a suitable index in place, any one of them can seek appropriately to the row(s) …
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 …
A Parameterization Puzzle With TOP PERCENT
Lawdy
There was a three-part series of posts where I talked about a weird performance issue you can hit with parameterized top. While doing some query tuning for a client recently, I ran across a funny scenario where they were …
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 …
A Useful Rewrite Using APPLY
More To The Matter
In the year 950 B.C., Craig Freedman write a post about subqueries in CASE expressions. It’s amazing how relevant so much of this stuff stays.
In today’s post, we’re going to look at a slightly …