Sql-server – Using two filegroups on one RAID 10

raidsql serversql-server-2008

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?

Best Answer

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.

Related Topic