Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 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

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

 

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