calculating index size

Question

calculating index size

Gabriel P

SSCarpal Tunnel

Points: 4289
More actions
March 11, 2009 at 9:42 am

#197996

Hello, I am seeking some clarification on how to calculate index size as I appear to be off in my calculations from what I see in sys.dm_db_index_physical_stats
I'm trying to calculate the size of a nonclustered index with a composite key (int, int, datetime) = 16 bytes. The 3rd field (datetime) is also clustered index key.
My table has 3354939 records. So my calculations show that:
Index Level 0: 3354939 records * 16 byte key / 8060 bytes per page = 6660 pages
Index Level 1: 6660 records * (16 byte key + 6 byte pointer) / 8060 bytes per page = 18 pages
Index Level 2: 18 records * (16 byte key + 6 byte pointer) / 8060 bytes per page = 1 page
So the page count should look like:
6660
18
1
Instead it looks like
12375
59
1
And this is after a index rebuild. Anybody have any idea what I'm doing wrong?

Viewing 4 posts - 1 through 3 (of 3 total)

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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

Unfortunately it's not quite that simple ... each row has other information associated with it - a null bitmap for example. If your clustered index is not unique then some rows may have an additional 4 byte uniqueifier. And did you take fillfactor into account?

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=sr_1_1?ie=UTF8&s=books&qid=1236794301&sr=8-1 has a lot of information on index internals if you have access to a copy, and it's well worth buying if you don't.

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

There's also the row header, the null bitmap and the variable column offset to take into account. If the clustered index is not unique, you also have a hidden 4 byte uniquifier on some of the rows

Index level 1 stores twice the number of rows that you have accounted for, as it has the first and last row of each underlying page.

Are all the index pages completely full? If the default or specified fillfactor is not 100%, then there will be empty space on each 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

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 3

Gabriel P (3/11/2009)
Hello, I am seeking some clarification on how to calculate index size as I appear to be off in my calculations from what I see in sys.dm_db_index_physical_stats
I'm trying to calculate the size of a nonclustered index with a composite key (int, int, datetime) = 16 bytes. The 3rd field (datetime) is also clustered index key.
My table has 3354939 records. So my calculations show that:
Index Level 0: 3354939 records * 16 byte key / 8060 bytes per page = 6660 pages
Index Level 1: 6660 records * (16 byte key + 6 byte pointer) / 8060 bytes per page = 18 pages
Index Level 2: 50 records * (16 byte key + 6 byte pointer) / 8060 bytes per page = 1 page
So the page count should look like:
6660
18
1
Instead it looks like
12375
59
1
And this is after a index rebuild. Anybody have any idea what I'm doing wrong?

With you all's help, I've accounted for some of the space (unique id and field is null). However I've been doing some testing using DBCC IND and DBCC Page and realized the uniquifier is a variable length field which requires 4 more bytes. So on top of the 22 bytes for non leaf nodes, add 4 (uniquifier) + 4 (variable length) + 3 (null bitmap) + 1 (row header).

I now get

Index Level 0: 3354939 records * 28 byte row / 8060 bytes per page = 11655 pages

Index Level 1: 11655 records * (34 byte row) / 8060 bytes per page = 50 pages

Index Level 2: 18 records * (34 byte row) / 8060 bytes per page = 1 page.

Close enough for me. Again thanks for the help