SQL Server 2000 Optimization Tips

May 6, 2002

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.

» See All Articles by Columnist Alexander Chigrik