Table/Index Storage

Question

Table/Index Storage

SQLSACT

SSC-Insane

Points: 21838
More actions
September 22, 2012 at 9:00 am

#393237

Hi All
I have a table with just one Index on it (Clustered)
I checked the storage of the table by right-clicking on the table, selecting properties and selecting storage.
Shouldn't the values for data space and index space be the same?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

No, they will be different.

Your structure (with both the data and the index) is a tree of pages. The data pages are just the leaf pages in that tree. There is at least one other page, because the tree has to have a root.

Tom

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

L' Eomot Inversé (9/22/2012)
No, they will be different.
Your structure (with both the data and the index) is a tree of pages. The data pages are just the leaf pages in that tree. There is at least one other page, because the tree has to have a root.

Ok

I figured that if the table has a Clustered Index, because the rows of the table actually is the leaf of the Index, th values would be the same

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

Tom is correct, almost.

All indexes will have a root page, but only if there are more than one data page. If there is only one data page, then there is no need for a root page.

Proof:

use tempdb

go

create table root_test(id int primary key, some_text char(1000))

go

insert into root_test values(1,'')

-- used_pages=2, data_pages=1

select * from sys.allocation_units where container_id=(select hobt_id from sys.partitions where object_id=object_id('root_test'))

-- Will return two rows. One IAM page, and one data page. There are no index pages at this point

-- because the index only has one page.

dbcc ind('tempdb','root_test',1)

-- Let us insert seven more rows. The row with id 8 will not fit in the existing data page

-- so the page will be split. We now have two data pages, so SQL Server will now need an index page, the root page.

insert into root_test values(2,'')

insert into root_test values(3,'')

insert into root_test values(4,'')

insert into root_test values(5,'')

insert into root_test values(6,'')

insert into root_test values(7,'')

insert into root_test values(8,'')

-- used_pages=4, data_pages=2

select * from sys.allocation_units where container_id=(select hobt_id from sys.partitions where object_id=object_id('root_test'))

-- Will return four rows. One IAM page, one index page (the root page) and two data pages.

dbcc ind('tempdb','root_test',1)

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

Nils Gustav Stråbø (9/22/2012)
Tom is correct, almost.
All indexes will have a root page, but only if there are more than one data page. If there is only one data page, then there is no need for a root page.
Proof:
use tempdb
go
create table root_test(id int primary key, some_text char(1000))
go
insert into root_test values(1,'')
-- used_pages=2, data_pages=1
select * from sys.allocation_units where container_id=(select hobt_id from sys.partitions where object_id=object_id('root_test'))
-- Will return two rows. One IAM page, and one data page. There are no index pages at this point
-- because the index only has one page.
dbcc ind('tempdb','root_test',1)
-- Let us insert seven more rows. The row with id 8 will not fit in the existing data page
-- so the page will be split. We now have two data pages, so SQL Server will now need an index page, the root page.
insert into root_test values(2,'')
insert into root_test values(3,'')
insert into root_test values(4,'')
insert into root_test values(5,'')
insert into root_test values(6,'')
insert into root_test values(7,'')
insert into root_test values(8,'')
-- used_pages=4, data_pages=2
select * from sys.allocation_units where container_id=(select hobt_id from sys.partitions where object_id=object_id('root_test'))
-- Will return four rows. One IAM page, one index page (the root page) and two data pages.
dbcc ind('tempdb','root_test',1)

Thanks

What I'm trying to figure out is how the storage is calculated in SQL Server, when you right click on a table, check properties and storage.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 5

The best way to find out how it calculates it is by starting a Profiler trace with a filter on your login.

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

Nils Gustav Stråbø (9/23/2012)
The best way to find out how it calculates it is by starting a Profiler trace with a filter on your login.

Will give it a shot

Thanks