How many File Groups?

It isn’t abnormal in the line of business that I am in to run across a past vendor who has set up a SQL Database, train the users on the application, then depart off to the wild blue yonder leaving the DB’s in the hands of the company’s DBA team on site. Not long ago this very same scenario came about, once the vendor had departed I was curious as to what they had done; and since now I’m responsible for the maintenance, issues, etc I figured it would behoove me to check it out.

I found a 3 GB Database which is cool; checking out the file groups I discovered over 300 .MDF files had been created. My initial gut instinct was one of why, but as I got into further research I can see where they were going with this. The sliding window approach was utilized by this vendor because of two reason upon me asking them…….one they had one architect and that is what they used; and two that is how their app interacts with the product on the back end. I figured given their answer a more prudent search was merited for my own purposes. I was intrigued……

In researching I came up with a few reasons that multiple data files would be utilized

1. Performance increases
2. Parallelism
3. Rolling off extinct data

Now, I’m sure that there are more than just three gains that can be brought up; however in my case these were the ones that stood out.

I can see that if tables/indexes were placed onto separate file groups that it would allow for better performance from a complex queries standpoint in that SQL Server can utilize the “in” parallel I/O operations. I can also see that if the app is doing obsolete removal of data that separate partitioning can be utilized pretty heavily.

All in all I found that that their are some perks to having multiple File Groups. I’m not totally sold on having over 300 data files for a 3 GB Database; however I do see some valid points on to use this type of methodology against a database that is very large along with complex queries hitting the database.

I think it goes without question that thorough testing should be completed when using multiple file groups. Just because it works well in one environment does not mean it will work as well in the other, and I also want to STRONGLY recommend that you don’t decide to make this jump in a prod environment. I would hope that is given but always like to re-iterate. Some formal testing in my opinion should occur first.