SQL Server 8-Way Scalability

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.

The Enterprise Server
The Northbridge NX801 posted better results for SQL Server than the four-processor system. Thus, I decided to use the Northbridge NX801 to compare SQL Server 6.5, Standard Edition with SQL Server 6.5, Enterprise Edition. First, I established a baseline benchmark for NT Server 4.0, UNL Processor Edition and SQL Server 6.5, Standard Edition. Second, I tested SQL Server 6.5, Standard Edition on NT Server 4.0, Enterprise Edition. Third, I benchmarked SQL Server 6.5, Enterprise Edition on NT Server 4.0, Enterprise Edition and compared the three tests' results. I used Dynameasure/SQL's Order Entry/Mixed Reads and Writes test to place a measured stress on the Northbridge NX801 in its various configurations. I set my six steps to simulate 100 to 1000 users.

NT Server 4.0, Enterprise Edition comes with a new 4GB RAM Tuning (4GT) capability that lets memory-intensive applications use up to 50 percent more RAM on Intel servers. Microsoft informally warned the Lab that 4GT might not improve performance on systems with 2GB of RAM or less; 4GT only benefits memory-intensive applications that use more than 2GB of RAM.

Graph 4 shows each configuration's number of transactions per second at each step. SQL Server 6.5, Standard Edition running on NT Server 4.0, Enterprise Edition consistently performed more transactions per second than the other two configurations. This configuration reached 167.87 transactions per second at step 6. SQL Server 6.5, Standard Edition on NT Server 4.0, UNL Processor Edition reached 157.24 transactions per second, and SQL Server 6.5, Enterprise Edition on NT Server 4.0, Enterprise Edition reached 144.01 transactions per second at step 6. All three configurations' number of transactions per second continued to show linear increases through step 6.

Graph 5 shows each configuration's average response time at each step. SQL Server 6.5, Standard Edition on NT Server 4.0, Enterprise Edition achieved lower average response times than the other configurations for most steps. SQL Server 6.5, Enterprise Edition running on NT Server 4.0, Enterprise Edition averaged a response time of 0.86 seconds at step 6, which was considerably slower than either of the other two configurations. SQL Server 6.5, Standard Edition on NT Server 4.0, Enterprise Edition had an average response time of only 0.29 seconds at step 6. SQL Server 6.5, Standard Edition on NT Server 4.0, UNL Processor Edition had an average response time of 0.47 seconds in step 6.

Graph 6, page 73, shows each configuration's number of motors per step. SQL Server 6.5, Standard Edition on NT Server 4.0, Enterprise Edition supported a maximum of 879 motors in my tests; SQL Server 6.5, Standard Edition on NT Server 4.0, UNL Processor Edition and SQL Server 6.5, Enterprise Edition on NT Server 4.0, Enterprise Edition both supported a maximum of 858 motors. I was surprised to find that these configurations supported similar numbers of motors. I was even more surprised that the Northbridge NX801 didn't limit the number of motors my tests could run. The Lab's benchmarking network reached its limit when the control server that Dynameasure uses to conduct the tests and measure the results couldn't keep up with the Northbridge NX801. I couldn't overstress the Northbridge NX801 using the Lab's current benchmarking setup.

The linear projections of all the software configurations' transactions per second and motors per step reveal that the SQL Server configurations on this eight-processor SMP could have continued to perform well under more stress than the Lab's network could provide. The Northbridge NX801's ability to handle a workload of nearly 1000 users is impressive. After I make some serious upgrades to the benchmarking network, I can't wait to determine SQL Server's true upper limits on the next eight-processor system the Lab has access to.

What's Ahead
NT Server 4.0, Enterprise Edition performed better than NT Server 4.0, UNL Processor Edition, and SQL Server 6.5, Enterprise Edition did not fare well against SQL Server 6.5, Standard Edition. However, my tests don't demonstrate conclusively that SQL Server 6.5, Standard Edition works better in an enterprise setting than SQL Server 6.5, Enterprise Edition works. Because I didn't have access to a server with more than 2GB of RAM, I don't know whether the 4GT technology affected my tests.

In future articles, the Lab will test other eight-processor SMP systems that have more than 2GB of RAM. I hope those tests will provide more conclusive information about SQL Server 6.5, Enterprise Edition's performance. The Lab's goal is to determine whether upgrading hardware and software to an eight-processor SMP system is a cost-effective method of improving enterprise performance.