October 17, 2014 at 10:48 am
I would like to know a simple way to estimate the size of a new index(s) I want to create to ensure I have enough space. I found a complicated article on this process (http://msdn.microsoft.com/en-us/library/ms190620.aspx) but would like something much more simple and straightforward.
As an example looking at an index that is non-clustered on a table with roughly 680,000 rows on two columns (int(4) and datetime(8)).
October 17, 2014 at 3:40 pm
If you're concenred about the exact size of the index, it would behoove you to read through that article more thoroughly. That or you could build the index in a dev-type environment and see what comes out.
The quick and dirty way to calculate the index size would look something like this. A nonclustered index contains it's indexed columns and a pointer to the primary key, plus a little overhead for each row, and some other variables. Think something like this:
[ Header | Indexed Columns | Primary Key Columns ]
Header: 6 bytes
Index Columns: Given your inputs, 12 bytes
Clustered index columns: Lets assume a 4 byte integer
you come away with about 22 bytes per row. Multiply that by 700,000 rows, and divide it by 1024 (to go from bytes to kb) and you come up with about 15,000kb.
I built this index on a temp table with a 4 byte PK and an index on a 4 byte integer and an 8 byte datetime2 column and came up with an index size of 18216 kb. Not bad from a napkin calculation.
The devil is in the details. I have no doubt that if I spent the time to look at all the variables that go into building the index those extra bytes would be in there somewhere.
October 17, 2014 at 3:52 pm
Yeah, you nailed it. The extra bites are in the balanced tree of the index. But your napkin calculation should be fine for most purposes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2014 at 4:05 pm
My code to calculate max row length for a table -- and an index isn't that different -- shows 30 bytes, assuming 4 bytes for the clustering key.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply