How do I estimate SQL Server index sizes

Question

While estimating straight row and table sizes are fairly simple math, we find it challenging to guess just how much space each index will occupy (for a given table size). What areas can we learn to calculate a better estimate and growth rate for indexes?

Answer 1

An index leaf has a preamble identifying the data page (7 bytes plus some directory information for variable length columns, if any) plus a copy of the key value (s) which will be the same size as the table data for those columns. There's one for each row in the table. The higher up levels of the index are much smaller, usually less than 1% of the leaves unless you are indexing a very wide key.

The fill factor leaves some space free so that updates and inserts do not generate excessive leaf splitting traffic.

EDIT: This MSDN link describes the page-level structures, although it's a bit light on the format of the individual index rows. This presentation goes into the physical format of disk log entries and data pages to some extent. This one more detail and includes the index data structures. Numeric and fixed length columns have the size it says on the box; you would have to estimate the average size of varchar columns.

For reference, some documents on Oracle's block format can be found Here and Here.