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:
- Create new database device with the appropriate size.
- Uncheck "Default device" option for the master database device.
- Set "Default device" option for the new device.
- Put tempdb database into RAM from the Enterprise Manager or sp_configure system stored procedure.
- Stop and start MSSQLServer service.
- Put tempdb database back on the hard disk from the Enterprise Manager or sp_configure system stored procedure.
- Stop and start MSSQLServer service.
- 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.
» 