深度探索:Clustered Index Scan vs Table Scan

环境: 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 Scan operator tells you that the scan was not ordered, meaning that the access method did not rely on the linked list that maintains the logical order of the index.