In SQL Server, when should you divide your PRIMARY Data FileGroup right into second information files?

Our data source presently just has one FileGroup, PRIMARY, which has about 8GB of information (table rows, indexes, full-text magazine).

When is a great time to divide this right into second information files? What are some standards that I should recognize?

2019-05-04 12:32:08
Source Share
Answers: 2

There's 2 components to this inquiry : when to add a new FILEGROUP, and also when to add a new FILE in a filegroup. First allowed's talk concept :

Mark's appropriate concerning the key factor being efficiency.

The second factor is calamity recuperation. With SQL Server 2005 and also more recent, you can do filegroup recovers. When calamity strikes, you can recover simply your key filegroup first and also bring the data source partly on-line for questions. Customers can run questions while you're recovering various other filegroups. This serves for data sources with a huge quantity of historic information that might not be called for today, or information stockrooms that require to load information right into existing tables without requiring historic information for accessibility.

An additional factor is the read/write account of groups of information. If you have some information that is frequently contacted, and also various other information that obtains hefty read task, you can construct various sorts of storage space to suit those demands. You can place the hefty - write things on raid 10, and also leave the read - prejudiced things on less costly raid 5.

Currently, allow's chat files versus filegroups. When you position things in SQL Server, you need to position them at the filegroup degree. You can land a table or an index in a filegroup, yet you can not select a details documents. So every little thing we've reviewed until now has had to do with when to add a filegroup - yet when do you add a documents?

If you're making storage space, and also you have 80 disk drives, there's a couple of means you can damage it up :

  • One swimming pool of 80 drives
  • Two swimming pools of 40 drives
  • Four swimming pools of 20 drives, and so on.

Various storage space subsystems have various efficiency accounts. I've collaborated with some SANs that executed ideal with 12 - 16 drive selections, and also anything bigger than that really did not have an efficiency renovation. An additional instance is SANs with multipathing : if you have numerous HBAs attaching your server to your storage space, and also if your multipathing software program isn't actual active/active, after that you might require one array per course in order to disperse load. 4 courses, 4 swimming pools of drives will certainly improve efficiency on those sorts of drives.

In those instances, you wind up with 4 various selections, 4 various drives under Windows (unless you make use of place factors, and also also after that it's various folders) and also you'll require 4 different files in SQL Server. Those different files can be in the very same filegroup.

2019-05-08 23:01:54

The key factor is efficiency. When you lack IOPS ability on your key filegroup hard disk drive, you'll require to expand onto a 2nd filegroup to divide IOPS over numerous disks/LUNs relying on storage space config.

EDIT : Brad Wilson made an excellent comment pertaining to SSDs. If you're making use of a composite SSD/SATA/FC storage space system, you might intend to have various filegroups on various sorts of storage space. You can after that place your severe IOPS need tables onto SSD filegropus, while history/stats tables might be saved on economical SATA filegroups.

2019-05-08 02:21:50