Does clustered table have IAM page?

Question

Does clustered table have IAM page?

vgv8

SSChasing Mays

Points: 627
More actions
November 1, 2010 at 1:18 am

#231063

I am trying to better understand SQL Server internals - indexing, data structures, memory/storage allocation management.
Using SQL Server 2008 R2 Dev (SSMS) on Windows XP Pro SP3
I follow (see by following link) Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back.
The beginning of article creates clustered table t1 in a separate database pagesplittest...
The first picture/scan in this article shows the output of
DBCC IND ('pagesplittest', 't1', 1);[
2 rows with PageType = 1 (data page) and PageType = 10 (IAM Page).
1)
Why is there no TypePage=2 line corresponding to index page?
2)
Why does this output has IAM Page line if the code creates clustered table?
According to all msdn docs the clustered table does not have IAM pages[/b], cf., for ex., "Clustered Index Structures" vs. "Heap Structures"
The prevailing majority of answers to my question: "No direct access to data row in clustered table - why?" supported this opinion. Is it correct?
I am having the same results if to run the script from this article creating separate database pagesplittest.
But if I create the table in my existing test database and run the same
DBCC IND ('myExsistingTestDatabase', 't1', 1);[
after the same t1 table creation and inserts (in existing database), then the output has 4 lines with PageType = 10, 1,2,1 (2 datapages, index page and IAM page).
3)
Why is such difference?
4)
Is it essential to create a separate database to study dbcc ind() or dbcc page()?

Viewing 15 posts - 1 through 15 (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

Both heaps and all indexes have one or more IAM pages to indicate what extents are allocated to that heap/index. The cluster is no different to a nonclustered index here, it also has at least one IAM page.

This may be worth a read http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-IAM-pages-IAM-chains-and-allocation-units.aspx

Also maybe an introductory indexing article, as I noted some misconceptions over at stackoverflow http://www.sqlservercentral.com/articles/Indexing/68439/ (3 parts in total)

Edit: The MSDN pages don't state that a cluster does not have an IAM. They just neglect to mention that it does.

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

Thanks for references but I am (also or more) concerned about the questions on how to use and interpret the output of "dbcc page()" and dbcc ind()", i.e. my other questions.

The results of such skills will be natural - for example, I would be able to analyze what is true or false in reality without necessity of asking something on forums

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

What questions?

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 4

GilaMonster (11/1/2010)
What questions?

I prefixed my question in original post with numbers.

They are now 1), 3), 4).

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

Post the exact code that you ran to create the tables and exact DBCC commands. I'd rather not guess.

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 6

I just executed the script from mentioned "Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back", up to first command with output (dbcc ind())

Script 1) - the same as in article, with creation of new database

USE MASTER;

GO

IF DATABASEPROPERTY (N'pagesplittest', 'Version') > 0 DROP DATABASE pagesplittest;

GO

CREATE DATABASE pagesplittest;

GO

USE pagesplittest;

GO

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

CREATE CLUSTERED INDEX t1c1 ON t1 (c1);

GO

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

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

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

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

-- leave a gap at 5

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

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

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

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

GO

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

GO

Script 2), the same scriptbut on my existing database (without creating a new database)

USE myExistingTestDatabase;

GO

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

CREATE CLUSTERED INDEX t1c1 ON t1 (c1);

GO

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

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

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

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

-- leave a gap at 5

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

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

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

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

GO

DBCC IND ('myExistingTestDatabase', 't1', 1);

GO

Script 1) outputs 2 lines

Scipt 2) outputs 4 lines

Please see questions 1), 3), 4) in my original post

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

I tried in a new database and an existing (and well-used) database, two rows in both.

1 row with a page type of 10, one row with a page type of 1.

SQL 2008 SP1

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 8

I am on SQL Server 2008 R2.

And, well, I reproduced results a dozen times already

4-lines output from Script 2) is:

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

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

1 284 NULL NULL 165575628 1 1 72057594039369728 In-row data 10 NULL 0 0 0 0

1 283 1 284 165575628 1 1 72057594039369728 In-row data 1 0 1 286 0 0

1 285 1 284 165575628 1 1 72057594039369728 In-row data 2 1 0 0 0 0

1 286 1 284 165575628 1 1 72057594039369728 In-row data 1 0 0 0 1 283

(4 row(s) affected)

And question #1 - why doesn't output of "DBCC IND()" (isn't IND here means index?), from Script 1), as well as shown in cited article and re-cited by you in both cases, contain anything corresponding to index page? i.e. any line with PageType=2 ?

My output from Scipt 1):

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

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

1 79 NULL NULL 2121058592 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0

1 78 1 79 2121058592 1 1 72057594038845440 In-row data 1 0 0 0 0 0

Note that output from Script 2) defeats the purpose of article to illustrate splitting of page and output from Script 1) defeats, INHO, the sense of command "dbcc IND()"

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

I also ran several times and the results are consistant. Two rows in each case. Sure there's not something else different between the databases?

DBCC IND shows all pages related to a table or index. I haven't looked carefully in this case, but if all data fits onto a single page, there may not be a index page, just one data page. Add another page of data and see what happens.

btw, check in your existing database what the default fill factor is and if it differs from your new database. if it's lower than for the new DB, those rows could have split over two data pages. That's certainly what it looks like in your output.

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 10

but if all data fits onto a single page, there may not be a index page, just one data page

This is the essence of this post, my main question (all others are collateral). It is clustered table and all table or all data is inside clustered index.

I have difficulties to interpret this result - a clustered table but no index (page)

Add another page of data and see what happens

If to add another page an index page appears.

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

vgv8 (11/1/2010)
I have difficulties to interpret this result - a clustered table but no index (page)

Why's it hard? With a single page there's no need for the b-tree structure, and that's what appears as type 2 pages. When a 2nd page is added, now you need a non-leaf page forming the upper portion of the b-tree.

With a clustered index, the leaf pages are type 1, it's the non-leaf pages that are considered index pages. Look at the index level that DBCC Ind returns, 0 is leaf, > 0 is a non-leaf 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 12

Thanks.

It is not hard but where is it written?

BTW, can you give definition of index?

I asked for this but had not got clear formulation, just round-about illustrations how it is used, etc.

http://stackoverflow.com/questions/3800918/what-is-index-and-can-non-clustered-index-be-non-unique

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

I doubt these kinds of internals are documented anywhere. After all, we're using undocumented features.

Definition of index depends who you ask and where you look. What's your context?

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 14

Context of SQL Server (for ex., 2008 R2), desirably other mainstream RDBMS

I am asking myself in order to understand docs (msdn articles, posts, BOL, etc.) as well as as interact with humans (IT colleagues, participants of forums, work clients/customers) on SQL server internals and explain it to others - clones of me (with the same experience, age, level of education, etc.) - on the basis of common terminology and predefined definitions of terms (for example, of index)