3

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.

https://www.brentozar.com/pastetheplan/?id=Bkq1VjySm

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

5

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...

NUTS

... 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!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.