Recommendations for SQL Server File Group Creation

· The primary file group must be totally separate and should be left to have only system objects and no user defined object must be created on this primary file group.
· The primary file group should not be set as default file group.
· Separating the system objects from other user objects will increase performance and enhance ability to access tables in the case of serious data failures
· If there are more than one physical drive available in the system then try to create as many physical files per file group and put one file per disk. This will improve performance because whenever a table is accessed sequentially in SQL Server, a separate thread is created for each file to read the table’s data in parallel.
· A separate file group can be created for indexes and the number of physical files can be created as mentioned in the above point.
· Creating a table in one file group and place the text, next and image columns in a different file group on different physical disks.
· The log file must be placed on different physical disk then the data files, because the logging is more write intensive, it is important to have the log on the disk that have good I/O performance. The log file should be RAID 0 or 1 which supports write intensive operation.