Microsoft SQL Server 6.5 Scalability

What a difference a CPU makes

Beyond a certain point, increasing SQL Server's performance is verydifficult--and expensive. You get bottlenecks that you can't get rid of nomatter what you do. SQL Server bottlenecks exist in many places both inside andoutside the physical box. Inside, SQL Server can choke on disk I/O, CPUresources, memory, and network performance (or other interrupt-drivenperipherals). Outside, SQL Server can suffer from client performance(workstation horsepower), overall network capacity, software efficiency, and theever-present user element, dragusperformancedownium. Filtering particlesof this annoying element out of your system is every administrator's nightmare.But you can compensate for bottlenecks by tuning your server to its maximumpotential. (For tuning tips, see "More Easy SQL Server Performance Tips," page 88.)

In the Windows NT Magazine Lab, I tweaked and tuned my way to someperformance numbers that show the differences--or lack thereof--between thePentium and Pentium Pro microprocessors and Windows NT Server 3.51 and NT Server4.0 running SQL Server 6.5 on single, dual, and quad-processor systems. Forinformation about the systems I tested, see "Compaq ProLiant 5000," and "NEC ProServa SH." For information about my testbed, see "The Testbed."

Graphs 1A through 8B show two tests on the different systems. Graphs 1A through 4B show performance for single-read activity, and graphs 5A through 8B show mixed read/write activity. The results were surprising:

Cache
More cache is better. In a test described in "Diamond Flower Doubleshot"(February 1996), Andy Smith found that a dual-Pentium server with a shared 512KBcache (the DFI Doubleshot) performed much worse than a dual-processor serverwith independent cache modules (the ProLiant 4500). The latter is a moreexpensive solution, but it significantly boosts performance. Now you have tochoose how much cache per CPU you need.

My tests for this month's issue showed that a Pentium Pro (the NEC ProServaSH) with the on-chip 512KB Level 2 cache did not produce a significantly fasterresponse time on a higher transaction count than the Pentium Pro with the 256KBon-chip cache under heavy load at the same clock speed. For example, Graphs 8Aand 8B show no marked improvement over Graphs 7A and 7B under heavy load.However, the design of the NEC may have been the limiting factor: For example,the ProLiant 5000 with the 512KB Level 2 cache showed a significant differencein scaleability from the NEC ProServa SH, as you can see by comparing Graph 6Ato Graph 8A. The Compaq ProLiant 4500--a Pentium system with 2MB cache modulesfor each CPU--showed lower TPS and higher average response time (ART) than thePentium Pros, but it still performed well in these tests. As Screen 1 shows, the4500's overall CPU utilization for single read and mixed read/write activity washigher than the ProLiant 5000. However, although the ProLiant 4500 did not haveas high a TPS value or as low a response time, it handled the load withoutbreaking down.

You really see a difference at four CPUs: The ProLiant Pentium Pro with the512KB Level 2 cache wins by a landslide, with the best TPS and response times.The 256KB Pentium Pro has a real problem in two- and four-CPU configurationsunder heavy load, as graphs 3 and 7 show. CPU utilization is 100 percent at 300users on all systems we tested, but the 256KB Pentium Pro system falls down onthe job. The right side of Screen 2 shows that the 256KB Pentium Pro simplycan't handle the load. Toward the end of each test run under heavy load, thesystem basically stopped processing requests.

Intel is aware of this problem and recommends that you use 256KB PentiumPros only in single- and dual-processor servers and workstations. Many vendors,such as NEC and Compaq, have responded by ceasing sales of symmetricmultiprocessing (SMP) enterprise servers with more than two CPUs with 256KBcache. The price differential to get 512KB is more than $2000 per CPU, but theperformance problems you face otherwise make increased cache essential.

Disks
For your data volumes, five or six drives are the effective limit forimproving performance. Compaq has found that more than six drives in a RAID 0stripe set do not improve performance. The limitations on SCSI (six devices onone standard SCSI channel) and performance hits for duplexing multiplecontrollers mean that adding drives to a data volume won't help.

My tests confirmed this result: For my 500MB data set, I saw no systemenhancement going from two, to four, to five drives for the data volume on theCompaq ProLiant 4500. Screen 3 shows %Disk Time maxed out during the mixedread/write tests. You can see that disk usage on the Compaq ProLiant 5000 washigh, although I/Os per second (not shown) were within an acceptable level (lessthan 20 per second, well within the tolerance of 80 I/Os per second for a systemwith a hardware RAID controller).

If you need to increase capacity, look at controllers with multiplechannels on which you can create large arrays, use multiple controllers, or uselarger drives such as 4.3GB Fast and Wide drives or the new 9GB ones. To improveperformance, you can use faster drives and faster controllers, but remember thatdisk I/O is only one component of overall system performance.

Operating System
Microsoft made significant claims about the performance enhancements inWindows NT 4.0 over NT 3.51, including better SMP scaleability, optimization forPentium Pro, and overall speed improvements. Pre-release and beta informationclaimed that NT 4.0 would be as much as two times faster than NT 3.51.

I found that with SQL Server 6.5, NT 4.0 offers only slightly betterperformance than NT 3.51, on either Pentium or Pentium Pro, as TPS graphs 1Athrough 8A show. Is this lack of improvement a limitation of NT or theapplication running on top of it? The answer is both.

Windows NT 4.0 has a smaller footprint and better network throughput thanNT 3.51. But additional features, such as the new GUI and extra services (newnetwork protocols, etc.) negate these advantages.

The OS can go only as fast as the application it runs, and even Microsofthas found that SQL Server doesn't perform better on NT 4.0 than on NT 3.51. Ifan application isn't programmed to use the new features and enhancements in NT4.0, the OS isn't going to run better. Perhaps SQL Server 7.0 or anotherdatabase program will respond differently--we'll just have to wait and see.

What about NT 4.0's optimization for Pentium Pro? This feature means thatNT 4.0 runs faster than NT 3.51 on a Pentium Pro than on the Pentium. But Ididn't get that result. The improvement I saw was only about 5 percent to 10percent, and even that improvement wasn't consistent. On the Pentium Pro withthe 256KB Level 2 cache, NT 4.0 responded with lower TPS and higher ART at highloads than did NT 3.51, as graphs 3A and 7A and 3B and 7B show. In screens 2 and4, you can see that the NEC ProServa SH with 256KB of Level 2 cache performedmuch better under NT 3.51 than under NT 4.0, as evidenced by the smooth scalingof CPU usage. Upgrading to 512KB of cache smoothed performance under both NT3.51 and NT 4.0. You get more features with NT 4.0, but not better performance.

Pentium Pro offers about a 60 percent capacity improvement over the Pentium.Under mixed read/write activity, the ProLiant Pentium Pro ran at 33 TPS, and theProLiant Pentium stopped at about 21 TPS. The differences were about the samefor single-read activity.

Again, CPU utilization shows the differences: The Pentium worked harderwith less performance than the Pentium Pro, as shown in screens 1 and 5. As youcan see in Screen 5, the ProLiant 5000 scaled evenly from low to high userloads, and overall performance didn't degrade at the high end, although responsetimes rose. Machine slowdown under heavy load is expected, but the computershouldn't choke. The Pentium Pro system operated at much lower response times,even under very heavy load. The cache in the Pentium Pro makes a difference toTPS values, but not so much to response time.

Many systems will perform the same under light load, because the system isnot fully stressed. So when you look for differences between architectures orsystems from different vendors, you need to fully stress each subsystem. Forexample, if you want to see how much better one CPU will perform than another,you have to run it at nearly 100 percent CPU usage, or you won't see anydifference in performance. You can see this difference in my TPS graphs. For 150or fewer users, all systems perform similarly, so a faster CPU doesn't buy youperformance improvement. Under light load, upgrading CPUs from Pentium toPentium Pro will give you slightly lower response times, but you won't get ahuge jump in performance. Under heavy load, you can add capacity andsignificantly improve performance by upgrading the CPUs.

SMP Scaleability
The good news is that SQL Server 6.5 on both Windows NT 3.51 and NT 4.0scales, and unlike Exchange Server 4.0 (described in "Optimizing Exchangeto Scale on NT," November 1996), SQL Server scales consistently from one tofour CPUs. Throwing more CPUs at a SQL system is a good investment for handlingmore transactions and reducing response times.

Contrary to what you'd expect, scaleability is the same for both NT 3.51and NT 4.0. And, let me repeat that the 256KB Pentium Pro has problems atfour-CPU configurations.

Under a Microscope
What does all this information mean to you, your upgrading and purchasingdecisions, and your users? Well, how much money do you have?

Upgrading database servers is a complicated issue. With some thoughtfulanalysis, you can cut through the hype and mumbo jumbo to find what's right foryou. Here are some things to keep in mind.

My tests are by no means the be-all and end-all of the SQL world, but theypoint to some interesting issues. If you are not already bottlenecking on CPU ormemory resources, disk I/O, or network throughput, how do you improve databaseserver performance? You can redesign and optimize your SQL applications (bettercode, more efficient procedures) as I describe in "More Easy SQL ServerTuning Tips," you can tune SQL somewhat, or you can throw more memory atthe application. But you'll reach a point after which you can't improveperformance. Only buying more stuff for your server will make a difference. So,what do you buy?

In midrange environments (about 300 users) with moderate data sets (5GB to20GB), extra CPUs are a good investment, as is memory. Get plenty of memory sothat SQL has enough to operate in, maybe even enough to run with TempDB fully inRAM, and enough so that NT does not do any paging. Use Perfmon to watch SQL andsystem counters, and tune appropriately.

Upgrading to Windows NT 4.0 will buy you features but not performance.However, upgrading won't hurt you, either. You need to weigh the cost of newlicensing fees against the easier and more efficient administration of NT 4.0.

Do you need to replace your existing Pentium servers with Pentium Prosystems? This decision depends on how much money you've got. If your Perfmonanalyses don't show that you are already bottlenecking on CPU resources, addingfaster CPUs will give you only an incremental performance improvement. Weighthis option against the cost of replacing an entire server. If you have a heavyload, Pentium Pros will probably improve response times and give you morecapacity, depending, of course, on your transaction mix. Compare graph pairs 1and 5 to 2 and 6 as an example of how a move to Pentium Pros can improveperformance. A mostly disk-intensive environment still won't get a big boostfrom more CPU resource. Be aware of the cache issues described earlier: 200MHzPentium Pros with 256KB cache modules drag in SMP systems.

The decision to add drives is a little easier because you can do so withoutreplacing your whole system. A new, faster disk or RAID controller will help,especially if you use NT's software-based RAID capabilities or have an oldserver. Many new systems ship with Fast and Wide SCSI controllers. Improvingdisk I/O is generally a great way to improve system performance inexpensively,but only to a point (six drives for one data volume). Try breaking up SQL andsystem operations across multiple drives and controllers or SCSI channels.

Beyond upgrading CPUs, you can change the brand of your server. Comparegraph pairs 2 and 6 to 4 and 8. My tests show you'll find performancedifferences between platforms from certain vendors using the same types ofhardware (CPUs, disk controllers, etc.). The scope of the differences depends onyour load and the specific design and architectures of the machines.CPU-to-memory bus width, bus design, and many other factors influence systemperformance. You'll also find differences in upgradeability, feature sets,service and support, and price. The choice depends on your needs.

Capacity vs. Raw Performance or Speed
When analyzing your system's performance or the new system you want to buy,be clear about what you're looking for. Capacity addresses how many users youcan support or how many transactions per second your server can offer.Performance or speed, in my mind, represents how fast your server processesrequests and returns results (i.e., average response time): What is yourprocessing power, and what overhead is available?

You, of course, want both capacity and speed. But you generally can't haveyour cake and eat it, too. You must consider the tradeoffs between how much workyour system can do and how fast your system can do that work.

Graphs 1 through 8 show that, with certain system types and configurations(and, depending on load and transaction types), you can have low response timeswith steadily increasing TPS values. You can throw more work at the system andnot take a performance hit.

You reach a point where the TPS values level off and the response timesstart rising markedly, that point is where your capacity runs out. If you'relucky, your system won't start losing transactions at that point; it will justget slower. Now is when you need to add CPUs, memory, or other resourcesaccording to where you find the first performance bottlenecks.

You can squeeze more performance out of your database server with someintelligent tuning, system configuration, and upgrades. First, look at yoursetup to determine whether you have the optimal configuration. Do you haveenough network bandwidth (do you need to add a segment to the server?) andenough memory allocated to Windows NT and SQL Server (is it balanced?), and arethe disks laid out properly? Next, have you done all you can through software tofully optimize your system: Have you tuned SQL parameters and NT and writtenefficient SQL code?

If you've done all these things, look at upgrading the hardware: Add disks,add memory, add CPUs, or replace the server with a faster architecture (newgeneration Intel chips or RISC). Always look for bottlenecks in your wholesystem, on the client side and server side, before buying new stuff. Be sure thecomponents you buy will actually improve system performance; if you aren'talready maxing out a given resource, upgrading it won't help much.

Corrections to this Article: