Clustered indexed fields hash matching. Does it matter?

Question

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?

Answer 1

SQL Server is very good at deciding the best, most efficient execution plan for any given query. Since you have no predicates, such as a WHERE D500M.PERSON_REF = 123456, SQL Server is most likely assuming an index scan is the quickest way to gather all the relevant details from each table. If you add a predicate to the query, you will most likely see an index seek instead. This is to be expected.

"Good, bad or irrelevant" is only something you can decide. Is the performance acceptable for your needs?

You could try forcing the join type to see what happens if a loop join or a merge join were used instead. Something like:

SELECT p.PERSON_REF, p.surname, p.first_forname, e.employee_number, ph.MAIN_FLAG
FROM D500M p
INNER LOOP JOIN D550M e ON e.PERSON_REF=p.PERSON_REF
INNER LOOP JOIN D580M ph ON ph.PERSON_REF=p.PERSON_REF

If you want a more conclusive answer, you should provide code for a minimally complete verifiable environment that matches your environment. i.e. provide us table definitions, the actual queries involved, and the actual query plans you're seeing.

Answer 2

I think when you have under 10K rows on each table and all join columns are clustered index then Index Scan do matter.

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

Here e.PERSON_REF ,p.PERSON_REF ,ph.PERSON_REF all are Clustered index

Why you have mention non clustered index when NON CI is not visible in this query and also NON CI is not require from this query point of view.

Since CI actually store the row data.

you are getting HASH Match because index is not being utilized.Index is not being utilize because of poor cardianility estimate.

So hope you have correct choice of CI and data type is correct.

Sometime changing the order of join correct the cardianility estimate.

your query or table relation seem fishy.

On D500M for example, I have a CI on person_ref (the join field), and a Non CI on person_ref that INCLUDES forename and surname

you do not need to have CI and NON CI on same column.NON CI is of no use and covering index definitely not.

Because by definition of CI ,"CI actually store the row data" so it will directly locate all rows.So you should remove NON CI and it will definitely not improve your query.