Set file locations to optimize performance

from the Artful SQL Server & Access Tips List

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 slow down 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.

Last updated 4 May 2021

Return to the Artful SQL Server & Access Tips page