Filegroup for Indexes on Raid? Is it worth it?

[druer]

My system is about 50 gig and is a large RAID-5 system. Since I can't really control which "disk" a filegroup is on is there still any internal benefit to creating a separate filegroup for certain "heavily hit" indexes?

(I've "optimized" the heck out of the indexes themselves at this point and am looking for ways to squeek out any more gains I can make because there are a handful of tables that this third party application hits for nearly every user and every situation, some that are hit for every single activity.)

 

[SQLDenis]

There is a benefit
You can backup and restore a filegroup only


keep in mind that when you move/create a clustered index on another filegroup the whole table will be placed on that filegroup also. This is not the case for non clustered indexes

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

In this case I was actually thinking of some non-clustered, covering indexes that get hit very, very, frequently.

I'm not much of an "internals" type guy. I fully understand and have used filegroups on multiple disk systems effectively. In this case this server already has a single large disk array configured for Raid-5. Logically I'm having trouble understanding what SQL Server could or couldn't do differently if I created a new filegroup that would be using the same disk array. Sometime this year we'll be constructing a new server and merging 2 databases. From what I've read, and from my understanding if we create multiple arrays on the new SAN I could take full advantage, but in this case I'm kind of stuck with what I've got.

I wasn't sure if a seperate filegroup still offered any kind of performance benefits on a single Raid 5 disk array somehow or not. (I haven't read enough to know if SQL Server could use multiple threads if a query ended up pulling from 1 index on the primary group and 1 index from the second filegroup or not. ie I have several queries that join data from the same block of tables.)