Solved

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 73
Where clause in stored procedure 8 57
Unable to save view in SSMS 21 70
Find data in a column which is not in  a date format 29 30
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 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