How does SQL Server scale to eight processors?
As servers with eight CPUs arrive in the Windows NT market, IS professionals are beginning to consider NT a true enterprise platform that can replace legacy UNIX and midrange systems. But how do important NT applications, such as SQL Server, really scale?
Last year, the Windows NT Magazine Lab tested the scalability of SQL Server 6.5, Standard Edition for systems with one, two, and four processors (see Joel Sloss, "Microsoft SQL Server 6.5 Scalability," January 1997). These tests revealed that additional CPUs and Level 2 cache improve SQL Server performance. When 8-way systems came out, the Lab was eager to revisit these scalability tests to see how NT and SQL Server scale to eight processors. The Lab recently reviewed Axil Computer's (http://www.axil.com) eight-processor symmetric multiprocessing (SMP) server, the Northbridge NX801 (see "Northbridge NX801," April 1998), and I took advantage of the opportunity to test NT and SQL Server scalability.
The Hardware
To compare NT and SQL Server performance on four- and eight-processor systems, I tested SQL Server 6.5, Standard Edition on both the Northbridge NX801 and a four-processor SMP system, Amdahl's EnVista Server Model FS. The Northbridge NX801 has eight 200MHz Pentium Pro processors, a 512KB Level 2 cache, 2GB of RAM, 2MB of VRAM, one 3.5" disk drive, a 12X SCSI CD-ROM drive, a Diamond Stealth64 video graphics card, and twenty-four 4GB Seagate Barracuda SCSI hard disks. The Northbridge NX801 uses Adaptive Memory Crossbar architecture to scale up to eight processors. The Northbridge NX801 came with NT 4.0, UNL Processor Edition, a version of NT that Axil Computer modified to include an eight-processor hardware abstraction layer (HAL).
The EnVista has four 200MHz Pentium Pro processors, 523MB of RAM, four 10/100Mbps Fast Ethernet PCI network cards, and four 4GB Seagate Barracuda 4LP SCSI hard disks. The EnVista demonstrated its versatility in previous Lab tests, performing well in both SQL Server and file services tests. The EnVista came with NT Server 4.0, Standard Edition and Service Pack 3 (SP3).
In the Ring
To begin my tests, I ran SQL Server 6.5, Standard Edition on each server's native version of NT Server 4.0. I used the Order Entry/Mixed Reads and Writes test from Bluecurve's Dynameasure/SQL 1.5 to benchmark the systems' performance. (For information about Dynameasure/SQL, see "Dynameasure Enterprise 1.5," September 1997.) The Order Entry/Mixed Reads and Writes test consists of 19 SQL transactions that randomly perform reads and writes against a database to simulate business orders. These transactions create a well-balanced test of system performance without isolating any subsystem. I set the test specifications to a 500MB dataset and 5-second think time to increase stress on the servers. I ran tests at six steps, increasing the number of simulated users, or motors, at each step from 100 motors at step 1 to 600 motors at step 6. These test specifications were consistent with Joel Sloss' tests of one- to four-processor systems. (For more information about the Lab's benchmarking network, see the sidebar "The Lab's Test Environment," page 74.)
I fine-tuned the EnVista's use of memory cache to maximize its SQL Server 6.5, Standard Edition performance, and the EnVista provided great performance compared with other four-processor servers the Lab has benchmarked in the past. I ran identical tests on the Northbridge NX801.
Graph 1 shows each system's number of transactions per second at each step. The EnVista's number of transactions per second leveled off and its performance began to degrade after step 5. The EnVista peaked at 93.50 transactions per second at step 6. In contrast, the Northbridge NX801 peaked at 112.93 transactions per second, and its performance continued to increase at a relatively constant rate through step 6.
Graph 2 shows each system's average response time at each step. The EnVista's average response time climbed rapidly from step 5 to step 6. Its average response time at step 6 was 1.57 seconds. The Northbridge NX801's average response time increased only slightly to 0.36 seconds from step 5 to step 6. The data from Graph 1 and Graph 2 indicates that I needed to increase the stress on the eight-processor machine to see a rise in response time and a degradation in performance.
Graph 3 shows each system's number of motors per step. Both systems' motors-per-step graphs are linear, and both systems supported 600 motors at step 6. When I further stressed the EnVista in additional tests, the server demonstrated that it couldn't support more than 638 users. After step 6, the EnVista's transactions per second decreased from 93.50, and its average response time continued to climb rapidly.