SQL Server 2000 Optimization Tips

Original:

January 20, 2001
SQL Server 2000 Optimization Tips
By Sergey Vartanyan

http://www.databasejournal.com/features/mssql/article.php/1442691


Introduction

There are not so many differences in the internal architecture between SQL Server 7.0 and SQL Server 2000, as between SQL Server 6.5 and SQL Server 7.0. SQL Server 2000 has the same features as SQL Server 7.0: autogrow features, new storage engine, complete row level locking and so on. So, you can use all optimization tips from my previous article for SQL Server 7.0 (see this article): Some tips about database optimization for SQL Server 7.0

But there are some new features in SQL Server 2000, which you can use to increase performance of your databases.

In this article, I want to tell you, how you can increase performance of your databases by setting some options and configuring some parameters.

Distributed partitioned views

Distributed partitioned views allows you to partition tables horizontally across multiple servers. So, you can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers.

Due to distributed partitioned views, SQL Server 2000 now on the first place in the tpc-c tests.

For more information about tpc-c tests, see: TPC-C tests


Indexed Views

The new SQL Server 2000 feature are indexed views. You can create index on the view, and the result set of the view will be stored in the database. So, you can significantly improve the performance of an application where queries frequently perform certain joins or aggregations. Wnen you will modify the base table, the modifications will be automatically reflected in the view.

You should specify the SCHEMABINDING option for a CREATE VIEW statement, if you want to have a indexed view.

For more information, see: SQL Views


Using Tempdb for CREATE INDEX statement

When you create an index, you can specify WITH SORT_IN_TEMPDB option of the CREATE INDEX statement, which directs the database engine to use tempdb as a work area for the sorts required to build an index. This option reduces the index creation time when tempdb is on a different set of disks than the user database, but increases the amount of disk space used to create an index.

For more information, see: tempdb and Index Creation


Setting index order with CREATE INDEX statement

You can specify ascending or descending order with CREATE INDEX statement. The default is ascending order.

For more information, see: Table Indexes


Creating index defined on computed columns

In SQL Server 2000, you can create indexes on computed columns. The computed column must be deterministic, precise, and must not contain text, ntext, or image columns. The UNIQUE or PRIMARY KEY can also contain a computed column.

For more information, see: CREATE INDEX


Text in Row Data

SQL Server 2000 has eight types of pages in the data files. There are:

* Data pages
* Index pages
* Text/Image pages
* Global Allocation Map, Secondary Global Allocation Map pages
* Pages Free Space
* Index Allocation Map pages
* Bulk Changed Map pages
* Differential Changed Map pages

So, text and image values are stored on the Text/Image pages separately from the other data, stored on the Data pages.

SQL Server 2000 now supports a text in row table option that specifies that small text, ntext, and image values be placed in the Data pages with other data values in the same data row instead of Text/Image pages. This increases the speed of read and write operations and reduces the amount of space used to store small text, ntext, and image data values.

You can set a text in row table option with sp_tableoption stored procedure.