[Edit 8/25/2013: The tool referenced in this post is no longer available.]

During SQL Server 2005 development I did a comprehensive rewrite of the Books Online entries concerned with estimating index, table, and database size (see http://msdn2.microsoft.com/en-us/library/ms187445.aspx). I was recently having a discussion with Øystein Sundsbø about a bug in one of the formulas I wrote and it turns out he’s written a neat tool that codifies all the formulas.

 

In case you’re interested, the part of the formula I had incorrect was for figuring out how many pages would be needed for the upper levels of an index b-tree. My formula was:

 

Num_Index_Pages = ∑Level (Index_Rows_Per_Page)Level – 1
where 1 <= Level <= Levels

 

and Øystein came up with a better formula:

 

Num_Index_Pages = ∑Level (Total_Num_Rows/(Index_Rows_Per_Page)Level )
where 1 <= Level <= Levels

 

where in both formulas:

 

Levels = 1 + log Index_Rows_Per_Page (Total_Num_Rows / Index_Rows_Per_Page)

 

Check out Øystein’s cool tool at http://dbgoby.blogspot.com/2007/10/db-goby-v10.html!