SQL Server 6.5 Optimization Tips

November 28, 2000

Tempdb database

SQL Server 6.5 installation put tempdb database on the master database device. The size of the tempdb database is 2MB by default. Usually, it is not enough. So, you should increase the size of the tempdb database. To increase the size of the tempdb database, you can expand master device and then expand tempdb database, or create new device and then expand tempdb database on this new device. You can also move tempdb database on its own database device to simplify administering and monitoring.

Here is step by step description:

1. Create new database device with the appropriate size.
2. Uncheck "Default device" option for the master database device.
3. Set "Default device" option for the new device.
4. Put tempdb database into RAM from the Enterprise Manager or sp_configure system stored procedure.
5. Stop and start MSSQLServer service.
6. Put tempdb database back on the hard disk from the Enterprise Manager or sp_configure system stored procedure.
7. Stop and start MSSQLServer service.
8. Set whichever device you want to be default.

If you want to increase a speed of your queries (if these queries contain subqueries, or GROUP BY, or ORDER BY clause), you can put tempdb database into RAM.

By the way, tempdb in RAM is no longer supported in SQL Server 7.0.

Using segments

Segment is a named collection of disk pieces. It is a subset of one or more database devices that is available to a particular database. Each database can use up to 32 segment. You can increase SQL Server 6.5 performance by creation specific segments for specific tables or indexes.

By the way, Microsoft recommends to use hardware-based RAID or Windows NT software-based RAID instead of using segments.

There are three segments in SQL Server 6.5 (by default):

• system segment
• logsegment
• default segment

The system segment is used to store the system tables. The logsegment is used to store transaction log. The default segment is used to store all other database objects, unless you create additional segment and put data into it by using sp_placeobject system stored procedure or by CREATE TABLE statement.

Microsoft recommends to use segments for (this is from SQL Server Books Online):

• Placing a table on one physical device and its nonclustered indexes on a different physical device.
• Splitting a large, heavily-used table across database devices on two separate disk controllers.
• Storing data for text and image columns on separate physical devices.
• Placing a transaction log on a separate device so it can be dumped.

Create separate device for the transaction log

In SQL Server 6.5, any database (except the master database) can span multiple devices. If you want to ensure recoverability and reduce contention, you should place the transaction log on a separate device.

Put the log device on a different physical disk. So if one disk crashes, you have the database, or the log plus a recent backup, and in either case, you can recover.

Place transaction log on the faster hard drive

Read about "Microsoft SQL Server Optimization and Tuning" from msdn. It's therefrom:

"Windows NT File System Selection

"Microsoft's internal testing has shown that there is very little difference in SQL Server performance between New Technology file systems (NTFS) and file allocation table (FAT) file systems. In general, NTFS is faster than FAT for reads, and slightly slower for writes (because it performs transaction logging for recoverability). The SQL Server TPC-B benchmarks published by COMPAQ hardware used NTFS for the database devices and FAT for the log, because logging is write-intensive."

So, transaction log is more write-intensive than data device, and transaction log should be placed on the faster hard drive.

Raw partition

You can create a database device on the raw partition. It can increase the speed of your queries and INSERT/UPDATE/DELETE operations more than on 20 percents.

Raw partition has several limitations:

• Each raw partition can contain only one device.
• You cannot use file system operations such as copy, move and delete with raw partitions.
• The Windows NT Backup utility cannot be used to backup the devices located on raw partitions.
• Some file system services such as bad block replacement are not available with raw partitions.

Calculate the size of the database

Because database in SQL Server 6.5 cannot automatically increased, you should calculate estimating database size.

See the following link to decide how it can be made: "Special Edition, Using SQL Server 6.5 Second Edition" by Stephen Wynkoop, published by Que Corporation: Optimizing Performance.

It's difficult to make some general recommendations about the size of the transaction log, so start with 20 percent of the database size and gather the information about log growing.