четверг, сентября 07, 2006

Yet another one myth about clustered index in MS SQL 2k

So, there are many myths about clustered index. One of them is that when clustered index exists on the table, databse engine doesn't use IAM pages anymore to retrieve data, it uses doubly-linked list of pages. Also one of my favorite authors - Khen Henderson mentioned about that. This is partially true. I've found that in the read uncommitted isolation level, SQL Server still uses IAM to retrieve data. Here is the repro:

create database test
go
use test
go
drop table dbo.test
go
create table dbo.test (
i int primary key clustered identity(1, 1),
d int
)
go
insert into dbo.test (d)
select top 100000 checksum(newid()) from master..sysobjects s1, master..sysobjects s2, master..sysobjects s3
go

select * from dbo.test with(readuncommitted)
Clustered index scan, the result is:
3328 -1892742514
3329 -1303737718
3330 -1030174007
3331 -157548677
3332 1160183127
98533 -1095189775
98534 -1661674379
98535 -1640363165
98536 1313858725
98537 -271346219

select * from dbo.test
Clustered index scan, the result is :
3328 -1892742514
3329 -1303737718
3330 -1030174007
3331 -157548677
3332 1160183127
3333 -1669536656
3334 -327094669
3335 -1424229963
3336 -923814371
3337 655524158

Why so? Why we've got unsorted result in the first case (I know about ordering guaranties and so on, I'm speaking about different thing)? Let's go more deeply.

dbcc traceon(3604)
go
declare @db_id int, @tbl_id int
select @db_id = db_id('test'), @tbl_id = object_id('test')
dbcc tab (@db_id, @tbl_id)

PageFID PagePID IAMFID IAMPID ObjectID IndexID PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ------- -------- ---------- ----------- ----------- ----------- -----------
1 25 NULL NULL 2073058421 1 10 0 0 0 0 0
1 15 1 25 2073058421 1 2 0 0 0 0 0
1 28 1 25 2073058421 0 1 0 1 29 0 0
1 29 1 25 2073058421 0 1 0 1 30 1 28
1 30 1 25 2073058421 0 1 0 1 31 1 29
1 31 1 25 2073058421 0 1 0 1 33 1 30
1 33 1 25 2073058421 0 1 0 1 34 1 31
1 34 1 25 2073058421 0 1 0 1 35 1 33
1 35 1 25 2073058421 0 1 0 1 1704 1 34
1 1616 1 25 2073058421 0 1 0 1 1617 1 1631
1 1617 1 25 2073058421 0 1 0 1 1618 1 1616
1 1618 1 25 2073058421 0 1 0 1 1619 1 1617


Let's examine the page 1616:
dbcc page('test', 1, 1616, 3)

Slot 0 Offset 0x60
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
6B906060: 000c0010 000180e5 beb8baf1 000002 ...............
i = 98533
d = -1095189775

It is here!!! So, as you see, database engine still uses IAM to retrieve data like in the case of a heap.

Комментариев нет: