SQL SERVER执行计划选择原理
一直遇到多张表进行连接后执行计划随着连接数目的变化而变化的现象，最初以为跟连接的数目有关，后来发现是不对的。求教老大。
一张大表（132W数据行），分别与4、5、6张纬度表相连，4张表时0-1秒，5张表时10-16秒，6张表时0-1秒。由于这几张表调用都很频繁，所以都在缓存中。
我使用了set statistics profile on和set statistics io on等选项进行了观察。由于外侧表都非常小，只有几行或是几行，个别表只有1行，所以大部分外侧表都没有建立索引，所以连接大多执行的是hash join。但是经过观察后并不是因为sql server选择hash join 而不是nested loop所造成的，而是其中一张只有一行的表坐在的缓存页被重复读了132W次。
示例如下：
5张表连接：

select count(1)
from Fact_test_MS_brand a11
inner join LU_region a16 on (a11.Region_id = a16.Region_id)
inner join LU_POPDB a15 on (a11.POPDB_id = a15.POPDB_id)
inner join LU_category a14 on (a11.Category_id = a14.Category_id)
inner join LU_brand a13 on (a11.Brand_id = a13.Brand_id)

Table 'LU_brand'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_category'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_POPDB'. Scan count 17, logical reads 428, physical reads 0, read-ahead reads 52, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_region'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact_test_MS_brand'. Scan count 17, logical reads 34933, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(19 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
  CPU time = 3019 ms, elapsed time = 313 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------
6张表连接：

select count(1)
from Fact_test_MS_brand a11
inner join LU_region a16 on (a11.Region_id = a16.Region_id)
inner join LU_POPDB a15 on (a11.POPDB_id = a15.POPDB_id)
inner join LU_category a14 on (a11.Category_id = a14.Category_id)
inner join LU_brand a13 on (a11.Brand_id = a13.Brand_id)
inner join lu_city a12 on a11.city_id=a12.city_id

Table 'LU_POPDB'. Scan count 17, logical reads 428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_brand'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_city'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_region'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_category'. Scan count 16, logical reads 1327428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact_test_MS_brand'. Scan count 17, logical reads 34933, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(22 row(s) affected)

(1 row(s) affected)

SQL Server Execut