Solved

Table size overhead -- how much room does a table take?

Posted on 2008-10-21
3
279 Views
Last Modified: 2013-11-05
Greetings.

For starters, let me say that I'm somewhere below noob on SQL2005, and am fishing for information that I have to provide to others for how much of a server is likely to be consumed. So, forgive my lack of experience.

I have a user who is wanting space on a SQL2005 database for some testing parameter data.

Tables will have 10 to 30 columns, with 15 being typical.

Tables will have 2 to 30 rows, with 10 being typical.

Data will be simple strings, 2 to 80 characters, with 20 being typical.

That means, on average, the 150 cells with about 20 characters, being about 3K of data per table.

I understand that there are additional bytes for string terminations and such, and thus some overhead in simply storing strings. But, as my numbers are estimates, I'm still good with my estimate 3K of real data per table.

If it were Oracle, I would expect to give the user his own db, rather than a schema in an existing db. I would assume that SQL2005 is analogous, but I don't know that. (Which is why I'm here.)

Say there were going to be 150 of these tables. "Real" data consumption would be less than 1/2 MB. With all the overhead, how big a footprint should I expect this to consume on the server? How big a footprint should I expect if there were going to be 2,000 tables, consuming about 6 MB of "real" data?

Thanks.
0
Comment
Question by:BarryTice
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 125 total points
ID: 22768846
The answer to this type of question is that the data will be the amount of footprint.  If you have a SQL Server and a user needs a database, you should allocate a database to him.  The difference between Oracle and SQL in the database world is that Oracle's database has binaries installed for each database, where SQL Server is 1 engine binaries, and multiple databases managed by that instance of SQL.

So as far as overhead, there is overhead in a table to tune of about 36 bytes per page and each page is 8K.  But that is only the general answer.  

But to your questions:
If the data consumption of 1/2 MB or 3 MB or 6 MB, you should expect there to be overhead on the disk for the Log File, and the metadata in the Data file so I would allocate 4 MB for the 1/2 MB and 6 for the 3 MB, and 12 MB for the 6 MB.  But the reality is that 10 MB now a days, is not a really big hardship for a server.

Hopefully that answers your question.  Most of it depends on the constraints, not necessarily on the overhead.  For that little of data, you probably won't run into problems unless you are constrained by space.
0
 
LVL 7

Author Comment

by:BarryTice
ID: 22769154
OK. Thanks, dbaduck. I had assumed there would be more overhead, as there is in Oracle. Knowing that I can chuck a database onto the server for less than 50 MB tells me everything I need to know.

-- BarryTice
0
 
LVL 7

Author Closing Comment

by:BarryTice
ID: 31508370
Thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now