SQL Tips - SQL Server 6.5 Optimization Tips

*****

Set a reasonable size of your database.
Because SQL Server 6.5 database cannot automatically grow, you should estimate how big the database will be. To estimate the reasonable database size, you should previous estimate the size of each table individually, and then add the values obtained.

*****

Set a reasonable size for the transaction log.
Because SQL Server 6.5 transaction log cannot automatically grow, you should estimate how big the transaction log will be. The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The less the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimation database size is equal to 10Mb, you can set the size of the transaction log to 4-5Mb, but if the estimation database size is over 500Mb, the 50Mb can be enough for the size of the transaction log.

*****

You can place a database device on a raw partition.
It can increase the speed of your queries and modify operations on 20 percents and more.

*****

Move tempdb database on its own database device to simplify administering and monitoring.
This is the description:
1. Create new device with appropriate size (tempdb_dev for example).
2. Uncheck "Default device" option for the master database device (this option is enable 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.

*****

You can put 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.

*****

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.

*****

For heavily accessed table with text/image columns place this table on a one device and place text/image columns on a different device on separate physical disks.
It can be useful to simplify administering and monitoring.

*****

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.

*****

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.

*****

If one of the join queries is used most often, place the tables used in this query on different devices on separate physical disks.

*****

Create 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. It can be useful to simplify administering and monitoring.

*****

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.

*****

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.

*****

If you create 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

*****

Place all nullable fields to the end of the fields list (after fields with fixed length), so than more often this field will contain NULL value, the closer to the end of the record it should be placed.
It can be used to store storage space.
See this article for more details:
Optimization tips for MS SQL 6.5: storage Nullable fields

*****

If you use OR logical operation to find rows from a MS SQL 6.5 table, and there is index on the field for which values you use OR operation, then you can improve performance by writing two queries and combine their result sets by using the UNION ALL statement.
If you use OR logical operation to find rows from a MS SQL 6.5 table, and there is index on the field for which values you use OR operation, then MS SQL 6.5 can use worktable with dynamic index on searchable field instead 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 divide it into to select statement and union this statements with UNION ALL operator. For each query the appropriate index will be used, and this way can increase the speed of the new select statement in several times in comparison with the first one.
See this article for more details:
Using UNION ALL statement in SQL Server 6.5.