Splitting database into seperate files

Question

Splitting database into seperate files

Gail Shaw

SSC Guru

Points: 1004504
More actions
September 12, 2006 at 6:18 am

#114331

Hi all
I'm working with a rather large database (~500GB) and am planning to split the database into multiple files on different drives (got a new SAN coming in a few weeks)
Are there any recomendations on how to go about splitting the database apart? I've got several options and I'm not sure which would have the greatest impact.
I have info on which of my tables are the more frequently written to and which are most frequently read from and one option I have is to split apart the often written from the often read.
I'm also considering the possibility of spliiting based on functionality, putting tables used by one section of the system away from tables used by another section.
Conversly, I've also considered splitting related tables apart. If two table are frequently written to at the same time, put them on seperate files.
Has anyone been through a similar exercise? Is there anyone who would like to share how they split large databases apart? Are there any good references or recomendations available?
Thanks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Gail

We have our tables on one filegroup and non-clustered indexes on another. While this may not provide the best performance in every case, it is a satisfactory generic solution and it makes it easy for developers to write scripts for the creation of objects.

For a database the size of yours, I would look at splitting the tables into more than one filegroup as well - maybe have a look at what tables are joined the most often and split them into different filegroups.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I forgot to mention, we've currently got three filegroups, clustered indexes in 1, nonclusters in the second and some of the tables text images in the third and we're looking to split further for performance reasons.

Thanks. I'll certainly look at splitting the joined tables apart.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

May be obvious for some but not for me... Are the joins performed much faster if you use 2 filegroups or if it's just a tad faster (like 10%)?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

The two filegroups would have to be on separate physical disks for there to be any performance improvement. I've never analysed it, but I would think if you're pulling a lot of information from both sides of a join (and there aren't any covering indexes) then you must be looking at it going at least slightly faster. You'd need to test it in your own environment, but that is where I would start.

John

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Thanx!

Now let's see if someone's gonna buy me that environement .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

It depends on whether the data required for the join is in memory or on disk. If both tables are in memory, then there will be no gain (because no ios are been done)

If both tables are on disk, then having them on seperate physical devices (not logical devices) will speed up the query, because the ios can be done in parallel from different drives, rather reading then from a single drive one after the other.

I don't have a measure of how much faster though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Thanx again. I'm sure somone has a good idea of how much gain there is. I'm sure it can't be much more than 80% unlesss you pass from a ridiculy slow disk to a san array!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

I wouldn't personally think more than about 10-20% from just splitting the files. Now if you're moving to faster disks as well, you'll get an increase from that as well.

We're in the process of installing a faster SAN, so the file splits are been planned at the same time, while we have the luxury of redesigning the disk array.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass