SQL Server 2000 on Large Servers

One of the primary design goals for Microsoft® SQL Server™ 2000 and SQL Server version 7.0 is to increase their ability to implement the databases supporting the largest Web sites and enterprise systems. Although earlier versions of SQL Server do well at supporting large numbers of concurrent users, the length of time it takes them to run utility, backup, and restore operations limits the size of a manageable SQL Server database to 200 through 300 GB.

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

• SQL Server Enterprise Edition can scale effectively on up to 32 microprocessors on SMP computers running Microsoft Windows® 2000 DataCenter.
  
  
• SQL Server 2000 Enterprise Edition can use up to 64 GB of physical memory (RAM) on Windows 2000 DataCenter. For more information, see Using AWE Memory on Windows 2000.
  
  
• SQL Server 2000 Enterprise Edition supports distributed partitioned views, which allow groups of database servers to support the workload of a large Web site or enterprise system. Such a group, or federation, of servers must be administered separately, but provide the same level of performance as a cluster of database servers. For more information, see Federated SQL Server 2000 Servers.
  
  
• SQL Server 2000 Enterprise Edition supports indexed views. Creating an index on a view causes the view to be materialized, and its result set stored in the same format as a table. For certain types of views, this can improve performance exponentially. For more information, see View Indexes.
  
  
• The on-disk data structures that support 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.
  
  
• SQL Server 2000 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.
  
  
• Hash and merge join types offer improved join performance. For more information, see Advanced Query Tuning Concepts.
  
  
• SQL Server 2000 supports intra-query parallelism on servers that have more than one microprocessor, or 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 2000 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 2000 uses Microsoft Windows NT® and Windows 2000 asynchronous I/O and scatter-gather I/O, along with buffer cache management algorithms to maximize OLTP performance. For more information, see I/O Architecture.
  
  
• The speed of the BACKUP and RESTORE statements is fast enough to run the statements 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. 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 SQL Server 2000 and SQL Server 7.0 on-disk data structures are much simpler than in earlier versions, which make the structures more robust. 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 improvements result in fewer problems with on-disk structures and reduce or eliminate the need to run database integrity checks.
  
  
• The algorithms in the database integrity check statements are much faster in SQL Server 2000 and 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 2000 and version 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 Loads.
  
  
• SQL Server 2000 and version 7.0 now support 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 2000 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 2000 defines OLE DB extensions that OLE DB providers can use to report distribution statistics to the SQL Server 2000 database engine. This allows the engine to more efficiently optimize distributed queries. The Microsoft OLE DB Provider for SQL Server 2000 supports these extensions, improving the performance of distributed queries referencing SQL Server databases. For more information, see Distribution Statistics Requirements for OLE DB Providers.
  
  
• SQL Server includes failover cluster support. Two to four Windows NT or Windows 2000 servers can have instances of SQL Server and all access a set of cluster disks holding SQL Server databases and each instance is identified by a single virtual server name. If the server currently processing SQL Server requests fails, one of the other Windows servers starts its SQL Server services, recovers any uncompleted transactions recorded in the database logs, and begins operating in place of the lost server. For more information, see Failover Clustering Architecture.
  
  
• SQL Server 2000 introduces log shipping, which can be used to maintain a warm standby server. The transaction logs from a production server are periodically backed up and applied to a warm standby server. If the production server fails, the warm standby server can be brought online in its place. For more information, see Log Shipping.