5

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 :)

Svein Erik
  • 205
  • 3
  • 8
  • 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 Answers3

6

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.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
0

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.

grub
  • 1,118
  • 1
  • 7
  • 12
0

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.

Greg Bray
  • 5,610
  • 5
  • 36
  • 53