Help slower performance

Question

Hello,

I'm hoping someone could help me boost the performance of a query I'm working on:

select distinct ID1, col2+1000
from dbo.table1 as t1 with (nolock)
join dbo.table2 as t2 with (nolock) on t2.t2id = t1.t1id
join dbo.table3 as t3 with (nolock) on t3.t3id = t2.t2id
join dbo.table4 as t4 with (nolock) on t4.t4id = t3.t3.id
join #tempable5 as t5 with (nolock) on t5.t5id = t4.t4id

Table 1 has millions of rows and the proper indexes.
When I look at the execution plan, the hash match aggregate is at 35%.

Any help would be greatly appreciated.

Answer 1

Hi @M. Graham
SQL Server hash match aggregate is selected by query optimizer for the tables with large data and when they are not sorted.
Are you sure all joins on non-clustered indexes?
Hash operation builds a hash table in memory and Hash Key used for this table is displayed in properties window, could you check that?
Refer to this blog: SQL Server hash match aggregate operator

By the way, the alternative aggregation operator, Stream Aggregate, is faster and has less overhead; but it requires the input stream to be sorted. When no efficient way to sort the input is available, the optimizer will usually favor the Hash Match operator for aggregation. Similar to Stream Aggregate, Hash Match (Aggregate) can be used without a Defined Values property, to effectively perform a DISTINCT operation. Unlike Stream Aggregate, Hash Match (Aggregate) can not be used to compute a scalar aggregate (except in Batch Mode plans on SQL Server 2014 and up).

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.