SQL Server 6.5 Optimization Tips

June 17, 2002

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.

» See All Articles by Columnist Alexander Chigrik