SQL Server 7 Optimization Tips
December 21, 2000
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:
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 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.
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