I'm a software developer, but this month I've to do some db admin tasks. I've some knowledge gaps about how to split the database, here my first aproach:

  • FileGroup for a tables
  • FileGroup for a non-change tables
  • FileGroup for high datatables
  • FileGroup for text and images

Also I've a SAN with a RAID1 disk for the transaction log and RAID6 disk for the database files.

My question is, with this db configuration, I've to create a file for each filegroup? is correct the filegroup configuration? There is any performance leak?

Thanks in advance


How big is this database? How much traffic is it likely to see?

None of our SQL server databases have reached the point of needing more than one file for data and one for logs.

As doug_w says, test various configurations and see how they perform. But remember not to go overboard and make maintenance harder just becuase it will make your average response time 10% faster when the average with the simple was .1 seconds.

EDIT: After your comment giving size and user numbers. My feeling is that how you split the data up is going to depend on what they are all doing. Are they all going to be hitting the same few tables, or do they all hit most of the tables, or is it fairly randomly distributed? If all the users are likely to hit the same tables, be it a few or most. Then my first test configuration would be to put the data all in one file on one set of disks. Obviously though this is just a start so it still needs testing.

Also, just noticed you say you already have a RAID 6 for data. Can you change that, as in make to 2 seperate arrays. You will get no performance gain by splitting the database into seperate filegroups if all the files are then on the same set of spindles. If you are stuck with the one set of disks, then the only reason to split the database accross filegroups is to ease maintenance. Things like backups can be done at the filegroup level, so if you group all your frequently changing data in one filegroup, you could then back that up more frequently and not have to backup loads of static data.
Again, this does depend on your backup stratagy, if you dont have very tight recovery deadlines then you might be satisfied with a full backup nightly and just log backups during the day.

|improve this answer|||||
  • Thanks, The database isn't in an production enviroment yet. But we expect to have 5Kusers simultaneus and the size database can grow up to 350GB. – pau. Feb 18 '10 at 10:58
  • I have updated my answer a little. I could ramble on about different factors that will affect your decision for days, so I have tried to be a bit brief. If you want me to go into more detail anywhere then please ask and I'll try. If I get stuck then I'll rope in one of our real DBAs. (I'm just a programmer) – pipTheGeek Feb 18 '10 at 20:20

If you specify the purpose you may get better answers, but here is a shot at giving you some general info on the matter:using files and file groups.

You will need one file in every file group at a minimum. If you add more than one file to a filegroup sql server will utilize those files proportionally based on file size (data files only, log files are treated differently).

Tables that are frequently joined existing in separate file groups can yield performance gains.

Test Test Test.


|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.