Microsoft SQL Server Database Basics
Appendix C: Backup and Recovery Best Practices 111
Performance Improvement Tips
Consider the physical layout of your database to take advantage of the way files
and FileGroups and partial backups/restores can optimize your database
performance.
■
If you separate tables with high traffic estimates from tables with less
expected traffic by placing them into different FileGroups, you can store the
high-traffic table on high-performance disks and store the other files in
another FileGroup on different disks. Queries against the a table and
FileGroup backup jobs can then access your fastest devices.
■
Create Files and FileGroups on as many separate devices as possible so that
queries against their tables can be processed by multiple read/write devices.
■
Put different tables used in the same queries in different FileGroups to enable
parallel data searches.
■
Put the transaction log file on a disk that does not also contain data files.
■
If you are using Microsoft SQL Server 2005 or later, you can partition tables
across multiple FileGroups to speed query access times (queries scan part of
the data instead of all of it) and simplify tasks like rebuilding indexes.
Consider horizontal or vertical partitions. See the Microsoft SQL Server
documentation for more details.
■
Consider data stability to help you allocate files and FileGroups. For example,
data that is static but needed for historical purposes can be assigned to a
Read-only FileGroup. With SQL 2005 and later, use the Partial Database
subset option to exclude Read-only FileGroups from your backup plan,
improving backup time.
■
You can individually restore the files in a database in which multiple
FileGroups are used. If a database is allocated on several devices and a disk
fails, only the file on the failed disk must be restored.
■
Put the files expected to grow quickly in different FileGroups on separate
drives.
■
As files become full, you can add files and disks to existing FileGroups,
allowing SQL Server to pass data to the new file.
