斯斯有三種，Join的訣竅也有三種!

最近遇到一件詭異的事，某個平常很穩定的SQL，忽然執行到逾時。上網查發現原來是統計資訊不及時導致SQL Server選錯執行計畫。原來平常SQL的Join在內部還可分為三種執行模式。似乎是如果table資料常有大量的資料異動，像是我們家APS每次跑一個計畫就要寫入上百萬筆資料，很容易因為統計資訊更新不及時出現選錯執行計畫的窘境，我們公司的DBA是建議就使用hash join 雖然不是最佳的選擇，但至少穩定。

以下為MSDN的說明:

出處:Advanced Query Tuning Concepts

SQL Server employs three types of join operations:

• Nested loops joins
• Merge joins
• Hash joins

If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons. For more information about nested loops, see Understanding Nested Loops Joins.
If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ sihttps://www.blogger.com/blogger.g?blogID=3153720234527010496#editor/target=post;postID=2111640605181692195gnificantly from each other. For more information, see Understanding Merge Joins.
Hash joins can efficiently process large, unsorted, nonindexed inputs. They are useful for intermediate results in complex queries because:

• Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not suitably sorted for the next operation in the query plan.
• Query optimizers estimate only intermediate result sizes. Because estimates can be very inaccurate for complex queries, algorithms to process intermediate results not only must be efficient, but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.

The hash join allows reductions in the use of denormalization. Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Hash joins reduce the need to denormalize. Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design. For more information, see Understanding Hash Joins.

這邊有大陸高手中文說明，有範例
 看懂SqlServer查询计划