Spills Week: How Hash Join Spills Hurt SQL Server Query Performance

Posted on by Erik Darling

Thirsty Thursday

If you’ve made it this far, you’ve learned a few things:

  • Not all spills are worth trying to fix
  • The more columns you select, the worse spills get
  • The larger your string datatypes are, the worse spills get

Today’s post won’t prove much else different from those things, but follow along if you’re interested.

Batter Up

Our first example looks like this:

SELECT v.*
FROM dbo.Votes AS v
LEFT  JOIN dbo.Comments AS c
ON v.PostId = c.PostId
WHERE ISNULL(v.UserId, c.UserId) > 2147483647;

We’re joining Votes to Comments with kind of a funny where clause, again.

This’ll force us to join both tables fully together, and then filter things out at the end.

Maximum Bang For Maximum Buck.

With no restrictions, this query runs for about 18 seconds with a 4.6GB memory grant.

SQL Server Query Plan
Stolen wine

If we restrict the memory grant to 10MB, it runs for around 30 seconds. The spill is fairly large, too: 600k pages.

SQL Server Query Plan
Paul White Likes Cowbells

Dropping it down to 4.5MB follows a similar pattern. I told you. No surprises. Easy reading.

SQL Server Query Plan
Slightly less good, eh?

Spill level 6. 1.4mm pages. Runs for a minute eighteen.

It’s almost like memory is kind of a big deal for SQL Server, huh?

That might be something to consider the next time you look at the size of your data in relation to the amount of memory that pesky VM admin swears is “enough” for SQL server.

Home Team

Our first query was selecting all the columns from the Votes table.

This time, we’re gonna select everything from the Comments table, including that pesky NVARCHAR 700 column.

SELECT c.*
FROM dbo.Votes AS v
LEFT  JOIN dbo.Comments AS c
ON v.PostId = c.PostId
WHERE ISNULL(v.UserId, c.UserId) > 2147483647;
SQL Server Query Plan
Get in loser

About 22 seconds, with a 9.7GB memory grant.

If you recall up a little further, when we just selected the columns from Votes, the grant was 4.6GB.

Still big, but look at those string columns inflating things again. Golly and gosh.

With a 10MB grant, we shoot right to nearly 2 minutes.

SQL Server Query Plan
DEAR LORD

If you’re keeping score at home, bloggers are very patient people.

SQL Server Query Plan
My, my, my

That’s 4:32 of my life that I’m never getting back. And I have to waste it again because I forgot to look at the hash bailout extended event for this.

There we are.

SQL Server Extended Events
I’m bailing out of finishing this post tonight.

That represents a significant performance degradation.

Ahem.

Tomorrow, we’ll look at Exchange Spills, which represent an even worse one.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts