|
|
| |
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.
*****
|
|
|
