Introducing Arcserve Backup Agents and Options › Using Agents › Agent for Microsoft SQL Server › Backup and Recovery Best Practices › Microsoft SQL Server Database Basics › Performance Improvement Tips

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.