By default, SQL Server locates all its files on
the same drive as the server. In addition, it places the log file
for each database on the same drive. All that disk access can cost
you a significant amount of performance. When installing SQL
Server, you can specify the default drive and directory for each
file type, but suppose the installer doesn’t.

Fixing this problem is easy. When creating a
new database, reject the defaults and specify the locations for
each file, as follows:

  1. Place the data file on a different drive from
    the one that houses SQL Server. Keep only the master database on
    the default drive/directory.
  2. Place the log file on a third drive.

This assumes that you have at least three
drives available on your server, which may not be possible.
However, at the current cost of disk drives, this upgrade will not
be expensive. If your budget won’t allow you to add new drives, you
could partition the drive. This will increase performance somewhat,
but not as much as truly separate drives.

For an existing database, perhaps the simplest
way to rearrange its layout is to back it up and then restore it,
refusing the default options and specifying your preferred
locations.

In Enterprise Manager, select the database of
interest and then back it up. Next, restore it. On the Restore
Database dialog box, click the Options tab, which specifies the
actual locations of the data and log files. Edit the current
values, placing the data on one drive and the log on another, and
then restore the database. Your users should experience an
immediate performance gain.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!