Here are fourteen little known tips that you can use to ensure your SQL Server 2000 databases are performing in the most efficient manner possible.
1. For very large databases, use distributed partitioned views to
partition tables horizontally across multiple servers.
This is a new SQL Server 2000 feature, which is available when using
SQL Server 2000 Enterprise Edition only.
Due to distributed partitioned views, SQL Server 2000 now occupies first
place in the TPC-C tests.
2. Use indexed views to increase the speed of your queries.
The result set of the indexed view is persistent in the database
and indexed for fast access. Because indexed views depend on base
tables, you should create indexed views with the SCHEMABINDING option
to prevent table or column modification that would invalidate
the view.
Furthermore, using views instead of heavy-duty queries can reduce
network traffic and can be used to facilitate permission management.
3. Consider using the WITH SORT_IN_TEMPDB option when you create an
index and when tempdb is on a different set of disks than the user database.
Using this option can reduce the time it takes to create an index, but it does
increase the amount of disk space used to create an index.
4. You can specify whether the index keys are stored in ascending or
descending order.
For example, using the CREATE INDEX statement with the DESC option
(descending order) can increase the speed of queries, which return
rows in the descending order. Ascending order is used by default.
5. Consider creating index on computed columns.
In SQL Server 2000, you can create indexes on computed columns. To create
an index on a computed column, the computed column must be deterministic,
precise, and cannot have text, ntext, or image data types.
6. Consider setting the ‘text in row’ table option.
The text, ntext, and image values are stored on the Text/Image pages,
by default. This option specifies that small text, ntext, and image
values will be placed on the Data pages with other data values in a
data row.
This can increase the speed of read and write operations and reduce the
amount of space used to store small text, ntext, and image data values.
You can set the ‘text in row’ table option by using the sp_tableoption
stored procedure.
7. Use table variables instead of temporary tables.
The table variable is a new SQL Server 2000 feature. The table variables
require less locking and logging resources than temporary tables, so
table variables should be used whenever possible.
8. Use cascading referential integrity constraints instead of triggers,
whenever possible.
For example, if you need to make cascading deletes or updates, you can
specify ON DELETE or ON UPDATE clause in the REFERENCES clause of
the CREATE TABLE and ALTER TABLE statements.
The cascading referential integrity constraints are much more efficient
than triggers and can boost performance.
9. If you work with SQL Server 2000 Enterprise Edition, use SAN (System
Area Network) protocols instead of LAN (Local Area Network) or WAN (Wide Area
Network).
SANs are more reliable than LANs or WANs and support high levels of
messaging traffic by lowering CPU loads and message latency.
10. Use user-defined functions to encapsulate code for reuse.
The user-defined functions (UDFs) contain one or more Transact-SQL
statements that can be used to encapsulate code for reuse. Using UDFs
can reduce network traffic.
11. Set the ‘awe enabled’ server configuration option to 1 if you work
with SQL Server 2000 Enterprise or Developer edition and have more than
4 gigabytes (GB) of physical memory.
Because SQL Server 2000 can support up to a maximum of 64 gigabytes (GB)
of physical memory, you can purchase the appropriate server box and get
all the advantages of the hardware platform.
12. Use the DBCC CHECKCONSTRAINTS statement if you need to check the
integrity of a specified constraint or all constraints on a specified
table.
13. Use the DBCC INDEXDEFRAG statement to defragment clustered and
secondary indexes of the specified table or view.
DBCC INDEXDEFRAG statement is an online operation. Unlike DBCC DBREINDEX,
DBCC INDEXDEFRAG does not hold locks long term and thus will not block
running queries or updates. So, try to use the DBCC INDEXDEFRAG command
instead of DBCC DBREINDEX whenever possible.
14. You can use INSTEAD OF triggers to perform enhance integrity checks
on the data values.
The INSTEAD OF trigger is a new SQL Server 2000 feature. These triggers
can be used to enforce business rules when constraints cannot be used.
Note: Because triggers are more resource expensive, use constrains
instead of triggers whenever possible.
