Tool for estimating the size of a database

[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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.