Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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

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
BarryTice
Asked:
BarryTice
  • 2
1 Solution
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
BarryTiceAuthor Commented:
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
 
BarryTiceAuthor Commented:
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now