The table compares SQL Server version 7.0 behaviors with behaviors of earlier versions of SQL Server.
| SQL 6.x considerations | SQL Server 7.0 enhancements and philosophies |
|---|---|
| There are many configuration options to consider during performance tuning. | The database engine has become self-configuring, self-tuning, and self-managing. The lazy writer and Read Ahead Manager are self-tuning. The max async IO option is the only sp_configure option that must be configured initially, and then only when you are working with servers with large amounts of storage. This reduction in tuning requirements saves valuable administrative time, which can be applied to other tasks.
There are fewer sp_configure options that you must adjust manually to achieve good SQL Server performance. Although it is still possible to manually configure and adjust many of the sp_configure options available in earlier versions of SQL Server, it is recommended that you allow SQL Server to automatically configure and tune all sp_configure options for which SQL Server provides defaults. This allows SQL Server to adjust automatically the configuration of the database server as factors affecting the server change, such as RAM and CPU utilization for SQL Server and other applications running on the database server. |
| Manual tuning of lazy writer is sometimes necessary. | Unlike earlier versions of Microsoft SQL Server, Microsoft SQL Server 7.0 automatically configures and tunes the lazy writer. You no longer need to manually tune free buffer and max lazywrite IO. Free buffer and lazy writer I/O activity still can be monitored with SQL Server 7.0 Performance Monitor objects. |
| Manual tuning of checkpoint is sometimes necessary. | In earlier versions of Microsoft SQL Server, the recovery interval option also was used to tune the checkpoint process. When the recovery interval option is set to the default of 0, Microsoft SQL Server 7.0 automatically monitors and tunes the recovery interval. The default setting maintains recovery times of less than one minute for all databases, as long as there are no exceptionally long-running transactions present on the system. For more information, see SQL Server Books Online. |
| SQL Server 6.x log pages share RAM cache with data pages. Manual tuning of the log manager is sometimes necessary. |
The SQL Server log manager has changed significantly. The SQL Server 7.0 log manager manages its own log cache. SQL Server no longer depends on the syslogs table as it does in earlier versions of SQL Server. Separating the log file management from the data cache management brings enhanced performance for both components.
SQL Server log manager also is capable of performing disk I/O in larger block sizes than in earlier versions. Larger I/O size and the sequential I/O aids logging performance. SQL Server 7.0 automatically tunes the performance of SQL Server log manager. It is no longer necessary to manually tune the sp_configure option logwrite sleep, which has been removed from SQL Server 7.0. For more information, see SQL Server Books Online. |
| Page splitting is costly to a database server operation. | In earlier versions of Microsoft SQL Server, B-tree index pages were high maintenance due to significant row pointer recalculation necessary on the index pages during page splits (page splits occur as insert rows fill a data or index page). This issue has been minimized with the SQL Server 7.0 storage structures. Nonclustered index pages now use either Fixed RID (Row ID) for tables with no clustered index (these tables are called heaps) or a Clustering Key for tables with a clustered index. B-tree maintenance activities during inserts and page splits have been dramatically reduced. The overall effect is that index maintenance is much faster, which means that more nonclustered indexes can be created on a table with less impact on data modification performance. For more information, see SQL Server Books Online. |
| SQL Trace | SQL Server Profiler replaces the SQL Trace utility in SQL Server 6.5. SQL Server Profiler provides similar but significantly enhanced functionality. |
| ISQL/W | SQL Server Query Analyzer replaces ISQL/W from earlier versions of SQL Server. |
| Devices and segments | Files and filegroups replace the device and segment model used in earlier versions. Files and filegroups provide a more convenient method for spreading data proportionately across disk drives or RAID arrays. For more information, see SQL Server Books Online. |