Solved

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

Posted on 2008-10-21
3
289 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 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