Solved

What is the purpose of filegroups in SQL Server?

Posted on 2009-04-07
6
441 Views
Last Modified: 2012-05-06
Hi all,

I'm just wondering what the purpose of Filegroups is in SQL Server. What do they do? How do they work?

Thanks
0
Comment
Question by:Liam_H
6 Comments
 
LVL 12

Assisted Solution

by:udayakumarlm
udayakumarlm earned 62 total points
Comment Utility
filegroups provide an opportunity for fine-tuning performance by allowing you to move specific tables and indexes from one physical drive array to another

read more at
http://www.sql-server-performance.com/tips/filegroups_p1.aspx
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Database objects and files can be grouped together in filegroups for allocation and administration purposes.

http://msdn.microsoft.com/en-us/library/ms179316(SQL.90).aspx
0
 

Author Comment

by:Liam_H
Comment Utility
So if I understand this correctly, it's a way to group tables together - that work together, and can potentially speed up and increase the performance of the database? So you would have tables in a filegroup that have a lot of interaction with each other, and are generally large in size?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 12

Expert Comment

by:udayakumarlm
Comment Utility
yes
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 63 total points
Comment Utility
Yes. And its a best practice to

1. Keep MDF files and LDF files in different partition to increase the performance of the Server.
2. Its advisable to have NDF files ( Secondary Files) to improve the Performance of the Primary File further.

having filegroups as mentioned above will improve the IO speed for reading and writing data into SQL Server.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>So if I understand this correctly, it's a way to group tables together - that work together, and can potentially speed up and increase the performance of the database?
actually, it's just the other way round.
tables (and indexes) that are used together should go into different filegroups, so that the I/O for the same action/query can be effectively shared among distinct files, aka drives.

putting those tables "together" will not give you any advantage.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled taskā€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now