Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Testing connection to sql 7 61
SQL Encryption question 2 61
SSRS report parameters set after publishing to report manager 1 64
SQL query with cast 38 54
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

827 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