Sql-server – Execution plan – Hash Match


SELECT        dbo.Contracts.ContractID AS [Service Unit ID], dbo.Jobs.JobName, dbo.Customers.CustomerName, dbo.Contracts.ContractAutoID, dbo.Contracts.ContractType, dbo.Contracts.ContractStatus AS [Unit Status], 
FROM            dbo.Customers WITH (READPAST) INNER JOIN
                         dbo.Jobs WITH (READPAST) ON dbo.Customers.CustomerID = dbo.Jobs.CustomerID INNER JOIN
                         dbo.Contracts WITH (READPAST) ON dbo.Jobs.JobID = dbo.Contracts.JobID
WHERE        (dbo.Contracts.ContractType = 'service')

I understand Hash Match indicates the indexing is not appropriate, which is why it takes up so much space. I am wondering what i need to different with my indexing to get these Hash Matches to become nested loops. Below is a picture of the Execution Plans.

Customer Index

Index Key Column: CustomerID

Included Columns: City, CustomerStatus, CustomerName

Contracts Index

Index Key Column: ContractType

Included Columns: ContractAutoID, JobID, Area, ContractStatus,

Jobs Index

Index Key Column: JobID

Included Columns: CustomerID, JobName, Area

enter image description here

Best Answer

You could use INNER LOOP JOIN to cause a nested loop, but it may not improve the performance. Or use an OPTION (LOOP JOIN) hint to still allow the optimiser to decide join order (which is forced by join hints).