Do I need to split a database to filegroups? It's 30gb now

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

Solution 1:

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.