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