Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Robin Schumacher

Using Filegroups

SQL Server filegroups allows the DBA to intelligently spread the database among the various disks that exist on the server or SAN. Transaction logs are different in that they do not participate in user defined filegroups; they have their own set of files.

Although DBAs most likely already know how filegroups work, they may wonder if there are any best practice methods that can be employed through filegroups to enhance performance. While every situation is different, there are some rules of thumb that can be utilized when building a database.

When first creating a database, it is good to put all system objects on the primary filegroup and separate those from the actual user-defined objects. Putting transaction log files on a separate volume helps lessen the I/O burden on SQL Server, especially if the database will be very write intensive.

Then, create one or more filegroups that exist on volumes that are separate from the initial primary and transaction log files. These filegroups will hold the custom database objects. The tables and indexes across separate physical volumes should be further segmented by creating filegroups that hold only tables and indexes.

Tables can be broken down even further by creating filegroups that hold only parts of a table. All versions of SQL Server allow the DBA to place a table’s TEXTor IMAGE data on a separate filegroup. Starting in SQL Server 2005, a table can be partitioned across filegroups using the new table partitioning feature. This feature will be covered in more detail later.

If the existing databases are not physically arranged the way desired, the DBA should not despair. New filegroups can be created and objects recreated/rebuilt into those filegroups via standard SQL scripting or through the use of third party SQL Server tools, some of which allow objects to be easily moved between

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher  


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter