Benefit of filegroups and setting filegroups to read-only

Question

Can someone cite me a real world scenario on when changing multiple filegroups to read-only is a good option and when to use them? What benefits are there if you set it to read-only?

On a database with multiple filegroups, do you have to do backups for the whole database and also backup each of the files of that filegroup? Can you also give me an example of when filegroup backups will be used? I don't see why it would be beneficial to backup a filegroup when you can just backup the whole database. Hoping I could get a real world experience where this filegroup backup would be ideal

Answer 1

Your question is a bit wide-ranging, however some of the main points include the following.

Multiple file groups, in general:

• can be a performance improvement, assuming they are physically isolated. Typically, you might create one filegroup for clustered indexes, one filegroup for non-clustered indexes, and one filegroup for heaps. Or you might create multiple filegroups in a partitioning situation.

• can improve recoverability by enabling piece-meal restore. Piece-meal restore can be used to bring critical production data online quickly while allowing the restore of archival data to not impact online activities. Be aware online piece-meal restore is an "Enterprise-only" option. I've written a blog post showing how to do piece-meal restores over at SQLServerScience.com

• do not need to be backed-up individually, however they can be backed up individually, which might be necessary for VLDBs. Note, when backing up individual filegroups you must also backup the primary filegroup.

Read-only file groups:

• Can be used to reduce contention for reporting-only data.

• Can be used, obviously, to prevent modifications to data.

• Do not need to have the recovery process ran when the database is brought online. This can substantially improve recovery time for VLDBs.

• Also, while read-write filegroups cannot be used with NTFS filesystem-based compression, read-only filegroups can. The implication here is that for a very limited set of systems you may be able to see a slight performance improvement for VLDB data that resides on slower disks by compressing the readonly filegroup files on disk. This would of course need to be evaluated on a case-by-case basis vs the CPU overhead necessary to decompress the NTFS clusters as they are read off the disk.

Myths surrounding read-only filegroups:

• "Statistics will never be updated for tables stored on a read-only filegroup." This is totally incorrect, and can be proven by creating a test-bed as shown in my blog post on SQLServerScience.com. Thanks are due to @PaulWhite for pointing this out to me.

• "No locking occurs in read-only databases. This can cause faster query performance". This is incorrect unless the entire database is read only. A read-only filegroup alone does not allow shared locks to be skipped.