Size of a clustered index

Question

Size of a clustered index

terry999

SSCertifiable

Points: 5036
More actions
September 27, 2012 at 9:00 am

#264925

Hi
I have a clustered index on a field that is defined as Datetime.
It is not unqiue so I believed the clustered index size would be
8bytes(datetime) + 4bytes (sql server adds for uniquesness) = 12 bytes
I changed the field to smalldatetime so I thought it would
4bytes(smallDT) + 4bytes (sql server adds for uniquesness) = 8 bytes
All queries on this readonly table use this date field to get a range of data. There will be many more indices which will use the clustered index so I'm trying to get it as small as possible.
When I examined the size of clustered index I was expecting the smalldate version to take upto 2/3 of the size of the datetime one (i.e. 8/12) yet its only about 8% smaller.
I rebuilt all indices on the table before and after datatype change
Have I misundertood the size of clustered indices..
I used
SELECT
i.name AS IndexName,
s.used_page_count * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('mytable')
ORDER BY i.name
compute SUM(s.used_page_count * 8)

Viewing 6 posts - 1 through 5 (of 5 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 clustered index is the table and hence has all columns present at the leaf level.

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

terry999 SSCertifiable Points: 5036 More actions · Answer 2

Thanks

I have a nonclustered index on this table with 2 fields numeric(8,0) + numeric(10,0)

5 + 9 + 12 (datetime clustered index) = 26 bytes

I Changed clustered index fields to smalldatetime

5 + 9 + 8 (smalldatetime CI) = 22 bytes

Now when I examine the size of this non clustered index before and after I do indeed get an approximate ratio of 22/26.

Is my logic sound?

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

Close

The non-leaf levels have just the key columns, leaf levels have all the columns in the table.

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

terry999 SSCertifiable Points: 5036 More actions · Answer 4

GilaMonster (9/27/2012)
Close
The non-leaf levels have just the key columns, leaf levels have all the columns in the table.

Thats a clustered index you describe?

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

Yes

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