Here are sixteen useful tips that you can use to ensure your SQL Server 6.5 databases are performing in the most efficient manner possible.
1. Set a reasonable size for your database.
Because SQL Server 6.5 database cannot automatically grow, you should
estimate how big the database will be. To derive a reasonable estimate for the database size, you should first estimate
the size of each table individually, and then add all the values obtained.
2. Set a reasonable size for the transaction log.
Because the SQL Server 6.5 transaction log cannot automatically grow, you
should estimate how large the transaction log will likely become.
The general rule of thumb for setting the transaction log size is
to set it to 20-25 percent of the database size. The smaller the size
of your database, the greater the size of the transaction log
should be, and vice versa. For example, if the estimated database
size is equal to 10 Mb, you can set the size of the transaction log
to 4-5 Mb, but if the estimated database size is over 500 Mb,
then 50 Mb should be enough for the size of the transaction log.
3. You can place a database device on a raw partition.
It can increase the speed of your queries and modify operations
by 20 percent or more.
4. Move tempdb database to its own database device in order to simplify
administering and monitoring.
This is the description:
1. Create a new device with appropriate size (tempdb_dev for example).
2. Uncheck the “Default device” option for the master database device
(this option is enabled for the master database device by default).
3. Set “Default device” option for the tempdb_dev device.
4. From the Enterprise Manager (or sp_configure) set tempdb to be
in RAM (set value to 1).
5. Stop and restart MSSQLServer service.
6. From the Enterprise Manager (or sp_configure) set tempdb to not be
in RAM (set value to 0).
7. Stop and restart MSSQLServer service.
5. You can set the tempdb database to be in RAM.
If your queries contain subqueries, or GROUP BY, or ORDER BY clause, you
can increase their performance by placing the tempdb database into RAM.
6. Create a 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.
7. For heavily accessed tables with text/image columns, place these
tables on one device and place text/image columns on a different
device on separate physical disks.
This can be useful to simplify administering and monitoring.
8. Place a table on one physical device and its nonclustered indexes
on a different physical device.
This will improve performance because separate threads will be created
to access the tables and indexes.
9. Split a large, heavily-used table across database devices on two
separate disk controllers.
This will improve performance because when a table is accessed
sequentially, a separate thread is created for each physical device
on each disk array in order to read the table’s data in parallel.
10. If one of the join queries is used most often, place the tables
used in this query on different devices on separate physical
disks.
11. Create a separate physical device and place some tables in it to
run maintenance tasks (backups, DBCC, update statistics, and so on)
against these tables.
You can group user objects with similar maintenance requirements into
the same physical device. This can be useful to simplify administering
and monitoring.
12. If you have several physical disk arrays, try to create as many
physical
devices as there are physical disk arrays so that you have one physical
device per disk array.
This will improve performance because when a table is accessed
sequentially, a separate thread is created for each physical device
on each disk array in order to read the table’s data in parallel.
13. You can backup and restore a single table.
It can be used to improve the speed of backup process and decrease the
backup size. In SQL Server 7.0 and higher, the LOAD TABLE statement is
no longer supported.
14. If you create a default constraint for some fields, you should
define these fields as NOT NULL.
It can be used to store storage space. See this article for more details:
Optimization
Tips for MS SQL 6.5: Storage Nullable fields
15. Place all nullable fields at the end of the fields list (after
fields with fixed length), so that the more often this field will contain
NULL value, the closer to the end of the record it should be placed.
Again, this can be used to store storage space. See this article for more details:
Optimization
Tips for MS SQL 6.5: Storage Nullable fields
16. If you use the OR logical operation to find rows from a MS SQL 6.5 table,
and there is an index on the field for which values you use the OR operation,
then you can improve performance by writing two queries and combining
their result sets by using the UNION ALL statement.
If you use the OR logical operation to find rows from a MS SQL 6.5
table, and there is an index on the field for which values you use the OR
operation, then MS SQL 6.5 can use worktable with a dynamic index
on searchable fields instead of a simple index search. So, if the table is
very big, it can take a lot of time.
You can increase the speed of this query by dividing it into two select
statements and union these statements with the UNION ALL operator. For each
query the appropriate index will be used, and this way can increase
the speed of the new select statement by several times in comparison
with the first one.
See this article for more details:
Using
UNION ALL Statement in SQL Server 6.5.
