Database Reference
In-Depth Information
You can also do this programmatically, as follows:
ALTER DATABASE AdventureWorks2012 ADD FILEGROUP Indexes;
ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = AdventureWorks2012_Data2,
FILENAME = 'S:\DATA\AdventureWorks2012_2.ndf',
SIZE = 1mb,
FILEGROWTH = 10%) TO FILEGROUP Indexes;
By separating tables that are frequently joined into separate filegroups and then putting files within the filegroups
on separate disks or LUNS, the separated I/O paths can result in improved performance. For example, consider the
following query:
SELECT jc.JobCandidateID,
e.ModifiedDate
FROM HumanResources.JobCandidate AS jc
INNER JOIN HumanResources.Employee AS e
ON jc.BusinessEntityID = e.BusinessEntityID;
If the tables
HumanResources.JobCandidate
and
Person.BusinessEntity
are placed in separate filegroups
containing one file each, the disks can be read from multiple I/O paths, increasing performance.
It is recommended for performance and recovery purposes that, if multiple filegroups are to be used, the primary
filegroup should be used only for system objects and secondary filegroups should be used only for user objects. This
approach improves the ability to recover from corruption. The recoverability of a database is higher if the primary
data file and the log files are intact. Use the primary filegroup for system objects only, and store all user-related objects
on one or more secondary filegroups.
Spreading a database into multiple files, even on the same drive, makes it easy to move the database
files onto separate drives, making future disk upgrades easier. For example, to move a user database file
(
AdventureWorks2012_2.ndf
) to a new disk subsystem (F:), you can follow these steps:
1.
Detach the user database as follows:
USE master;
GO
EXEC sp_detach_db 'AdventureWorks2012';
GO
Copy the data file
AdventureWorks2012_2.ndf
to a folder
F:\Data\
on the new disk
subsystem.
2.
3.
Reattach the user database by referring files at appropriate locations, as shown here:
USE master;
GO
sp_attach_db 'AdventureWorks2012R2'
, 'R:\DATA\AdventureWorks2012.mdf'
, 'F:\DATA\AdventureWorks2012_2.ndf'
, 'S:\LOG\AdventureWorks2012.1df ';
GO
