Microsoft SQL Server 2000 Scalability Project – Basic Capacity Scalability

Author: Man Xiong
Microsoft Corporation

July 2001

Summary: Microsoft® SQL Server™ 2000 running on Microsoft Windows® 2000 servers handles the largest mission-critical applications. This white paper is a joint effort by Microsoft and Dell™ to demonstrate the scalability of SQL Server 2000 and Dell hardware. SQL Server 2000 running on a Dell enterprise eight-way server can support multiple-terabyte databases and handle heavy workload and administrative tasks. SQL Server 2000 maximizes your return on investment in symmetric multiprocessing (SMP) systems, allowing users to add processors, memory, and disks to build large, centrally managed enterprise servers.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred.

 2001 Microsoft Corporation. All rights reserved.

Microsoft, Windows, and Win32 are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Table of Contents

Microsoft SQL Server 2000 Scalability Project – Basic Capacity Scalability

Table of Contents

Introduction

SQL Server Scalability

Hardware Scalability

Computer Configuration

1 TB OLTP Database

OLTP Workload

Index Reorganization

2 TB and 4 TB Decision Support Systems

CREATE DATABASE Statement

Bulk Data Loading

Index Creation

DSS Workload

Database Verification

Conclusion

For More Information

Appendix A: A Dell Solution for Scalable Enterprise Computing

Appendix B: Test Configuration forthe1 TBOLTPDatabase

Appendix C: Test Configuration forthe 2 TBDSSDatabase

Appendix D: Test Configuration forthe 4 TBDSSDatabase

Introduction

Many successful companies are upsizing their online applications as their businesses expand with the growth of e-commerce. Now that every Internet and intranet user is a potential client, database applications face enormous user and transaction loads. As a result, many companies are building systems that can support millions of customers and users. Database servers are at the core of these systems—often managing multiple terabytes (TB) of information. Scalable systems solve the upsizing problem by giving the designer a way to expand the network, servers, database, and applications by simply adding more hardware. Scalable computer systems can increase an application’s client base, database size, and throughput without application reprogramming.

Well-known in the industry for its ease-of-use and self-tuning features, Microsoft® SQL Server™ 2000 manages large-scale servers as easily as smaller systems on a per-user basis. The tests described in this paper demonstrate the performance and scalability of SQL Server 2000 on very large databases (VLDB) without special tuning or unrealistic benchmark hardware configurations.

SQL Server Scalability

Microsoft SQL Server 2000 can both scale up on symmetric multiprocessing (SMP) systems and scale out on multiple-node clusters. This paper focuses on the scale-up scenario, demonstrating the scalability of a single SQL Server database running on a large SMP system, the Dell™ PowerEdge 8450.

Tests were conducted using three scenarios:

  • 1-TB online transaction processing (OLTP) database
  • 2-TB decision support system (DSS) database
  • 4-TB DSS database

The results demonstrate that SQL Server 2000 can manage very large (1 to 4 TB) OLTP and DSS databases by:

  • Maximizing the utilization of additional hardware resources (processors and memory) to provide predictable performance as the workload on the system increases.
  • Minimizing performance degradation while performing online management in the background.

Hardware Scalability

The hardware environment is one of the key considerations in achieving performance in scalable database environments. In these specific tests, a static hardware environment was maintained with an 8-processor Dell™ PowerEdge 8450 system with 5 TB of storage on 160 physical disks running on Microsoft Windows® 2000 Datacenter Server. For OLTP-centric solutions, rigorous testing and participation in industry-standard benchmarks has shown that the following hardware considerations can have an important effect on the performance of the application:

  • Processors and memory

Database applications can be very processor and memory intensive depending on the type of database, the focus of the application, and usage patterns. To achieve optimal performance from the processor, testing has shown that not only is the speed of the processor important, but in scalable database environments, the size of the level 2 (L2) cache is equally important. If the application is configured to maximize the available processing power, then the more L2 cache available on the processor, the better the performance of the application. Additionally, if testing shows that the bottleneck in the application is in the available amount of memory, adding memory to the system can improve performance.

  • Disk subsystem

As database applications are scaled, I/O is a common bottleneck. In specific applications in which access to the application data is more random than sequential (as in OLTP), increasing the number of disk drives can mitigate an I/O bottleneck. This allows the application to spread the I/O load across multiple paths and puts the performance load on the system processor, as opposed to the disk subsystem. If the application data access is more sequential than random (as in DSS), additional drives will not provide as much performance benefit.

  • Networking

One of the key environmental considerations affecting performance is the bandwidth available over the network for the application. High-performance network protocols (such as the Virtual Interface Architecture–based SAN support included in SQL Server 2000) have been shown to improve performance by reducing networking bottlenecks in scalable database solutions.

For more information about the Dell products used in these tests, see Appendix A.

Computer Configuration

The tests were conducted using the following environment:

Server

1 Dell PowerEdge 8450

8 Intel® Pentium® III Xeon™ 700 MHz processors

32 gigabytes (GB) of RAM

8 Qlogic QLA2200 PCI Host Bus Adapters

4 Dell PowerVault™ 650F, each with 10, 18-GB, 10,000-RPM disks and 512-KB write-read cache

12 Dell PowerVault 630F, each with 10, 18-GB, 10,000-RPM disks

Total Disk Space = 5 TB: (160) 18-GB, 10,000-RPM disks

Operating system

Microsoft Windows 2000 Datacenter Server, Build 5.00.2195, Service Pack 1

Database server

Microsoft SQL Server 2000 Enterprise Edition, Build 2000.80.194.0

1-TB OLTP Database

In this test, a representative OLTP database is generated. A transaction workload is simulated by software. The test application models a wholesale supplier managing orders and inventory. Transactions are provided to submit an order, process a payment, record deliveries, and check order status and stock levels. Although the underlying components and activities of this application are specific to this test, they are intended to be representative of typical OLTP systems. This test demonstrates that SQL Server 2000:

  • Achieves excellent performance running a large OLTP workload.
  • Performs online management work with minimal degradation to the transactional workload.

For information about the SQL Server database used for this test, see Appendix B.

OLTP Workload

To improve transaction throughput, one of the most common techniques is to add more CPUs and memory to the server. Adding more CPUs can help scale the system to handle more users or additional workload. OLTP workloads are often very I/O intensive due to simple transactions requiring random disk-reads and disk-writes. These workloads benefit from increased memory because a larger SQL Server buffer cache greatly reduces data and index page faults, thereby enhancing the workload performance.

SQL Server 2000 on Windows 2000 Datacenter Server can manage up to 64 GB of memory by using the Address Windowing Extensions (AWE). Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit Windows 2000 processes are therefore limited to 4 GB. By default, 2GB is reserved for the operating system, and 2GB is made available to the application. By specifying a /3GB switch in the Windows 2000 Datacenter Boot.ini file, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB.

AWE is a set of extensions to the memory management functions of the Microsoft Win32® API that allows applications to address more memory than the 4 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory as nonpaged memory and then dynamically map views of the nonpaged memory to the 32-bit address space. Although the 32-bit address space is limited to 4 GB, the nonpaged memory can be much larger. This enables memory-intensive applications that support AWE, such as SQL Server 2000 Enterprise Edition, to address more memory than can be supported in a 32-bit address space. On systems with 16 GB or more physical memory, it is recommended that the maximum memory for SQL Server be set to at least 1 GB less than total available physical memory, thereby leaving at least 1 GB for the operating system.

Test Results

The OLTP workload is run on systems with the following CPU and memory configurations:

  • 2 CPUs, 4 GB total physical memory
  • 4 CPUs, 8 GB total physical memory
  • 8 CPUs, 32 GB total physical memory

As shown in Figure 1, OLTP workload performance improves when the number of CPUs is increased along with the memory. The increase in transaction throughput is nearly linear, demonstrating an excellent return on hardware investment.

Figure 1

Additional tests with different combinations of CPUs and memory show a larger amount of memory helps only when there are available CPU cycles, as shown in Figure 2. For this database size and OLTP workload, using the full processing power and memory of this 8-way server significantly increases transaction throughput.

Figure 2

Index Reorganization

Microsoft SQL Server 2000 includes online index reorganization, a new feature that is an effective mechanism for maintaining optimal performance despite the effects of index fragmentation common to OLTP databases. Because this feature can be used online, it supports today’s requirements for high availability, 24 hours a day, seven days a week.

Test Results

DBCC INDEXDEFRAG using default parameters is run concurrently with the OLTP workload. The workload is first run normally at 50 percent CPU utilization to establish a baseline for transaction throughput. Next, an online reorganization is performed with the workload running. In this test, the online reorganization causes transaction throughput to degrade by only 1 percent.

This test demonstrates that SQL Server 2000 can perform online reorganization with minimal transaction throughput degradation. Results vary with hardware configuration and characteristics of the OLTP workload.

2-TB and 4-TB Decision Support Systems

In this test, a representative DSS database generation and workload software kit are used to simulate the data analysis requirements of a decision-support system processing business-oriented ad hoc queries. It models a decision-support system that loads data or refreshes data from an OLTP database and produces computed and refined data to support sound business decisions. Queries are provided to assist decision makers in five domains of business analysis: pricing and promotions, supply and demand management, profit and revenue management, customer satisfaction study, marketing share study, and shipping management. The underlying components and activities of this application are intended to be representative of a typical DSS system. This test demonstrates that SQL Server 2000:

  • Achieves excellent performance for a standard DSS workload.
  • Performs online management operations with minimal workload degradation.
  • Scales predictably with increasing database size.

For information about the database used for the 2-TB DSS test, see Appendix C. For information about the database used for the 4-TB DSS test, see Appendix D.

CREATE DATABASE Statement

The CREATE DATABASE statement is used to initialize the 2-TB and 4-TB databases and allocate the space for the databases.

Test Results

Scalability on the Size of the Database

The test results demonstrate scalability in relation to the size of the database: The database loading time scales linearly when going from a 2-TB database to a 4-TB database. (The average throughput is 730 GB per hour for the 2-TB DSS and 722 GB per hour for the 4-TB DSS.)

Bulk Data Loading

Microsoft SQL Server 2000 supports bulk database loading from multiple data streams in parallel. This technology makes optimal use of multiple CPUs and available I/O bandwidth.

Test Results

Effect of Multiple Streams

The BULK INSERT statement in SQL Server 2000 is used to load data into the 2-TB and 4-TB DSS databases. As shown in Figure 3, data-loading throughput increases predictably with the number of input streams.

Figure 3

Effect of Increased Data Volume

When going from a 2-TB database to a 4-TB database, the database loading time scales linearly.

Index Creation

Usually, decision-support systems require very complex indexes to speed up query processing. In this test, indexes are built after the data is loaded.

SQL Server 2000 uses two types of indexes:

  • Clustered indexes
  • Nonclustered indexes

A clustered index determines the physical order of data in a table. In a nonclustered index, the data is stored in one place, the index in another, with pointers to the storage location of the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order (which can be dictated by a clustered index).

Test Results

Clustered vs. Nonclustered Index Creation Times

Index creation time is tested by building a clustered index on column A of a 0.65-TB table and a nonclustered index on the same column. Creating the clustered index on column A takes 25 percent more time than creating the nonclustered index on the same column, primarily due to the additional time required to move the data into key order.

Effect of Multiple CPUs on Index Creation

A clustered index is built on column A of a 0.65-TB table and a nonclustered index on column B on the same table. All columns are of int data type. For testing purposes, the database with unsorted data is backed up once before building any of these indexes, and then this backup is restored prior to building the desired index.

As shown in Figure 4, increasing the number of CPUs increases index creation throughput for all indexes. Again, throughput increases predictably with additional CPUs.

Figure 4

Effect of Table Size on Index Creation Time

The index creation time scaled linearly when going from a 0.65-TB table to a 1.30-TB table, demonstrating that SQL Server 2000 index creation scales predictably.

Disk Space Required by Index Creation

Creating a clustered index requires approximately 1.2 x data volume extra disk space beyond the size of the existing table data. When a clustered index is created, the table is copied, the data in the table is sorted, and then the original table is deleted. Therefore, enough empty space must exist in the database to hold a copy of the data, the B-tree structure, and some space for sorting. In this test, 0.85 TB extra disk space is needed for the data files to create a clustered index on a 0.7-TB table. The index creation was performed with the recovery mode of the database set to BULK_LOGGED. In this mode, the system requires log space sufficient to record the allocation of storage for the new clustered index and the drop of the original table. In this configuration, 50 GB of log space is adequate to create a clustered index on a 0.65-TB table.

DSS Workload

This ad hoc DSS workload simulates an environment in which the users connect to the system to submit queries that are ad hoc in nature. It is composed of complex, read-only queries, as well as inserts and deletes to simulate the refresh of the database, perhaps from a source OLTP system. To simulate a real-world DSS environment, the queries are submitted by multiple, concurrent user sessions. This requires SQL Server to balance system resource allocation among the simultaneously running OLTP and DSS queries, which is done without any human intervention.

Test Results

Effect of the Number of CPUs

A massive DSS workload is run on the 2-TB DSS database. In this test, eight osql connections are run, each executing a mix of over 20 representative queries, plus insert and delete activity. Figure 5 shows relative query performance as a function of number of CPUs. The results for 2 and 4 processors are given relative to the 8-processor result. This illustrates nearly perfect linear scaling of query performance with the number of CPUs using SQL Server 2000.

Figure 5