Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following

  1. Creating custom table spaces organizes the physical files of the database therefore it might help improve the speed of disk reads

  2. Disk reads might also improve if your table spaces are partitioned.

  3. You will be able to clearly segregate database objects into separate physical locations (tables/clustered indexes/etc) Although I am not sure how this exactly works and how this might help improve the performance of the database

Now I'm not really sure if these assumptions of mine are correct and if so, what are the other advantages aside from what I have mentioned?

On the other hand, are there any quantitative measures that I can use in order to design my custom table spaces? For example, is there any mathematical formulas that I can use in order to set the growth size of table spaces according to the table's number of columns? Or is it more of predicting growth based on a timeline of gathering statistics of the table's usage?

One last thing, Is it much more efficient to segregate table spaces according to database object type? or according to logical design? (e.g. create separate table spaces for indexes and tables or create separate table spaces for each business module/function)

Please take note that I still have little experience with this and feel free to ask for any clarification if my question/s are not understandable

Edit: So I've read a bit about the mdf and ldf files and it seems to slightly address my problem with the growth of the database.

source: http://www.simple-talk.com/content/article.aspx?article=920

but still, Is there any importance of segregating per table space?

Edit 2: Okay based on further reading, if I am not mistaken, this basically falls on the category of segregating into different files/filegroups.. What advantages can I also expect from this aside from having to control the growth of the different mdf/ldf files, instead of maintaining just one big file?

share|improve this question

migrated from stackoverflow.com Feb 1 '13 at 11:20

This question came from our site for professional and enthusiast programmers.

    
If in any case I have misunderstood any concept that I might have used above, please feel free to correct me –  Avias Feb 1 '13 at 8:01
    
In SQL Server they're called data spaces. –  Remus Rusanu Feb 1 '13 at 8:06
    
Thanks for the correction @RemusRusanu –  Avias Feb 1 '13 at 8:08
    
okay now I'm confused.. what is the difference between table and data spaces? @marc_s –  Avias Feb 1 '13 at 8:24
    
bumping on this –  Avias Feb 1 '13 at 9:50

1 Answer 1

up vote 2 down vote accepted

A tablespace creates a layer of abstraction between physical and logical data

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower disk.

share|improve this answer
    
Very useful insight @Urban Björkman, but in terms of using a single disk, what other advantage can I expect if there are any? –  Avias Feb 1 '13 at 8:41
2  
Splitting the database into filegroups would force sql server to use multiple IO streams. If your disk is unable to handle it then you would most likley en up with worse performance. With a single disk system you probably get the best performance with one file in one filegroup –  Urban Björkman Feb 1 '13 at 9:01
    
Again a very useful insight. Thanks @Urban –  Avias Feb 1 '13 at 9:45
1  
Glad to be of assistance. Please accept answer if it is to your liking –  Urban Björkman Feb 1 '13 at 9:47
    
This only answers a part of my question and not all of it. I still need to know what the measurable advantages are and what would the efficient logical design be, whether to segregate by database object type or by using business modules.. all of this using a single disk system. :/ @Urban –  Avias Feb 1 '13 at 9:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.