Non-Blocking Aggregations

Daniel Hutmacher tilts at windmills:

It’s not entirely uncommon to want to group by a computed expression in an aggregation query. The trouble is, whenever you group by a computed expression, SQL Server considers the ordering of the data to be lost, and this will turn your buttery-smooth Stream Aggregate operation into a Hash Match (aggregate) or create a corrective Sort operation, both of which are blocking.

Is there anything we can do about this? Yes, sometimes, like when those computed expressions are YEAR() and MONTH(), there is. But you should probably get your nerd on for this one.

There are many ways to solve a problem, and sometimes the best method is indirect.

Related Posts

Getting An Accurate Query Execution Time

Grant Fritchey shares some tips on accurate query time estimation: Before we get into all the choices and compare them, let’s baseline on methodology and a query to use. Not sure why, but many people give me blow back when I say “on average, this query runs in X amount of time.” The feedback goes […]

Read More

Ways To Check For Non-Existence

Brent Ozar shows two methods for finding records missing associated child records: You’re writing a query, and you wanna check to see if rows exist in a table. I’m using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are: In dbo.Users, the Id field […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031