Using Files and Filegroups

Question

I am updating a database to use multiple filegroups. As it stands, it uses just the PRIMARY filegroup.

I have read that it is recommended to create a second Filegroup (called 'Data') and mark that one as the default. Will all database objects (tables, indexes, etc) automatically switch to this new one?

Secondly, does it matter how many files are created for each filegroup? Should I create more than a single file for each filegroup or will this make any difference performance wise?

Thanks!

Answer 1

For me, this is a question of simplicity-of-design vs performance-and-scalability.

If I am designing a non-critical database that won't see a huge amount of traffic, then I typically would go with a single file per database since it is easy to configure and maintain.

However, if your recovery point objective is to be able to recover the database to within several minutes of any failure, and your recovery time objectives are to be able to recover within as short a time as possible, I would carefully plan the filegroups and associated files as:

1. The main filegroup should only contain the system tables. This filegroup can then be very quickly restored or replaced if necessary without affecting business data.
2. A primary filegroup to store business critical data that can be restored quickly enough to meet the recovery time objective and get the business back up-and-running ASAP. I would create as many files for this filegroup as I have separate and distinct drive subsystems on high-speed devices. For instance, if I had 3 separate SSD raid arrays, I might create 3 files to spread the load across the arrays evenly.
3. A secondary filegroup to store historical yet business relevant data that is not critical to get the business up-and-running and yet still needs to be in the database for some reason. Typically this would be on one or more files, depending on performance and cost requirements.

If there is a requirement to store many file-based pieces of data, for instance images or documents, with relationships to data stored in SQL Server, I would use FILESTREAM, which requires its own filegroup.

Answer 2

"Will all database objects (tables, indexes, etc) automatically switch to this new one?"

No. Only new objects will be created there. Alternatively, you can rebuild the existing objects and specify the new filegroup to move them but this won't move LOB pages.

Does it matter how many files are created for each filegroup?

The more files per filegroup, the slower the performance. (Because SQL Server needs to read from more files -> more operations (more CPU consumption), it also tends to shorten I/O length -> worse performance). The only reason to create more files per filegroup are

• to address management page contention (e.g., SGAM contention) (but even management page contention can be resolved in different ways and adding more files is the last option to consider)
• ability to create big enough DB because a single file can't be more than 16 TB. However; 16 TB file is usually too big and causes performance issues during maintenance (restore); therefore, smaller files are created but not too small
• spread files across disks but useful to consider is to group disks to RAID to balance number of files that need to be created