Sql-server – SQLServer Split Database File and FileGroups

groupsperformancesql serverstorage-area-network

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

Best Answer

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.

Related Topic