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