深度探索: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.

转载于:https://www.cnblogs.com/h-hello/articles/1249542.html