Does clustered table have IAM page?

Question

Does clustered table have IAM page?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Bear in mind that the definition of an index is not the same as the definition (in term of page type) of an Index page

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vgv8 SSChasing Mays Points: 627 More actions · Answer 2

Edited later: inserted script and corrected results both for clustered table and table with non-clustered index

------------------

Thanks!

Let me understand

5)

how is IAM used on clustered table (or any table with any index)?

5a)

Do I understand correctly that it is used only for Full Table Scan?

5c)

But indexed table never have Full Table Scans?having Full Index Scans?

If I "repeat" the mentioned article creating table but with and adding a new page without split :

using pagesplittest;

--table tNCI with nonclustered index

CREATE TABLE tNCI (c1 INT, c2 VARCHAR (1000));

CREATE NONCLUSTERED INDEX IX_tNCI_c1 ON tNCI(c1);

INSERT INTO tNCI VALUES (1, REPLICATE ('a', 900));

INSERT INTO tNCI VALUES (2, REPLICATE ('b', 900));

INSERT INTO tNCI VALUES (3, REPLICATE ('c', 900));

INSERT INTO tNCI VALUES (4, REPLICATE ('d', 900));

-- leave a gap at 5

INSERT INTO tNCI VALUES (6, REPLICATE ('f', 900));

INSERT INTO tNCI VALUES (7, REPLICATE ('g', 900));

INSERT INTO tNCI VALUES (8, REPLICATE ('h', 900));

INSERT INTO tNCI VALUES (9, REPLICATE ('i', 900));

and

using pagesplittest;

--table tCL with clustered index

CREATE TABLE tCL (c1 INT, c2 VARCHAR (1000));

CREATE CLUSTERED INDEX IX_tCL_c1 ON tCL(c1);

INSERT INTO tCL VALUES (1, REPLICATE ('a', 900));

INSERT INTO tCL VALUES (2, REPLICATE ('b', 900));

INSERT INTO tCL VALUES (3, REPLICATE ('c', 900));

INSERT INTO tCL VALUES (4, REPLICATE ('d', 900));

-- leave a gap at 5

INSERT INTO tCL VALUES (6, REPLICATE ('f', 900));

INSERT INTO tCL VALUES (7, REPLICATE ('g', 900));

INSERT INTO tCL VALUES (8, REPLICATE ('h', 900));

INSERT INTO tCL VALUES (9, REPLICATE ('i', 900));

Now, I am pending the transaction with insert (avoiding page split):

BEGIN TRAN;

INSERT INTO tNCI VALUES (11, REPLICATE ('k', 900));

--either/or

--INSERT INTO tCL VALUES (11, REPLICATE ('m', 900));

--rollback

--commit

The following dbcc

either

DBCC IND ('pagesplittest', 'tNCI', 1);

OR

DBCC IND ('pagesplittest', 'tCL', 1);

is blocked

6)

Why is dbcc is not blocled blocked on page split?

If I either commit or rollback the insert, then results of "DBCC IND ();" are:

- for table tNCI (with nonclustered index):

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID

------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------

1 371 NULL NULL 245575913 0 1 72057594039697408 In-row data 10 NULL 0 0 0 0

1 370 1 371 245575913 0 1 72057594039697408 In-row data 1 0 0 0 0 0

1 376 1 371 245575913 0 1 72057594039697408 In-row data 1 0 0 0 0 0

- for clustered table tCL

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID

------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------

1 378 NULL NULL 277576027 1 1 72057594040025088 In-row data 10 NULL 0 0 0 0

1 377 1 378 277576027 1 1 72057594040025088 In-row data 1 0 1 380 0 0

1 379 1 378 277576027 1 1 72057594040025088 In-row data 2 1 0 0 0 0

1 380 1 378 277576027 1 1 72057594040025088 In-row data 1 0 0 0 1 377

So, the new "PageType =2" index page (non-leaf level) does not appear for non-clustered index page and appear for clustered page.

7)

Should it be understood that non-leaf levels are created only in clustered index? Or when?

8)

Should I have expected the 2 lines back on rollback if page is added not due to split?

Because the article could have been understood that it is only in case of page split the adding of page is not rolled back

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

vgv8 (11/2/2010)
Do I understand correctly that it is used only for Full Table Scan?

It's one of the allocation pages, it'll be needed for more than just scans. There must be a way for SQL to identify which extents belong to which object (as opposed to just which are allocated and not which the GAM/SGAM do)

5c)
But indexed table never have Full Table Scans?having Full Index Scans?

An index scan can use the IAM, just a a table scan. Only in certain circumstances though

The new PageType =2 page does not appear.

This table does not have a clustered index, and your DBCC IND is only looking for the base table (the last parameter is index id) I'm going to assume that 1 is interpreted as 0 if there's no cluster, hence you won't see the index page because the base table has none. Add a clustered index and you'll see a page type 2, or look at the nonclustered index (index id >=2)

I suspect you'd benefit from more reading on overall architecture of indexes before fiddling with the low level internals.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass