Using two filegroups on one RAID 10

Question

If a Microsoft SQL Server 2008 Standard Edition server is configured as below, does option 1 provide an advantage over option 2?

Base configuration:

32GB RAM
2 x Xeon 7460 (6 core)
Windows Server 2008 Standard SP2
Microsoft SQL Server 2008 Standard SP1
2 x 146GB 15K RPM HDD in RAID 1 (for Operating System)
2 x 146GB 15K RPM HDD in RAID 1 (for Logs)
4 x 146GB 15K RPM HDD in RAID 10 (for Data)

Option 1:

Configure one virtual disk on the RAID 10 and use one filegroup

Option 2

Configure two virtual disks on the RAID 10 and use two filegroups

Common sense tells me that there would be no speed advantage as the RAID 10 is still only able to read/write at the same speed.

Can someone advise if common sense prevails or if there is a reason I have overlooked why this would be advantageous?

score 2 · Accepted Answer · edited Apr 13 '17 at 12:14

Not sure if there is a performance enhancement for multiple file groups, but you might find that multiple files on your filegroup(s) will improve performance.

Review my contribution to this question: Why is CPU use so asymmetric on our 8-cpu SQL Server box?

We are careful to set up our biggest most often used tables to be on filegroups with multiple files in them. One of the performance enhancements of this is that SQL will thread requests to each file in the filegroup - so if BigOverUsedTable is on FileGroup1 and FileGroup1 has four files in it and your DB has 8 cores, it will actually use four cores to do "select big number crunching nasty query from BigOverUsedTable" - whereas otherwise, it will only use one CPU. We got this idea from this MSDN article:

http://msdn.microsoft.com/en-us/library/ms944351.aspx

From TFA:

"Filegroups use parallel threads to improve data access. When a table is accessed sequentially, the system creates a separate thread for each file in parallel. When the system performs a table scan for a table in a filegroup with four files, it uses four separate threads to read the data in parallel. In general, using multiple files on separate disks improves performance. Too many files in a filegroup can cause too many parallel threads and create bottlenecks."

We have four files in our filegroup on an 8 core machine due to this advice. It's working out well.

score 1 · Answer 2 · answered Aug 26 '09 at 14:18

1

The only benefits that you would get would be that the OS would have two physical disk queues within Windows instead of one, and that you'll be ready to add another array and move a database file to it in the future. Both are pretty minor benefits.

answered Aug 26 '09 at 14:18

mrdenny

27,074
4
40
68

Mmm Windows' physical disk queue.. a consideration I hadn't thought of, however I'm not convinced it's a good enough reason by itself. – PCurd Aug 26 '09 at 14:32
1

Probably not. You could always use one RAID 10 array, and two file groups. That way as the system grows it's much easier to migrate that second file group off to another array. – mrdenny Aug 26 '09 at 15:39

score 0 · Answer 3 · answered Aug 26 '09 at 14:12

0

If you have time, then you can always try it & see instead of guessing!

I don't see any perceived performance gain with 2 virtual disks over 1 virtual disk, but having additional files and filegroups is always good for future proofing.

Having your indexes in a separate filegroup is good practice, then you can always move the filegroup to a new set of spindles for an extra performance boost!

answered Aug 26 '09 at 14:12

Nick Kavadias

10,758
7
36
47

I'd like to do this but the machine doesn't have enough disks for this - going to a SAN etc would involve splitting the data files anyway. – PCurd Aug 26 '09 at 14:31
'splitting data files' is no easy feat in sql server. – Nick Kavadias Aug 26 '09 at 15:14

score 0 · Answer 4 · answered Aug 26 '09 at 15:24

0

There won't be any measurable speed advantage, but, as pointed out by others, having two filegroups is a more future proof concept.

answered Aug 26 '09 at 15:24

wolfgangsz

8,767
3
29
34

score 0 · Answer 5 · answered Aug 31 '09 at 21:10

Your two option just don't provide any benefit, as I/O will just be scattered around all physical disks in both configurations.

If you have 4 physical disks and you actually want to get the maximum possibile performance from them (*), here's how to do that:

Create 2 RAID 1 arrays, each one using 2 disks.
Create a single volume on each array.
Create a filegroup on each volume.
Optimize your database for maximum parallelism.

The last point is the most important one; with this configuration you can have the two filegroups effectively working in parallel, but this only gets useful if you can place your data in there in a way that allows doing I/O on both of them at the same time; so you could put different tables on different filegroups, or use one of them for tables and the other for indexes, or... you have endless solutions, it all depends on your actual workload.

(*) This really is an advanced optimization, in most cases it's just overkill, as a single filegroup/single volume/single array will be a lot simpler to manage and will achieve very similar perfomarnce, unless you actually can parallelize your I/O.

score 0 · Answer 6 · answered Aug 31 '09 at 21:44

Option 2 will very likely result in more physical movement of the drive heads as they need to go back and forth between the two logical disks. That may or may not offset any advantages gained by separate disk queues. Only tests using your environment could give conclusive results.

Using two filegroups on one RAID 10

6 Answers6