Split Data file into multiple file groups..!

[Query Builder]

I have one of our production Accounting Databases starting from 2 GB
now grown into a 20 GB Database over the period of a few years...

I have been getting timeouts when transactions are trying to update
different tables in the database.. Most of the error I get are I/O
requests to the data file (Data file of the production db
Accounting_Data.MDF).

I would like to implement the following to this Accounting database.

I need to split the Data file into multiple files by placing some of
the tables in different file groups. I have the server upgraded to be
able to have different drives in different channels. I can place these
data and log files in different drives so it will be less I/O
conflicts..

I would like to have the following file groups..

FileGroup 1 - which will have all database definitions (DDL).
FileGroup 2 - I will have the AR Module tables under here..
FileGroup 3 - I will have the GL module tables under here..
FileGroup 4 - I will have the rest of the tables under here
FileGroup 5 - I will like to place the indexes under here....

Also where will the associated transaction files go?

I would like to get some help doing this. Is there any articles / help
available that I can refer to. Any suggestions / corrections/
criticisms to what I have mentioned above is much appreciated...!

Thanks in advance....

[Erland Sommarskog]

Query Builder (qu**********@gmail.com) writes:

I have one of our production Accounting Databases starting from 2 GB
now grown into a 20 GB Database over the period of a few years...

I have been getting timeouts when transactions are trying to update
different tables in the database.. Most of the error I get are I/O
requests to the data file (Data file of the production db
Accounting_Data.MDF).

I would like to implement the following to this Accounting database.

I need to split the Data file into multiple files by placing some of
the tables in different file groups. I have the server upgraded to be
able to have different drives in different channels. I can place these
data and log files in different drives so it will be less I/O
conflicts..

Before you go ahead and split your databases, you should look into
what you can do by improving slow queries, either by rewriting them
or adding indexes. This can pay off a lot more than splitting up the
database over several filegroups.

Most of customers' databases are more than 20 GB in size, but nowhere do
have any more than the two standard MDF and LDF files. True, our system
is mainly back-office, so it's not the most update-intensitive in town.
Still it may give a hint that your system do not really need multiple
filegroups.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx