Data File Stratergy

Question

Data File Stratergy

Jeet

SSCommitted

Points: 1536
More actions
January 14, 2005 at 7:34 am

#109968

We are currently looking at getting a new server and we are looking to see if by organising our data on different data files(which would be on different physical disks) we can get better performance.
At the moment we have all of our data and indexes on the Primary filegroup and all the files that make up the filegroup reside on the same disk. I wanted to know if:
1. Putting data and indexes on different filegroups can improve performance.
2. Putting files that makeup different filegroups would have a performance benefit.
3. How does the above fit in with RAID.
I'd appreaciate any comments from anyone who has experience with such stratergies and/or any appropriate links I can look at.
Thanks in Advance.

Thanks Jeet

Viewing 4 posts - 1 through 3 (of 3 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

IMO, the use of filegroups complicates mostly things. Unless you don't have a DB > 1 TB I would leave it in one filegroup and place this over as many disks as possible. See, if some of this links will give insight:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/ssmsam.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0326.mspx

http://www.microsoft.com/seminar/events/webcasts/ondemand.mspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_0m5g.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76000 More actions · Answer 2

Clustered indexes have to go on the same filegroup as their data table in any case.

I would agree with Frank. Theoretically there should be a slight performance gain using more than one filegroup as each filegroup gets its own thread but in practice you won't see it on small/medium systems.

As log files are sequential you may benefit from writing them to a different disk array than your data files. RAID 5 is supposed to have relatively slow data writing therefore log files are best going onto a RAID 1 array.

LinkedIn Profile

Jeet SSCommitted Points: 1536 More actions · Answer 3

Guys,

Thanks for the feedback and the links they are very helpful.

Jeet

Thanks Jeet