SQL Server 7.0 on Large Servers

One of the primary design goals for Microsoft® SQL Server™ version 7.0 was to increase its ability to support large databases with many concurrent users that have to run in production 24 hours a day, 7 days a week. While earlier versions of SQL Server did well at supporting large numbers of concurrent users, the length of time it took to run utility, backup, and restore operations limited the size of a manageable SQL Server database to around 200 to 300 MB.

SQL Server 7.0 Enterprise Edition is capable of handling terabyte-sized databases with thousands of concurrent users. Some of the features that allow this are:

• The on-disk data structures in the database have been redesigned. The new data structures and data organization present more opportunities for parallel processing and serial, read-ahead scans. Table scans and index scans can now be performed serially, which is especially useful in online analytical processing (OLAP) that characterizes data warehouses. For more information, see I/O Architecture.
• The database page size increased from 2 KB to 8 KB, and the size of database extents increased from 16 KB to 64 KB. This results in large, more efficient I/O transfers.
• SQL Server 7.0 now natively supports the prepare/execute model of executing SQL statements. It also has logic to share query execution plans between connections without requiring an application to prepare the statement. These features reduce the overhead associated with compiling and executing statements. For more information, see Execution Plan Caching and Reuse.
• New hash and merge join types offer improved join performance. For more information, see Advanced Query Tuning Concepts.
• SQL Server 7.0 supports intra-query parallelism on servers which have more than one CPU. Individual SQL statements can be split into two or more tasks that operate concurrently to return the results faster. For more information, see Parallel Query Processing.
• SQL Server 7.0 evaluates an SQL statement and dynamically chooses the locking granularity (row, page, table) that will maximize concurrent throughput. For more information, see Locking Architecture.
• SQL Server 7.0 uses Microsoft Windows NT® asynchronous I/O and new scatter-gather I/O along with new buffer cache management algorithms to maximize OLTP performance. For more information, see I/O Architecture.
• The speed of the BACKUP and RESTORE statements have been significantly improved. BACKUP has been improved to do a serial scan without the random reads required in earlier versions of SQL Server. BACKUP and RESTORE can be run during production work because they do not interfere with database activity. BACKUP and RESTORE use parallel I/Os when a backup is stored on multiple backup devices. New BACKUP options, such as differential backups, and backing up only files or filegroups, reduce size of backups and their effect on the system. For more information, see Backup/Restore Architecture.
• The robustness of the SQL Server 7.0 on-disk data structures are much simpler than in earlier versions, which makes the structures less prone to problems. Also, the database engine is coded to detect errors at relatively early points in processing and terminate a task before it causes problems in the database itself (fail-fast logic). These result in fewer problems with on-disk structures and reduces or eliminates the need to run database integrity checks.
• The algorithms in the database integrity check statements are much faster in SQL Server 7.0 than in earlier versions. The integrity check statements now make a single serial scan of the database and check objects in parallel during the scan of the database. For more information, see Data Integrity Validation.
• The SQL Server 7.0 bulk copy components now transfer data at increased speeds. The bcp bulk copy utility can now copy data in parallel from multiple sources into the same file concurrently. For more information, see Parallel Data Load Using bcp.
• SQL Server 7.0 now supports doing bulk loads directly on the server without transferring the data through a client. This is done using the new BULK INSERT statement, and is the fastest way to get large amounts of data into a table. For more information, see BULK INSERT.
• Distribution statistics indicate the selectivity of index keys and are used by the query optimizer to choose the most efficient index when compiling a query. If the statistics are out of date, the optimizer may not generate an optimal execution plan. SQL Server 7.0 can be set up to generate distribution statistics automatically, which improves the effectiveness of the query optimizer. The sampling processes that generate the statistics have also been improved; they can now generate reliable statistics after scanning less data than earlier versions of SQL Server. For more information, see Statistical Information.
• SQL Server includes failover cluster support. Two Windows NT servers can have copies of SQL Server and both access a set of shared disks holding SQL Server databases. One operates as the primary server and the other as the secondary. If the primary server fails, the secondary server comes online, recovers any uncompleted transactions recorded in the database logs, and begins operating in place of the lost primary server. For more information, see Configuring SQL Server Failover Support.
• SQL Server Enterprise Edition running on Windows NT Enterprise Edition offers two significant performance benefits:
  • It can scale effectively on up to 32 processors.
  • When running on a Digital Alpha server, it can use 64-bit memory addressing.