Why does this SQL result in Index Scan instead of an Index Seek?

Question

Can someone please help me tune this SQL query?

SELECT  a.BuildingID, a.ApplicantID, a.ACH, a.Address, a.Age, a.AgentID, a.AmenityFee, a.ApartmentID, a.Applied, a.AptStatus, a.BikeLocation, a.BikeRent, a.Children, 
        a.CurrentResidence, a.Email, a.Employer, a.FamilyStatus, a.HCMembers, a.HCPayment, a.Income, a.Industry, a.Name, a.OccupancyTimeframe, a.OnSiteID,
        a.Other, a.ParkingFee, a.Pets, a.PetFee, a.Phone, a.Source, a.StorageLocation, a.StorageRent, a.TenantSigned, a.WasherDryer, a.WasherRent, a.WorkLocation, 
        a.WorkPhone, a.CreationDate, a.CreatedBy, a.LastUpdated, a.UpdatedBy
FROM    dbo.NPapplicants AS a INNER JOIN
        dbo.NPapartments AS apt ON a.BuildingID = apt.BuildingID AND a.ApartmentID = apt.ApartmentID
WHERE   (apt.Offline = 0)
AND     (apt.MA = 'M')

.

Here's what the Execution Plan looks like:

.

What I don't understand is why I'm getting a Index Scan for NPapplicants. I have an Index that covers BuildingID and ApartmentID. Shouldn't that be used?

Answer 1

It is because it is expecting close to 10K records to return from the matches. To go back to the data to retrieve other columns using 10K keys is equivalent to something like the performance of just scanning 100K records (at the very least) and filtering using hash match.

As for access to the other table, the Query Optimizer has decided that your index is useful (probably against Offline or MA) so it is seeking on that index to get the join keys.

These two are then HASH matched for intersections to produce the final output.