I'm trying to performance tune some lengthy queries and I'm slicing bits out and running them in isolation.
The query below features three tables D500M, D550M and D580M, all joined on person_ref. All of these tables have clustered indexes on the person_ref fields. They also have non clustered indexes which include the fields in the select list.
These tables are not large - just under 10k rows in each.
select p.PERSON_REF,p.surname,p.first_forname,e.employee_number,ph.MAIN_FLAG from D500M p INNER join D550M e on e.PERSON_REF=p.PERSON_REF INNER join D580M ph on ph.PERSON_REF=p.PERSON_REF
I'm studying the Actual Execution Plan and seeing Index scans on the three tables (nonclustered), and then two Hash Matches on the joins, with costs of 39% and 41%.
The question is this; Is having these hash matches good, bad or irrelevant?