Creating Filegroups in SQL Server

Background

Daily data transaction will eventually anticipate growth of the database and there will be a need to scale to support the large amount of data. When we start we usually start with a very small database, but if we have a look at our capacity plan then we understand the growth which can vary from 2-6 times the current database in next 2 years. In order to solve this problem we have to take certain steps from the planning perspective.

Workaround

There are lot of known features available to support the database growth in SQL Server 2005, 2008. Focusing issues from capacitive planning, then we have one main thing to deal with and i.e “filegroups“. For allocation and database administration purpose database objects and file are group together in filegroups.

As a rule each database has a primary filegroup to support the system and user objects. In order to maintain the performance and needed growth, additional allocation of hard drives can be done to the server. Using new disk drives we can create other filegroups and files. So that we spread the IO operations to these new drives by moving objects to these newly created filegroups.

Creating a filegroup

USE EmployeeDB;
GO
ALTER DATABASE EmployeeDB
ADD FILEGROUP FG_Emp_ReadOnly
GO

Adding files to filegroup

ALTER DATABASE EmployeeDB
ADD FILE
(
NAME = FG_READONLY1,
FILENAME = 'C:EmpDB_RO.ndf',
SIZE = 10MB,
MAXSIZE = 150MB,
FILEGROWTH = 10MB
) TO FILEGROUP FG_EMP_READONLY;
GO

Creating Objects in new filegroup

-- New Table
CREATE TABLE dbo.EmpDetail
(
EmpID int NOT NULL,
ProductID int NOT NULL,
CustomerID int NOT NULL,
Age int NOT NULL
)
ON FG_EMP_READONLY -- Index
CREATE INDEX IDX_EmpID ON dbo.EmpDetail(EmpID) ON FG_EMP_READONLY
GO

Moving an object from primary file group to another file group

In order to move an existing table with a clustered index, issue the following command:

-- Table - The base table is stored with the
-- clustered index, so moving the clustered
-- index moves the base table
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.EmpDetail(ProductID)
ON FG_Emp_ReadOnly
GO

And in order to move a non-clustered index, issue the following command:

-- Non-clustered index
CREATE INDEX IDX_EmpID ON dbo.EmpDetail(EmpID)
WITH (DROP_EXISTING = ON)
ON FG_Emp_ReadOnly
GO

It might be possible that there is table does not contain any clustered index and we still want to move, then we can create the clustered index on the table by specifying the new file group. After doing all this, it will move the base table and clustered index to the new file group. Finally we can drop the clustered index. We can use the following commands:

-- Table without a clustered index + drop index
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.EmpDetail(ProductID)
ON FG_Emp_ReadOnly
GO
DROP INDEX IDX_ProductID ON dbo.EmpDetail(ProductID)
GO

Determining the objects which exist in a particular filegroup

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = 2 --* New FileGroup *
GO

Possible filegroup configurations

As we say that sky is a limit and our application (nothing but software) and hardware specifications drives our most decisions, while designing a filegroup we can consider following options:

  • Option 1
    • Data filegroup
    • Index filegroup
  • Option 2
    • Read only tables filegroup
    • Read-write tables filegroup
    • Index filegroup
  • Option 3
    • Read only tables filegroup
    • Read-write tables filegroup
    • Index filegroug
    • Key table 1 filegroup
    • Key table 2 filegroup
    • Key table 3 filegroup

References

Files and Filegroups Architecture

http://msdn.microsoft.com/en-us/library/ms179316.aspx

SQL Server Best Practices: Setting a Default Filegroup

http://blogs.msdn.com/b/buckwoody/archive/2009/05/21/sql-server-best-practices-setting-a-default-filegroup.aspx

Tagged with: ,