Tuesday, November 01, 2005
Set up file locations for optimal SQL performance
When installing SQL Server, you can specify the default drive and directory for each file type, but suppose the installer doesn't. See how easy it is to fix this problem.
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.
2.) Keep only the master database on the default drive/directory.
3.) 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.
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.
2.) Keep only the master database on the default drive/directory.
3.) 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.
# posted by One Level : Tuesday, November 01, 2005
