SQL Server Magazine

Does the performance of your company's SQL Server application mean the difference between a pink slip and a promotion for you? You don't have to spend much time with applications that rely on database systems to realize how important database server performance is to overall application performance. But buying bigger, faster database servers can dramatically increase the application's operational costs. Cluster configurations to support high application availability cost even more.

Using IP-based load balancing is an easy, scalable solution to meet increasing demands on Web servers. But the traditional solutions for increasing SQL Server capacity aren't so neat. Scaling up your SQL Server—using a bigger server with more and faster processors—is an expensive proposition. Microsoft's Windows 2000 Datacenter Server will be a solution for DBAs who choose the relative simplicity of scaling up their SQL Server systems. Scaling out a SQL Server infrastructure—adding more SQL Server systems that work in parallel—is a more complicated strategy, but scaling out is sometimes the only solution when a bigger server doesn't fit your budget or when the biggest server available still doesn't meet your needs.

To handle increasing Web traffic, Web-based businesses have been forced to design applications that spread their processing loads among multiple parallel SQL Server machines. The greater application complexity of such a solution translates directly into greater application costs. Scaling out by using federated databases on linked servers isn't simple either; such a step requires careful planning and vigilant, ongoing database administration.

SQL Server 2000 Enterprise Edition's native support for Virtual Interface (VI) Architecture network adapters might be the tonic you need to boost your database application's performance. VI Architecture—an industry standard developed by a group of vendors including Compaq, Intel, and Microsoft—implements a thinner, more efficient protocol stack. This design translates into fewer interrupts and fewer CPU cycles expended for network I/O. In addition, SQL Server 2000 can then use the spared CPU cycles to satisfy additional application data requests. (See the sidebar "Virtual Interface Architecture," page 56, for a more detailed description of VI.)

In the SQL Server Magazine and Windows 2000 Magazine Lab, I compared the throughput capacity of a SQL Server 2000 computer using SysKonnect's 1000Base-SX Gigabit Ethernet with the same server using two implementations of the VI Architecture specification that SQL Server 2000 Enterprise Edition supports—Giganet's cLAN and Compaq's ServerNet II. My test application was an Internet Server API (ISAPI) implementation of Doculabs' @Bench e-commerce benchmark specification. The test goal was to drive the SQL Server system to nearly 100 percent utilization, observe peak application throughput (as measured in transactions per second), and record the throughput difference between the Gigabit Ethernet and VI Architecture network implementations.

To establish a baseline of application throughput with Gigabit Ethernet, I used SysKonnect fiber-optic Gigabit Ethernet cards and an NPI Cornerstone12g Gigabit Ethernet switch. Then I tested the two VI Architecture-based NICs that SQL Server 2000 supports (cLAN and ServerNet II). After switching from Gigabit Ethernet to a VI Architecture card, I measured an average overall throughput gain of 37.5 percent for the test application workload—a significant improvement by any standard. Graph 1 shows a summary of the test results. Table 1 shows a pricing summary for the products I tested.

The overall performance improvement from switching to a VI Architecture NIC will vary depending on your application's specific characteristics. The benefits of using VI Architecture come from saving CPU cycles for network I/O. So, applications that require SQL Server to work hard to generate a small result set will yield a smaller performance improvement than applications that use simple table lookups to generate a relatively larger result set.

I encountered several problems while testing the VI Architecture-based products, including application failures that were probably caused by problems in the VI Architecture NIC driver or OS protocol stack. In the Giganet cLAN test, an event log message reported an error at the cLAN switch, and I was unable to run the test successfully with more than 80 virtual users. In the ServerNet II test, the Web application made Microsoft IIS hang with a particular Network Load Balancing (NLB) configuration. Tests with both transport protocols returned other Web page errors with no message specifying the cause for the errors. However, the Gigabit Ethernet tests ran well, without the errors I saw when I tested the VI products. (Although I expect that the vendors will successfully resolve the problems I encountered in my testing, I suggest that you stress-test your application and server configuration before you put a VI Architecture-based system in production.)

Testing Overview
Because the test goal was to determine the effect on SQL Server throughput capacity of various SQL Server-to-Web server network connectivity options, I needed a test application that would heavily use SQL Server services across a network connection. I chose a high-performance Visual C++ (VC++) and COM+ /ISAPI implementation of the Doculabs @Bench e-commerce benchmark specification, which Microsoft wrote. Quest Software's Benchmark Factory generated the load. The application ran on four load-balanced Web servers supported by one SQL Server machine; 47 computers running the Benchmark Factory Agent simulated application users.

I configured the benchmark tests to drive the SQL Server 2000 system's CPU utilization to nearly 100 percent of capacity and measured application throughput (transactions per second—tps) and transaction response time. I installed each network configuration in turn, and I ran a workload that varied the number of virtual users from 10 to 150 in increments of 10.

In the Giganet cLAN product tests, I was unable to complete the tests successfully at user loads of more than 80. At user loads of 90 through 120, where test results hovered near maximum throughput, some transactions took more than a minute to complete. In my tests of other network configurations, no transaction took longer than 30 seconds to complete. I uncovered clues to the problem in the System Event Logs of two Web servers. A message classified as informational—which usually means that the reported event has no negative impact on system operations—reported that the Web-server-to-cLAN switch link failed and that the link reinitialized. When I reported these problems to Giganet, the company sent me a replacement switch. But retesting with the new switch didn't resolve the problem.

The successful test runs clearly showed that the transaction rates I observed at the highest load levels were at or near peak throughput, as measured by transactions per second. Benchmark Factory performance monitoring results showed that SQL Server's CPU utilization was near 100 percent during these successful test runs. The maximum throughput I've reported here for cLAN is an average of the top 4 scores for test iterations that ran normally. Giganet representatives told me that they suspect that a known problem, which the company is trying to solve, caused these errors.

During the ServerNet II test, when the application was running in the medium (pooled) IIS process isolation mode, the application terminated unexpectedly and reinitialized. Microsoft's IIS technical support group found that one of the Compaq VI support modules, vipl.dll, was always involved in the problem. I discovered that when I altered the NLB configuration from Affinity of None to Single Affinity, the test ran normally. (The Single Affinity setting causes one member of the NLB cluster to handle all work originating from a particular IP address rather than rotating work among cluster members, as with the Affinity of None setting.) At press time, Compaq was investigating the problem.

My testing with Gigabit Ethernet using SysKonnect fiber-optic cards and NPI's Cornerstone12g switch was flawless. "Testing VI Architecture," which you can find by entering InstantDoc ID 15913 at http://www.sqlmag.com, provides a detailed description of the test procedures, including a diagram of the test network configuration.

Giganet cLAN
cLAN implements a 1.25-gigabit, full-duplex connection through shielded copper cables up to 30 meters long. I used the cLAN1000 host adapters, which are 32/64-bit, 33MHz PCI adapters for 3.3-volt or 5-volt slots. My test network required only one 8-port cLAN5000 switch to connect the four Web servers and the SQL Server machine. A 30-port cLAN5300 switch is also available. You can cascade switches for larger networks. Giganet supports fault-tolerant configurations that use redundant switches and redundant adapters in each server. Giganet also supplies management console software for network monitoring.

Giganet's implementation of the VI Architecture specification is somewhat different from that of other VI product suppliers. Because Giganet implements the functions of the VI Architecture specification's Reliable Reception component (the most reliable communications mode defined by the standard) on the cLAN card as a fundamental part of cLAN's communications protocol, the cLAN1000 doesn't support the lesser modes—Unreliable Delivery and Reliable Delivery. In addition, Giganet doesn't implement Remote Direct Memory Access (RDMA) Read in cLAN. Instead, cLAN initiates RDMA Write from the other end of the connection.

   Prev. page   [1] 2     next page