Performance tuning tips from the SQLGrease team

Join Hints – Careful, They Force Order!

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.

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.

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.