The query below is using the StackOverflow2010 sample database:

DECLARE @Id INT = 18471

SELECT c.UserId FROM dbo.Comments AS c
ON c.UserId = u.Id
AND c.UserId = @Id

With a hash join hint, it returns the following error:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

It works when I add OPTION(RECOMPILE) or replace the variable with a string literal.

What could be causing this issue?

  • 2
    Hi, thanks for replying. The original query is using hash join hint to deal with a parameter sniffing issue. The query above is just a simple sample to replicate the issue found. – JieLong Jul 7 '20 at 12:34
  • 1
    This is because of implied predicates. There is a Craig Freedman blog post explaining it that I have linked previously on this site – Martin Smith Jul 7 '20 at 15:11
  • @MartinSmith That does seem plausible, but why would the error only occur when using local variables and not with literals and OPTION(RECOMPILE)? – JieLong Jul 7 '20 at 15:50
  • 4
    Not sure at the moment. The blog post explaining why you get this some times is docs.microsoft.com/en-us/archive/blogs/craigfr/… – Martin Smith Jul 7 '20 at 18:19
  • Is it a theoretical question, or do you think you really need a HASH JOIN for this query? With Comments.UserId fixed, Users.Id is fixed, too. Whereas there is only one user with any specified Id, the whole join operation is redundant. Futhermore, the Users table is not required at all: SELECT UserId FROM Comments WHERE UserId = @Id. Even futhermore, the query does not make any sense to me because it returns the given Userid in as many rows as there are comments by that user. What is the use of this redundant data? – Ant_222 Oct 17 '20 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.