Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm in the process of changing a database server at my company from Windows 2000/Sql Server 2000 to a Windows 2003 R2/Sql Server 2005 now. It holds 30 databases, each of the databases is about 7gb in size, but one of the is 30gb. And now I'm wondering if I should use the opportunity to use filegroups on this database. But I've never used it before, and I don't know the content of the database that good. But it's a economy system, so I would think that over the last 8 years of production, it holds lots of historical "read-only" information.

Can anyone give me some hints and tips on if I should split it or not? I do have 2 seperate disks now, one for log files and one for the databases.

I'd appreciate if someone could give me some input :)

share|improve this question

migrated from stackoverflow.com Feb 17 '10 at 17:03

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

    
You should go on Windows Server 2008 R2 and even SQL Server 2008. Servers are often installed for 3 or 4 years, will you still want 2003 R2 in 3 years ? –  Mathieu Chateau Feb 17 '10 at 17:04
    
Yes, I know, but my boss won't let me. So I'll have to stick with 2003 R2 and Sql Server 2005. –  Svein Erik Feb 17 '10 at 17:05

3 Answers 3

up vote 6 down vote accepted

You want to split the database into multiple filegroups, or add multiple files to the existing primary filegroup?

In the first case you would need to move object (tables, indexes) into the newly added filegroup, otherwise it will stay empty. Doing so requires you to have very good understanding of the patterns of use of said objects so you can determine which objects goes where. The advantage after that will be that you'll be able to allocate filegroups to separate IO paths (separate disks/LUNs) according to how they are accessed. Another advantage is that you can manage backup/restore more granularity, allowing you to do piece-meal restores and allowing you to to individual filegroup backups. I would say that allocating filegroups in a database is a design time decision and for you is a little late now.

Second case you simply add more files to the PRIMARY filegroup in order to spread the IO across multiple disks. Unless you actually do have IO problems, and you do have multiple IO paths (ie. separate disks/arrays/luns to place the files onto), there is zero advantage in adding multiple files. You may run into advice recommending splinting the database into N files of equal size where N is the number of CPU cores, but that advice is obsolete as SQL 2005/2008 deal with the SGAM/GAM allocation contention much better than SQL 2000 and no longer require the split.

From your description of the problem and environment, I frankly see no reason to do any split: you are not going to make any fancy restore plan to allow for piece-meal restore (and besides its only 30Gb, which is rather small), and you have only one disk anyway so multiple files bring no advantage.

share|improve this answer
    
Ok, this was what I was looking for, thank you very much for your explanation Remus! :) I'll just restore it on the new server, with log files on one disk and the data file on the other disk. –  Svein Erik Feb 17 '10 at 17:19
    
+1 very complete answer! –  Nick Kavadias Feb 18 '10 at 0:09

I don't think you'll gain much performance if you split up your database into filegroups because you've only two dedicated disks and you already need one for your translogs.

I wouldn't split it.

share|improve this answer

Here is information on filegroups. They really only improve performance if you located them on separate drives. The other use of filegroups is to isolate read-only tables and be able to backup only active filegroups.

share|improve this answer

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.