March 11, 2009 at 9:42 am
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?
March 11, 2009 at 12:00 pm
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.
March 11, 2009 at 12:29 pm
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
March 11, 2009 at 3:19 pm
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_statsI'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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply