SQL Server 6.5 Optimization Tips
November 28, 2000
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:
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.
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):
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):
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:
So, transaction log is more write-intensive than data device, and transaction log should be placed on the faster hard drive.
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:
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.