SQL Server Best Practices: Setting a Default Filegroup

SQL Server Best Practices: Setting a Default Filegroup

Rate This
  • Comments 8

When you create a database, you should always create Filegroups to store the data on multiple drives. Don't confuse this with just creating multiple files in a database - that is useful, but not the same. Let's take an example to explain what you want to accomplish.

First, you can create a database with just two files: one for data, and another for the log. If you type CREATE DATABASE Testing with nothing else, you'll get a database called Testing that has two files in it. Whether you know it or not, you'll also get a Filegroup called PRIMARY. A Filegroup is simply a logical name for a "container" of those database files.

You can also use the CREATE DATABASE statement to create multiple files. If you don't specify otherwise, they will be placed in the PRIMARY Filegroup, and they fill up at the same rate automatically. Think of a group of glasses as the files. When you pour water to fill them, the system acts like a tube, filling up all of the glasses at the same time. If you place your files on different physical drives, you effectively separate the I/O processing, making the system perform better. But there's a way to optimize this even better.

If you create a new Filegroup and place files there, you get that same benefit, but you also gain the ability to work with it separately, such as running maintenance on it, and even backing up and restoring it separately.

So the best practice is to create at least one new Filegroup and then taking one extra step - designating it as the Default. You see, when you create a Fielgroup, it isn't used by SQL Server automatically. When you create an object, like a table or an index, you have to specify which Filegroup it should be placed on. If you don't, it goes to the "Default" Filegroup, which is that PRIMARY Filegroup. This is also the Filegroup that holds your system objects. So when you create another Filegroup, take the time to make one of them the new Default using the ALTER DATABASE statement.

Here's a VERY simple example that shows you what I have been talking about. As always, this has to run on a test system, and should never be done in production. You'll need a C:\TEMP directory for it to work.

For the best performance, you would have more than just two files, and you would also place them on different physical drives. This will just show you how the process works. Run this one segment at a time - each comment represents a block.

USE

master;

GO

/* Create the Database - note the files and file paths */

CREATE

DATABASE TestingBuckWoody

ON

PRIMARY

(

NAME = TestingBuckWoody1_dat,

FILENAME = 'c:\temp\TestingBuckWoody1.mdf'),

FILEGROUP TestingBuckWoodyFileGroup2

(

NAME = TestingBuckWoody2_dat,

FILENAME = 'c:\temp\TestingBuckWoody2.mdf')

LOG

ON

(

NAME = TestingBuckWoody_log,

FILENAME = 'c:\temp\TestingBuckWoody.ldf');

GO

USE

TestingBuckWoody;

GO

/* Create a table, and don't specify where it goes */

CREATE

TABLE T1 (c1 int);

GO

/* Shows the filegroup */

EXEC

sp_help T1;

GO

/* Create a table, and specify where it goes */

CREATE

TABLE T2 (c1 int)

ON

TestingBuckWoodyFileGroup2;

GO

/* Shows the filegroup */

EXEC

sp_help T2;

GO

/* Now change the Default */

ALTER

DATABASE TestingBuckWoody

MODIFY

FILEGROUP TestingBuckWoodyFileGroup2 DEFAULT;

GO

/* Create a table, and don't specify where it goes */

CREATE

TABLE T3 (c1 int);

GO

/* Shows the filegroup */

EXEC

sp_help T3;

GO

/* Just show Filegroup info */

EXEC

sp_helpfilegroup TestingBuckWoodyFileGroup2;

GO

EXEC

sp_helpdb TestingBuckWoody;

GO

/* Clean up - make sure you are on a test system! */

USE

master;

GO

DROP

DATABASE TestingBuckWoody;

GO

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post
  • PingBack from http://microsoft-sharepoint.simplynetdev.com/sql-server-best-practices-setting-a-default-filegroup/

  • Buck, could you clarify 'This is also the Filegroup that holds your master, msdb, tempdb and other objects.'? I have a feeling that this is the crux of your post, but when I read it, i infer that ANY filegroup named PRIMARY is shared, which I didn't expect.

  • Buck may have post-TechEd flu.  The PRIMARY filegroup does *not* hold master, model, and msdb.  Those are system databases, and they contain PRIMARY filegroups of their own.

    I think he meant to say that the PRIMARY filegroup  holds the database metadata, aka system tables.  They are not accessible normally.  I agree with the recommendation to make another, non-PRIMARY filegroup become the default, however.

  • Mark, you are so correct. My original post had "master model and msdb" - but what I meant to say was "system tables". The system databases are of course, well, databases, so they have their own filegroups!

  • I don't understand why you describe an additional step is needed. The filegroup statement on the CREATE DATABASE command allows you to specify DEFAULT. What is accomplished by doing this in a seperate step?

    Tom

  • Tom - you are correct. I always do it in a separate step so that I can pull it out for an ALTER statement on an existing system.

  • Hi Buck, is possible define a default filegroup in the server level?

    My idea is define a default filegroup before create to any database.

    Thanks,

  • Gustavo - Filegroups are at the database level.

Page 1 of 1 (8 items)