深度探索:Clustered Index Scan vs Table Scan .
有帖子http://topic.csdn.net/u/20080704/11/40975992-fcce-4807-bd65-b65cc0b39e09.html
提到索引扫描的问题，其中关tb上只建有聚集索引时，select * from tb到底是走聚集索引还是走iam，
还是有些疑虑，所以做了以下的测试，有经验的同学可以发表下意见。


环境: SQL 2005+SP2 ON Winxp SP3

SQL code

1.
use tempdb
go

create table tb(a int primary key,b char(5000) default('bbbbb'))
go

declare @i int,@v int
set @i=1
while @i<=400
begin
reval:
    set @v=abs(checksum(newid())%1000)
    if exists(select * from tb where a=@v) 
        goto reval
    insert into tb(a) values (@v)
    set @i=@i+1
end
go

2.找出IAM、Root、IAM指向的第一页的页面位置

SQL code

select [first],[root],firstIAM,* from sysindexes where id=object_id('tb')

我这里的值分别如下图:


--
转换成十进制後，IAM页是1:90;IAM指向的第1个数据页是1:77;索引根页是1:110

3.计算分别从IAM开始扫描和从ROOT开始扫描所应该得出的结果
[3.1] 从ROOT页开始扫描(Clustered Index Scan)
DBCC TRACEON(3604) --
DBCC PAGE(tempdb,1,110,3) --查看索引根页的内容
  结果如下图所示(图未截完)　


记好ChildPageID的值，稍後要按row的顺序依次检查ChildPageID的内容(数据页)
dbcc page(tempdb,1,434,1)
结果如下图，转换成十进制之后，a的值是7，即select * from tb order by a 返回的
第1行应该是7.

如次再往下检查下一个ChildPageID的内容(1:434)
并把检查a的结果记下来。


[3.2] 从IAM指向的第一页开始扫描(Table Scan)
DBCC TRACEON(3604)
DBCC PAGE(tempdb,1,90,3)--先查看iam页的内容，我的结果如下图


依次检查下面的Slot 0~Slot 7所指数据页的内容，就是mssql扫描的顺序

1:77 a的值是355
1:105 a的值是209
1:121 a的值是210
1:174 a的值是958
......

4.验证
[4.1]

SQL code

    SELECT * FROM TB WITH (NOLOCK)
/*    
a           b
----------- -----------
355         bbbbb      
209         bbbbb      
210         bbbbb      
958         bbbbb      
662         bbbbb      
490         bbbbb      
190         bbbbb      
101         bbbbb      
302         bbbbb      
51          bbbbb      
935         bbbbb      
630         bbbbb  
*/

a的顺序符合[3.2]所记录的值吧,说明此时走的是iam，即Table Scan !

[4.2]

SQL code

    SELECT * FROM TB 
/*
a           b
----------- ------------
7           bbbbb       
8           bbbbb       
9           bbbbb       
11          bbbbb       
13          bbbbb       
14          bbbbb       
15          bbbbb       
16          bbbbb       
17          bbbbb       
20          bbbbb       
21          bbbbb       
25          bbbbb       
26          bbbbb       
32          bbbbb       
33          bbbbb      

*/

可以见到a的值是有排序的，走的是聚集索引，结果应该是跟上面[3.1]记录的是一样.

5.结论

老实说，我被自己搞糊涂了。
我原本以为当聚集索引中的碎片很多时，SELECT * FROM tb 是会走iam扫描的，结果却不是，走的是索引扫描。
我用DBCC SHOWCONTIG(tb)查看索引碎片的值是98%,按理说不会去走index的，生成的执行计划
也是标了Ordered : FALSE(如下图)，但我试了很多次，结果看起来都是按a排序的。

然后我就试着加上一些查询提示，WITH INDEX啊什么的，当试到WITH (NOLOCK)时，结果终於不一样
验证后发现with (NOLOCK)就是走的iam扫描.

打开set statistics io on ，
发现select * from tb with (nolock)比select * from tb 少读取1个io，应该是略过了根索引页。




PS:下面的一段文字和图片都是来自於Inside SQL Server 2005这本书，说的是聚集索引扫描会走iam，这样看来是有问题的。
even though the execution plan shows a clustered index scan, the activity is no different than a table scan, and throughout the book I will often refer to it simply as a table scan. As shown in the illustration, here SQL Server will also use the index's IAM pages to scan the data sequentially. The information box of the Clustered Index Sca