Why SQL Server uses a nested loop

Question

I have a request, for example:

SELECT TOP 10 
  User.id,
  User.Name,
  Country.Country
  FROM User  
  Inner Join Country 
  ON Country.Id = User.CountryId
  where User.PlanId = 1

In this case, the SQL manager shows in the execution plan that Hash-match uses, and it is pretty fast.

But if I use when User.PlanId = 2, the SQL manager uses a nested loop for my query, and it is very slow ... Why does it use different algorithms with different search criteria? How can i fix this?

Answer 1

I'm going to assume that you have a lot more users with PlanIDfrom 2 than from 1.

exec, . A HASH MATCH ( ) . , , .

A NESTED LOOP , , , .

A HASH MATCH , . , . :

SELECT PlanId, COUNT(*) as CT
FROM User
GROUP BY PlanID

..., .