Testing SMP with SQL Server 6.5

How do you test a symmetric-multiprocessing (SMP) quad-processor server when you don't have the resources to configure a full-blown TCP-C setup? We did it by focusing on processor/memory performance. Our SQL Server test measures a server's processing power in the context of a decision-support or data-warehouse application. Posing complex SQL queries to a cached database, it uses all available memory and CPU power, but negligible disk and network I/O.

We requested test systems configured accordingly -- four 200-MHz Pentium Pro processors (512 KB of L2 cache memory), 256 MB of RAM, but only three or four hard drives install ed on a single SCSI channel -- and set them up with Windows NT 3.51 and SQL Server 6.5. Connected through a 3Com LinkBuilder FMS 100 Fast Ethernet hub, a single 200-MHz Pentium Pro AST Bravo served as a client machine with up to 32 simulated users. The test launched a separate proce ss for each user plus an additional thread to monitor its progress. The test client application was a Win32 console application that uses the standard Microsoft Foundation Classes (MFC) Open Database Connectivity (ODBC) classes.

We kept the database size under 200 MB so that it would cache effectively on a server that has 256 MB of RAM. We configured SQL Server as would be done for a dedicated corporate data-warehouse server: 128 MB for database buffers, 96 MB for a temporary database, and 32 MB left for NT and code space. (The 96-MB temporary database is actually larger than is typical, but it benefited the queries used in our test.) The server and client systems used a TCP/IP connection, wh ich is a popular choice for many corporations.

By default, SQL Server 6.5 leaves one processor free so that NT can operate the user interface and any other server that might be running. We configured it to use all processors (a new parameter with version 6.5). As a side effect, when the server hits 100 percent CPU use (almost constantly with our test), no more users can log in. During testing, only four users at a time could log in and get their data. As these user queries completed, the server logged in other users and processed their queries. With the default setting (n-1 processors), the server was much "fairer"; all users could log in right away and their queries returned at approximately the same time. The overall test performance with the default setting, however, is only 75 percent of that using all processors, because SQL Server uses only three of the four processors at any given time.

We stepped through a number of user and processor configurations to see how the machines would scale. Ide ally, this test should scale linearly for both the number of users and processors. Earlier ports of both SQL Server and Oracle were suboptimal in this regard; consequently, NT received a bum rap about its supposed inability to scale beyond two processors. The current versions of Oracle and SQL Server put this myth to rest. NT scales nicely with either. Our empirical results with NT were close to the theoretical ideal.

---

Jim Hurd is vice president of R&D at National Software Testing Laboratories (Conshohocken, PA). He developed the SQL Server test used here.