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.
For more information, see:
sp_tableoption
» 
Comment and Contribute
