Recently I was looking at a query generating deadlocks as a result of a clustered index scan.  I saw that someone forced a LOOP JOIN on one of the offending queries.  At first glance it appeared as if the LOOP JOIN should have made an index seek more likely; however, after remembering a side effect of LOOP JOIN and HASH JOIN hints I realized what was happening.  This message pretty much sums it up: Warning: The join order has been enforced because a local join hint is used.

Forced order

When you use LOOP JOIN, HASH JOIN, or MERGE JOIN hints the order of the tables listed in the join is forced.  In the scenario where I saw a problematic join, the “driving” table for the query should have been a small temp table relative to the large table being joined to. Here is a query I tested this with.

SELECT TOP 10 CustomerID INTO #TempCustomerIDs FROM CustomerTest

SELECT ct.* FROM CustomerTest ct INNER LOOP JOIN #TempCustomerIDs t ON t.CustomerID = ct.CustomerID

There is a unique clustered index on CustomerTest.CustomerID.  The CustomerTest table contains over 4 million rows.  The temp table only contains 10.  See the plan below: From the statistics IO and statistics time output you can see there is a pretty significant amount of logical IO against the temp table – 4,635,216 logical reads.  The query elapsed time was also quite high – 67,919 milliseconds. If we change the order of the tables in the query we see very different behavior.

SELECT ct.* FROM CustomerTest ct INNER LOOP JOIN #TempCustomerIDs t ON t.CustomerID = ct.CustomerID The statistics IO and statistics time output show very different results.  1 logical read for the temp table, 30 logical reads on CustomerTest, and 1 millisecond response time.

Why isn’t this a plan warning?

If you’re not familiar with plan warnings, they appear in execution plans to alert you of possible issues (i.e. implicit conversions, missing join predicates, etc..).  This seems like something that should appear as a plan warning, but it doesn’t.

Final note

If you run the test query more than once back to back the warning does not appear in subsequent runs because it has been compiled.  It appears this warning only occurs at the time the query compiles since subsequent calls are just retrieving a cached plan.

 

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store: