Introduction
In comparison with version 6.5, SQL Server 7.0 reduces the tuning
and configuration required to administer and monitor your databases.
There are many new useful features in SQL Server 7.0: autogrow features,
new storage engine, complete row level locking and so on. So, you can
work with SQL Server 7.0 databases without special knowledge about the
internal database architecture quite successfully, but if you want to
increase performance to its maximum value, you would know some
additional information about SQL Server 7.0 features.
In this article, I want to tell you, how you can increase performance
of your databases by setting some options and configuring some
parameters.
Boost SQL Server priority
You can set this option to 1, if you want from SQL Server to work
with a higher Windows NT scheduling priority than other processes
on the same computer. The default is 0. Setting boost priority to 1
can degrade the performance of users who work with other applications.
So, set boost SQL Server priority to 1 only if you have dedicated
server for SQL Server only. In other case, don’t change this option.
Increase Recovery Interval
The recovery interval option is the maximum number of minutes per
database that SQL Server needs to complete its recovery procedures.
The default value of this option is 0. It means that SQL Server
will automatically configure this option.
SQL Server issues a checkpoint, using the recovery interval option.
Microsoft does not recommend to change this option in general case,
but sometimes you can increase a speed, by setting this option manually.
You can monitor disk-write activity on the data files, and if you see
periodic spikes that send disk utilization to 100 percent, you
can increase the recovery interval. Microsoft suggests resetting to 5
and continue monitoring in this case.
By the way, Microsoft sets the recovery interval to 20 minutes at its
last tpc-c tests.
File and filegroups
There are no database devices and segments in SQL Server 7.0, now
database consists of two or more Windows files. There are three
types of SQL Server database files:
- primary data files (necessary for each database, extension .mdf)
- secondary data files (not necessary, extension .ndf)
- log files (necessary for each database, extension .ldf)
Now data and log information are never mixed in the same file, and one
database file can be used only by one database.
Database files (not Log files) are stored in the filegroups.
There are two types of filegroups:
- primary filegroup
- user-defined filegroup
Primary file is assigned to the primary filegroup, secondary files
can be assigned to user filegroups or the primary filegroup.
All system tables are placed in the primary filegroup.
One filegroup is designated as the default filegroup (initially,
it’s the primary filegroup). You can change the default filegroup
by using the ALTER DATABASE statement.
When creating database objects, you can specify the filegroup to
which these objects are assigned.
So, with large systems, you can create several filegroups and
place all user objects into user-defined filegroups, so that user
objects do not compete with system objects for space in the primary
filegroup. This can be used for administration and performance
reasons, to facilitates the customization of maintenance for each
filegroup.
In SQL Server 7.0 LOAD TABLE statement is no longer supported, but
you can place a table in its own filegroup and can restore only
that table. So, you can group tables and indexes with similar
maintenance requirements into the same filegroups.
SQL Server 7.0 creates a separate thread for each file, if a table
is accessed sequentially. So, if table is assigned to a filegroup
with three files, then tablescan will use three separate threads to
read the data in parallel. So, you can create one file per physical
disk to increase read performance.
Autogrow Feature
SQL Server 7.0 databases can automatically grow. Autogrow feature
is set by default. Automatically growing results in some performance
degradation, therefore you should set a reasonable size for your
databases and a reasonable autogrow increment to avoid automatic
growing too often.
To calculate the reasonable database size, you would previous calculate
the reasonable size of all your database tables, see this MSDN link for more
information:
Estimating the Size of a Table
