Attempts to reduce duration by avoding hash spill under a nested loop drived parallel plan

GokhanVarol 2013-07-08 02:32:05

I ran into a hash spill under a driver and I am trying to improve the duration by eliminating spill by any other means, but I did not get too far so far.

Marie_UnvPclId_TableInsert_Spill_3869.queryanalysis : Spilled quite a bit

Marie_UnvPclId_Discard_FakeVarForEdition_Spill_2671.queryanalysis : To lessen the spill I used an optimize for to hide secondary index key to get larger estimation, still spilled but duration seems better. Higher memory grant

Marie_UnvPclId_UnionAll_No_Spill_3277.queryanalysis : This time to eliminate the spill I created an empty temp table identical to the hash build, updated it's statistics by 10mil, union all into the hash. No spill happened but Duration was worse than the one prior with some spill.

Can this hash spill handled better?
Thank you

link text