question

aRookieBIdev avatar image
aRookieBIdev asked

Size of the database

How to calculate the size of the database knowing the datatypes of the fields in table and the expected number of rows?
database-designcapacity-planning
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
To estimate the size of a database, estimate the size of each table individually and then add the values obtained. The size of a table depends on whether the table has indexes and, if they do, what type of indexes. It involves various calculations For tables it is the sum of size of clustered index + the sum of the size of all the non clustered index for heap it is page size i.e, 8192 * Number of pages that required to store all the rows To know more, see [Capacity Planning][1], article from Microsoft [1]: http://msdn.microsoft.com/en-us/library/ms187445.aspx
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I just tested Matt Whitfield's product named **Atlantis Data Space Analyser** and if this free tool is not the best thing since the sliced bread then I don't know what is. It graphically shows allocated, in-row, LOB, index etc spaces used by existing objects in the database. Since it is easy enough to write a script to mock the objects and data, the tool will let you see the whole picture before making a decision about storage requirements, and these picture will show exact numbers not just the estimates.
0 Likes 0 ·
@Oleg - Thanks - that means a lot to me coming from you!
0 Likes 0 ·
thank you.Its very useful.
0 Likes 0 ·
indexer avatar image
indexer answered
Database management systems must constantly face the trade-off of "speed vs. space," and generally speaking they tilt in favor of speed. Also, if the database is anticipated to be large enough that per-estimation of space requirements is likely to be a factor, many other issues can pop into reasonable consideration. You probably need to have a RAID array. You probably should distribute parts of the database to different drives and volumes. Once again you are paying, with space and drives, for ... speed, redundancy, and so on. Thus, the more "important" an advance space-estimate is perceived to be, the less likely it is to be accurate. And if you want to know the future growth rate of your database you should try Lepide SQL Storage Manager which having capability to forecast your database future disk space growth rate and manage the capacity of database via performing many operation like defragmentation, Partition, rebuild & reorganize the indexes etc [ http://www.lepide.com/sql-storage-manager/][1] [1]: http://www.lepide.com/sql-storage-manager/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

doesn't answer the question.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.