Filegroups in SQL Server 2005
Written By: Jeremy Kadlec -- 11/16/2006
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
- Take some time to analyze your environment to determine if filegroups will assist with your SQL Server growth, usage and performance needs.
- With the level of flexibility provided by filegroups, consider the options available with your hardware and application to improve the overall user experience.
- If you find the need for filegroups, build the code and test the process in a test environment before you implement the changes in production.
- Be sure to perform these processes during maintenance windows for SQL Server because these processes can be very resource intensive and time consuming.
- Build a performance baseline prior to the creation of the filegroups. Implement the filegroup changes and then compare the results to validate the performance improvements.
- When creating new objects, be sure that the objects are created in the correct filegroup to ensure expected performance.
- On a regular basis validate the database objects are in the correct filegroups and correct as needed.
- Check out these related MSSQLTips:
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
