In my last blog entry, I talked about VLDBs. One of the tactics suggested was to use as many data files on as many drives as possible to give better I/O performance. This makes sense, but the usual discussion arises as to whether to use multiple files in Filegroups or just use Hardware RAID. Of course, the answer is “it depends”. Let’s take a look.
When Microsoft introduced the Filegroup object into SQL Server 7.0, it was for administration and performance reasons. With the 2GB limit on file size in early versions of Windows NT, we needed a way of specifying multiple data files for a single large database. These multiple data files could then be grouped together into Filegroups for easier administration so we could backup part of a database without having to backup all of it every day. When a large part of a database is read-only this is a very useful feature speeding up the daily backup cycle. Performance gains were also apparent when multiple files in a Filegroup were hosted by different logical drives because SQL Server would stripe the data across the files giving faster read and write performance.
However, at the same time hardware RAID was becoming much cheaper and was an easy way of gaining performance and fault tolerance using RAID-5 and RAID-10 for the data files on physical disks. A separate RAID-1 Mirrored volume was recommended for the Transaction Log to assist in guaranteed recovery. (Of course, nowadays our SANs have appropriate RAID systems built-in.)
The complexity of administering Filegroups combined with the removal of the 2GB file size limit using NTFS, led to its decline in favor of hardware RAID. You could get plenty of performance and fault tolerance you would need through the relatively cheap plug-and-play RAID devices on the market. Now we could have a single large Primary data file (.mdf) in a single PRIMARY Filegroup and still get good performance using hardware RAID.
That all changed in SQL Server 2005 with Microsoft introducing new features like Data Partitioning, Online Restores, Partial Backups and Piecemeal Restores. All of these features required the multiple Filegroup implementation so we started to reconsider the Filegroup object for large databases again.
The current recommendation for the best performance of your SQL Server database is to combine both multiple Files/Filegroups and hardware RAID together. This will help break up the OS I/O via SQL Server giving better overall performance, as mentioned by Andrew Novick at the PASS Summit in Seattle. This gives performance at both the Logical and Physical Disk system layers.
However, at the end of the day the number of disk drives available will ultimately dictate your throughput. As Andrew put it, “Little has changed in 50 years”.
Later
Brian
Recent Posts
Big Data – Terabytes in SQL Server
Accumulation or Regression in SQL Server?
SQLCAT – awesome resource!
Microsoft re-open the loop hole – thankfully!
