Posted by Erland Sommarskog on 12/18/06 22:37

Query Builder (querybuilder@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]