By: Atif Shehzad | Updated: 2012-04-06 | Comments (2) | Related: More > Database Administration
SQL Server allows you to create additional filegroups to spread storage of data and indexes to different disks to improve I/O performance. The issue is that when creating new data storage objects we are required to explicitly point to the new filegroup. Is there any option to use the new filegroup by default without explicitly specifying the filegroup when creating new tables and indexes?
Before we get started, let me explain some terminology:
- Database - is made up of multiple files for database objects and transaction logs
- File - a physical file that is used to store database objects or transaction logs
- Filegroup - this is a container that is made up of one or more files to store database objects
- PRIMARY filegroup - this is the default filegroup that is created when you create a new database.
- Default filegroup - when creating new tables and indexes this specifies which filegroup is the default if a filegroup is not specified
So based on this when creating a new database, the primary file group of a database is the default filegroup and the primary data file is created in it. When a table or index is created without specifying a filegroup, all pages will be allocated from the default filegroup. Note: only one filegroup at a time can be the default filegroup.
By default, the primary filegroup is the default filegroup, so all new objects are placed in this filegroup. However we can change the default filegroup to any other filegroup at any time. When you have multiple filegroups, changing the default filegroup from PRIMARY to another one is a good idea, because it eliminates the need to specify the filegroup when objects are created and makes sure that new objects are created in the correct filegroup.
Let's analyze the problem for which we are required to change the default filegroup from primary to something else.
The following script creates a database and then adds additional filegroups. Although additional filegroups can be created when creating a database, I added the filegroups later for simplicity and illustration. So in this case, the primary filegroup will be the default filegroup.
-- Script 1: Create DB and add additional file group -- If DB pre exists then drop it IF exists (SELECT * FROM sys.databases WHERE NAME = 'MSSQLTip') USE MASTER DROP DATABASE MSSQLTip GO -- Create new DB CREATE DATABASE MSSQLTip GO -- Add file groups to DB ALTER DATABASE MSSQLTip ADD FILEGROUP FG1 ALTER DATABASE MSSQLTip ADD FILEGROUP FG2 GO -- Verify file groups in DB USE MSSQLTip GO SELECT groupName AS FileGroupName FROM sysfilegroups GO
At this point we have three file groups primary, FG1 and FG2 in our new database. Any new table or index will be created in the primary filegroup which is the default unless we specify the filegroup to use. If we want to create a data storage object in FG1 or FG2 then the only way is to specify the filegroup when creating the storage object.
Consider the following scenario where we want to create a table without specifying the filegroup. It is essential to provide explicit instructions for creating the data storage objects in other than the default filegroup. The only way to ensure the creation of new data storage objects on FG1 or FG2 without any additional consideration is to set the required filegroup as shown below.
-- Script 2: Create tables in file groups -- tbl1 would be created on primary file group CREATE Table tbl1 (ID int identity(1,1)) GO -- tbl2 would be created on FG1 CREATE Table tbl2 (ID int identity(1,1), fname varchar(20)) ON FG1 GO -- Verify file group of tbl1 sp_help tbl1 GO -- Verify file group of tbl2 sp_help tbl2 GO
Now that we created the table on the new filegroup we can try to insert data as follows:
INSERT INTO tbl2 (fname) values ('Atif') GO
Since we did not specify a file for this filegroup we get this error message:
The filegroup "FG1" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.
Another issue we have since we do not have a file associated with this filegroup is if we try to change FG1 to be the default filegroup as shown below:
ALTER DATABASE MSSQLTip MODIFY FILEGROUP FG1 DEFAULT GO
We will get this error message, because a file group cannot be set as the default unless it has at least one data file.
Cannot change the properties of empty filegroup 'FG1'. The filegroup must contain at least one file.
Creating the Data File
A data file may be placed in a single filegroup and cannot be shared among filegroups. Files may be placed in the filegroup at DB creation time or at a later time. In the following script we create secondary data files for each filegroup (FG1 and FG2). (Note: if you do this on your server you will need specify the correct path for the file creation.)
-- Script 3: Add data files to file groups -- Add data file to FG1 ALTER DATABASE MSSQLTip ADD FILE (NAME = MSSQLTip_1,FILENAME = 'F:\DBs\2K5\MSSQLTip_1.ndf') TO FILEGROUP FG1 GO -- Add data file to FG2 ALTER DATABASE MSSQLTip ADD FILE (NAME = MSSQLTip_2,FILENAME = 'F:\DBs\2K5\MSSQLTip_2.ndf') TO FILEGROUP FG2 GO -- Verify files in file groups USE MSSQLTip GO sp_helpfile GO
Setting New Default Filegroup
After creating the new files for filegroups FG1 and FG2, we can change the default filegroup and verify the new default by running the code below.
--Script 4: Set FG1 as default file group -- Set FG1 as default file group ALTER DATABASE MSSQLTip MODIFY FILEGROUP FG1 DEFAULT GO -- Create a table without specifying file group Create table table3 (ID TINYINT) GO --Verify the file group of table3 is FG1 sp_help table3 GO -- insert some data to make sure no errors insert into table3 values (10) GO
Using sp_help shows that table3 is created in FG1 even without specifying FG1 when creating the table.
Now let's set FG2 as the default filegroup using SSMS. Right click on the MSSQLTip database and click on Properties. Go to the filegroups page and set FG2 as the default as shown below.
Checking the Default Filegroup
In addition to using SSMS to find what filegroup is the default, we can also check the default filegroup by querying sysfilegroups as shown below.
--Script 5: verify default filegroup USE MSSQLTip GO SELECT groupname AS DefaultFileGroup FROM sysfilegroups WHERE convert(bit, (status & 0x10)) = 1 GO
Setting the default filegroup does not stop you from using other filegroups it just means that you will need to specify the correct filegroup when creating a table or index otherwise it will be created on whatever filegroup is set as the default.
- Members of db_owner fixed database role can change the default filegroup.
- If used effectively, utilizing multiple filegroups and files you can improve performance by spreading the I/O load. Analyze your current and new databases to take benefit of having multiple filegroups. Read this tip for more information.
- You can also move storage objects from one filegroup to another. Read this tip for more information.
- Click here to read more about database file and filegroup architecture
Last Updated: 2012-04-06
About the author
View all my tips