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], 
                         dbo.Contracts.Area
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,
  ContractID
  
  Jobs Index
  
  Index Key Column: JobID
  
  Included Columns: CustomerID, JobName, Area
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).