Size of an index

Question

Size of an index

sunny.tjk

SSCertifiable

Points: 5041
More actions
October 22, 2012 at 9:15 am

#263603

Is it possible to estimate the size of an index before creating it?

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

Yup.

(Size of index key + size of clustered index key) * number of rows / 8196 = number of leaf level pages required. It's an under-estimate, but it'll be within a reasonable error.

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Try reading these:

Estimating the Size of a Nonclustered Index

http://msdn.microsoft.com/en-us/library/ms190620(v=sql.100).aspx

Estimating the Size of a Clustered Index

http://msdn.microsoft.com/en-us/library/ms178085(v=sql.100).aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 3

GilaMonster (10/22/2012)
Yup.
(Size of index key + size of clustered index key) * number of rows / 8196 = number of leaf level pages required. It's an under-estimate, but it'll be within a reasonable error.

Does size of index key mean the size of nonclustered index?

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

No, it means the size of the columns you've chosen as key columns. If it meant the size of the index (which we're trying to calculate) it would be a rather recursive definition.

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

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 5

GilaMonster (10/22/2012)
No, it means the size of the columns you've chosen as key columns. If it meant the size of the index (which we're trying to calculate) it would be a rather recursive definition.

Got it. Thanks Gail.