Filegroups in SQL Server 2005

Problem

With the anticipated growth of my database what native features does SQL Server 2005 have to help me scale to support a large amount of data?  I have a relatively small database today, but based on my capacity planning, I anticipate that the growth will be 3 to 7 times my current database in the next 18 months.  What steps can I take from a planning perspective to ease this growth?

Solution

Many native SQL Server 2005 options exist to help support database growth.  From a planning perspective, one of the first topics that should be addressed is filegroups.  A filegroup can be considered a logical storage unit to house database objects that maps to a file system file or multiple files. 

By default each database has a primary filegroup to support the system and user objects.  To help support the needed growth and assumed performance, additional disk drives can be allocated to the server.  Next, new filegroups and files can be created on the new disk drives.  Then objects can be moved to these filegroups as a means to spread the IO over additional disks.

How can I create a new filegroups?

USE CustomerDB_OLD;
GO
ALTER DATABASE CustomerDB_OLD
ADD FILEGROUP FG_ReadOnly
GO

How can I add files to a filegroup?

ALTER DATABASE CustomerDB_OLD
ADD FILE 
( 
   NAME = FG_READONLY1,
   FILENAME = 'C:\CustDB_RO.ndf',
   SIZE = 5MB,
   MAXSIZE = 100MB,
   FILEGROWTH = 5MB
) TO FILEGROUP FG_READONLY;
GO

How can I create objects in the new filegroup?

-- Table
CREATE TABLE dbo.OrdersDetail
(
   OrderID int NOT NULL,
   ProductID int NOT NULL,
   CustomerID int NOT NULL, 
   UnitPrice money NOT NULL,
   OrderQty smallint NOT NULL
)
ON FG_READONLY 
-- Index
CREATE INDEX IDX_OrderID 
ON dbo.OrdersDetail(OrderID) ON FG_READONLY
GO

How can I move an object from the primary file group to another file group?

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.OrdersDetail(ProductID) ON FG_ReadOnly
GO

To move a non-clustered index, issue the following command:

-- Non-clustered index
CREATE INDEX IDX_OrderID ON dbo.OrdersDetail(OrderID) 
WITH (DROP_EXISTING = ON)
ON FG_ReadOnly
GO

If the table does not have a clustered index and needs to be moved, then create the clustered index on the table specifying the new file group. This process will move the base table and clustered index to the new file group. Then the clustered index can be dropped.  Reference these commands:

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

How can I determine which objects 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

What are possible filegroup configurations?

Although the sky is the limit and application as well as hardware availability drive most decisions, below outlines some options that can be considered when designing a filegroup configuration:

  • 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

Do other issues exist that filegroups can solve?

Yes – Based on your application, filegroups can be created to resolve IO performance problems by spreading the database over additional spindles alleviating disk queuing.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *