Hash Match Spill

Posted on

Question :

I’ve got a hash match spill going on here.
I’ve updated statistics with FULLSCAN on the tables involved so it isn’t that.
Any pointers much appreciated.


I am on SQL 2017 Enterprise, with 64GB RAM.

Answer :

Given the size of the data, it may not be possible to avoid the spills entirely, but you can do some things to improve the query.

  1. Make sure your join and where clause columns are NC index keys
  2. You’re not selecting many columns, so don’t be afraid to INCLUDE them to make indexes covering
  3. Calculating the row number (RN) causes quite a sort and spill — it may be worth considering different indexing to fix that. Note that the expression you’re using there, DATEDIFF(day,s.COMPLETEDDATE,att.atd_date, is also used in…


… The non-SARGable predicate you’ve got for actual_day_difference (AND a.actual_day_difference BETWEEN -60 AND 90), which eliminates rows from ~5mm down to ~400k. Quite a reduction that may help the query early on.

The problem is that the entire result set for DATEDIFF(day,s.COMPLETEDDATE,att.atd_date) has to be generated, and then filtered out. Sticking calculations like that inside of a CTE, derived table, or unindexed view doesn’t persist them. See my Q&A here: SARGable WHERE clause for two date columns.

enter image description here

I’m not sure if there’s a relationship between attend and Study, but it may be worth either using a temp table or indexed view to materialize the expression to make it SARGable. You could also explore adding and populating an additional column to either table that holds the corresponding date data.

That would also open up additional indexing options for you, and the ability to materialize the ABS on DATEDIFF as well, if necessary.

Since this is for SQL Server 2014, it may be worth exploring nonclustered column store indexes, which would likely reduce the pain of trying to figure out appropriate row store indexes, and are more appropriate for larger DW-style queries like this one.

Hope this helps!

Leave a Reply

Your email address will not be published. Required fields are marked *