Some definitions:

Primary data file – minimum requirement for a database and has a .mdf extension

Secondary data files – All files except the primary data file , they have a .ndf extension.These are optional

Transaction Log files – Minimum requirement to have a log file . Contains all log files used for recovery and uses a .ldf extension

Filegroups – Files and database objects can be maintained in filegroups . The Primary filegroup maintains all system objects and files that haven’t been assigned to another filegroup.User defined filegroups are files that have been created in the CREATE DATABASE or ALTER DATABASE  using FILEGROUP

 The following example, creates a database, with a new user-defined filegroup called DBsqlserver_filegroup1.

  CREATE DATABASE  DBsqlserver

ON PRIMARY

  ( NAME='DBsqlserver_Primary',

    FILENAME=

       'E:\MSSQLSERVER\MSSQL10_50.MSSQLSERVER\MSSQL\data\DBsqlserver_Prm.mdf',

    SIZE=4MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

FILEGROUP DBsqlserver_filegroup1

  ( NAME = 'DBsqlserver_filegroup1_Dat1',

    FILENAME =

       'F:\MSSQLSERVER\MSSQL10_50.MSSQLSERVER\MSSQL\data\DBsqlserver_filegroup1_1.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

  ( NAME = 'DBsqlserver_filegroup1_Dat2',

    FILENAME =

       'G:\MSSQLSERVER\MSSQL10_50.MSSQLSERVER\MSSQL\data\DBsqlserver_filegroup1_2.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB)

LOG ON

  ( NAME='DBsqlserver_log',

    FILENAME =

       'H:\MSSQLSERVER\MSSQL10_50.MSSQLSERVER\MSSQL\data\DBsqlserver.ldf',

    SIZE=1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB);

GO

--Make DBsqlserver_filegroup1  the default filegroup

ALTER DATABASE DBsqlserver

  MODIFY FILEGROUP DBsqlserver_filegroup1 DEFAULT;

GO

-- Create a table in the user-defined filegroup. DBsqlserver_filegroup1

USE DBsqlserver;

CREATE TABLE Table1

  ( IDcol int PRIMARY KEY,

   somevalue char(8) )

ON DBsqlserver_filegroup1;

GO

 How does organising files and filegroups improve performance?

1)Split files across multiple disks

2)Split files across multiple disk controllers

A server profile may have 4 separate drives . In that scenario – (using the example above) – we could place the 3 data files on 3 drives and the log file on the 4^th. Automatically, this allows 4 read\write processes to manage in parallel.

By allowing a heavily used table to be placed in a specified filegroup – the IO can be isolated on a specific disk. Therefore distributing IO across multiple disks. There are many factors influencing IO performance – such as memory pressure , therefore it is not a guarantee performance will improve by distributing tables. Some end to end analysis may be required – including hardware , indexes, memory

 Another area of performance is backup and restore of files in filegroups. A typical example may be that a full BACKUP of a database is taken once a week – and then the backup of a file is taken during the week – assuming only tables in that files edited. This will improve BACKUP performance.Also useful for moving files around quickly

 It is worth noting – that taking file backups when using Simple Recovery Model – has some restrictions. On the Restore – the filegroup must  be set to read only and a differential read-only file backup.  I’ve found this to be slightly restrictive – it is outside the normal Operational procedure 

Author: Jack Vamvas (http://www.sqlserver-dba.com)