Why we use Filegroups? What are the benefits?

Question

Why we use Filegroups? What are the benefits?

Rabia Kurnaz 386

What are File Groups? Can you give an example of why we use it?

0 comments

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Hi @Rabia Kurnaz ,

What is Filegroup?

In a database, a filegroup is a rational approach for grouping objects. Filegroups should not be confused with actual files (.mdf, .ddf, .ndf, .ldf, etc.). Per database, you can even have multiple filegroups.

When to Create Filegroup? Or benefits of creating filegroup.

• SQL Server accesses data using threads; each thread is responsible for retrieving or updating data on specific pages at specific locations on discs; if you have multiple filegroups and data is spread across the disc, SQL Server can take advantage of parallel threads, which improves database performance.
• If you’ve had a join operation that involves multiple tables in a database, putting them all in one filegroup will keep SQL Server running in parallel (for the most part); however, putting the tables in different filegroups and placing them on different discs or luns will increase operational efficiency against strongly accessed tables in different file groups because SQL Server can use parallel threads.
• While preparing or updating the database, your database performs poorly. Multiple threads can work together to retrieve data from different file groups at the same time.
• You have quite a table with several years of data, but you are only using a few recent years of data; that’s very helpful when partitioning the table and indexes. It also makes it easier to archive data and avoids unnecessary scanning of records that are not currently important.
• You have a large database, and full or disparity backup recovery times are unacceptable; if you discover database corruption of objects related to a specific filegroup, users could really restore just that file group to restore files quickly.

Refer to this blog to get more information.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

tibor_karaszi@hotmail.com 4,316 Reputation points

2022-08-10T10:04:03.083+00:00

I would be careful with connecting usage of threads to filegroups. The connection isn't there, since it is the reading connection/request that does the I/O operation. I.e., there isn't a connection between files and I/O threads (except for backup). Check out this, for example: https://www.sqlskills.com/blogs/paul/the-curious-case-of-does-sql-server-use-a-readwrite-thread-per-lun/
Paul Arscott 0 Reputation points

2023-12-29T16:21:45.6533333+00:00

These points aren't applicable with today's fast I/O and filegroups can be more hassle than anything else.
Dan Guzman 9,516 Reputation points

2023-12-29T17:15:05.9966667+00:00

Once use case for multiple filegroups is to facilitate piecemeal restores, allowing more critical data to be restored and the database brought online while less critical data is restored. I agree with @Paul Arscott that filegroups are typically not needed on modern storage subsystems for performance.

Answer 2

I would say that the reasons that sreejukg give for filegroups are mainly a thing of the past. Often all disks are on the same SAN anyway.

There can still be reason to use filegroups, though. Some people prefer to reserve the primary filegroup for the system tables and keep it small.

You can also use filegroups to improve your restore times. That is, if you put less interesting archive tables on a different filegroup, you can opt to restore the database without that filegroup, or save that for later. This shortens the restore time for the important data.

Answer 3

In general SQL Server database will have atleast two files, primary data file (.mdf) and log file (.ldf). The data file contains your tables, views, stored procedure etc. In addition to the data file and log file, you can have secondary data file (.ndf) files.

Imagine your server has 6 disks, instead of using one disk to store your database, where you will have to read and write to the same disk, for a high transaction database, it will have I/O issues. To solve this, you can have your database in to multiple files (where files represent the physical location where the data is stored) and file groups (that is a logical grouping of files. The files can be in diffrent disks thus your database will make the best of of your disks.

Read more below.
https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver16

Hope this helps

Share via

Why we use Filegroups? What are the benefits?

2 additional answers

Your answer