Menu
I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide....
What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server database (anything larger than Northwind or AdventureWorks) - do you use multiple filegroups? If so: how many? And why?
What are your criteria to decide when to move away from the "one filegroup for everything" approach:
If you use multiple file groups, how many do you use? One for data, one for index, one for log? Several (how many) for data? What are your reasons for your choice - why do you use that exact number of filegroups :-)
875
A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
Splitting a database across multiple filegroups permits you to take advantage of the I/O bandwidth for each physical device / path that you've placed filegroups onto.
To add a filegroup using SSMS, open SSMS and select a database where a file group needs to be created. Right-click the database select “Properties” >> select “Filegroups” and click “Add Filegroup” as shown in the following image: When we click the “Add Filegroup” button, a row will be added in the “Rows” grid.
SQL Server databases have three types of files, as shown in the following table. Contains startup information for the database and points to the other files in the database. Every database has one primary data file.
The Microsoft trained and best practice methodology is as follows:
Keep in mind that an MDF technically works similarly to a hard drive partition when it comes to storing data. The MDF is a randomly read file, whereas the LDF is a sequentially read file. Therefore splitting them into separate drives causes a huge performance gain, unless running solid state drives, in which case the gain is still there.
50
There's at least ONE good reason for having multiple (at least two) file groups in SQL Server 2008 : if you want to use the FILESTREAM feature, you have to have a dedicated and custom filegroup for your FILESTREAM data :-)
Marc
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
