Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

SQL Server Myth on Clustered Index page allocation or B-Tree formation

Here is another SQL Server myth which I realised while understanding anatomy of index pages.

For a long time I thought data and Index pages will be allocated once We create a table.

Its a myth. but the reality is

1. Once you create a table and if a table is empty, no data and index pages will be allocated.
2. A table gets its first data page only when we insert our first record in a table
3. Clustered index page will be allocated only when a table gets second data page (there is no need to have index page for only one data page right.)
4. NonClustered Index page is allocated even if single/first data page is available.

Note: And by the way SQL Server uses B+Trees and not B-Trees. its a another good myth.
(Reference MS Press : SQL Server 2008 Internals, Chapter 6, Page Number 300)

here is the script to demonstrate my understanding:

1. Lets create a simple table having clustered index and one non clustered index

create table Employee(
intEmpId int identity(10001,1
),
strFirstName varchar(100
),
strLastName varchar(100
),
strDeptId varchar(6
),
strSSNId int
)

go

Create Clustered index CI_employee_intEmpId on employee(intEmpId)
GO
Create NonClustered index NCI_employee_strSSNId on employee(strSSNId
)
GO

2. Lets check data and index page allocation.

DBCC TRACEON(3604
)
DBCC IND('CRS_ISG', 'Employee', -1
)
DBCC TRACEOFF(3604
)

Finding: No Data and Index page is allocated for a empty table

3. Lets Insert 10 records in sample table with some dummy values

set nocount on
insert into employee(strFirstName, strLastName, strDeptId, strSSNId) values ('AAA','AAA','DEPT01',round(rand()*1000000,0
))
go 10

4. Lets check data and index page allocation again

DBCC TRACEON(3604)
DBCC IND('CRS_ISG', 'Employee', -1
)
DBCC TRACEOFF(3604
)

Finding: 
a. Two IAM pages are allocated (one for Clustered Index and another for Non Clustered Index)
b. One data page is allocated
c. Index page for Non Clustered Index is allocated
d. Index page for Clustered Index is not allocated so far. Previous and Next page pointers shows 0 in data page (as we have only one data page)

5. Now lets try to make this table consume one more data page by inserting 200 more records

set nocount on
insert into employee(strFirstName, strLastName, strDeptId, strSSNId) values ('AAA','AAA','DEPT01',round(rand()*1000000,0
))
go 
200

Lets see page allocation now:

DBCC TRACEON(3604)
DBCC IND('CRS_ISG', 'Employee', -1
)
DBCC TRACEOFF(3604
)

set nocount
on
insert into employee(strFirstName, strLastName, strDeptId, strSSNId) values ('AAA','AAA','DEPT01',round(rand()*1000000,0
))
go
200

Finding: 
a. Second data page is allocated.
 
b. Clustered index page is allocated once a clustered index table gets its second data page.
c. Previous and Next page pointers are updated

Conclusion:
First Clustered Index page is allocated only when a table gets its second data page and there is no point of assuming b-tree for a single data page.

Reference: Paul S. Randal blogs

Comments

Posted by Anonymous on 20 July 2011

Pingback from  Wednesday Weekly #sqlserver Links for 2011-29 | sqlmashup

Leave a Comment

Please register or log in to leave a comment.