Charles Vogt
NSTL's database-server tests measure the speed of transactions and queries against a database of books, authors, orders, payments, and shipments. In the tests, the three SQL Server/operating-system combinations were tested on the same hardware--an IBM PS/2 Model 95 configured with 32 MB of RAM and a pair of IBM 400-MB SCSI drives--one for the database, and one for the transaction log. The tests were both transaction- and query-oriented.
Each transaction test stresses the database server in a different way. For example, the multitable selects make the order-entry tests fairly processor-intensive. The payments tests, which feature simple record-selection criteria and in-place updates of existing records, are by contrast more
disk-intensive. The transaction tests consist of the following:
ISBN Order. The test program generates a random ISBN number, selects the book, and identifies the author using a three-table join.
Author Order. The program generates a list of authors using the LIKE operation. After the user selects an author, the program generates a list of titles by that author.
Title Order. The test program selects book titles using a partial title key and the SQL LIKE operator. It selects a title from those returned and retrieves additional information from that book's record.
Payment. The test program generates a random order number and then selects an order record and its corresponding entries and updates the order record.
Shipment. The program finds the lowest order number for an unshipped order. It selects the order record for that number and all corresponding entries, and the order record and the entry records for book records with ISBN numbers that correspond to the selected entry record.
The query tests quantify a server's ability to handle the types of information requests you see in a corporate environment. The tests consist of the following:
Query 1. This is a single-table query with range search. The test program retrieves order number, customer name, payment status, and shipment status for a range of ZIP codes. The query returns approximately 2000 rows. During the six- and 12-user tests, one-third of the users execute this query while the others execute payment transactions.
Query 2. This single-table query is based on a list of values. The test program retrieves an order number, customer name, payment status, and shipment status for a list of 100 order numbers generated at random. During the six- and 12-user tests, one-third of the users execute this query while the others execute payment transactions. Note: NT would not process this query. The list of 100 order numbers creates a long SQL statement; NT truncated that statement, and the query failed. Thus, we didn't
report results for this test.
Query 3. This test uses a three-table join. For a range of ISBN numbers, the query lists the book title, the quantity in stock, the number shipped, and customers who have ordered each book. The ISBN numbers, generated at random, span 500 values. During the six- and 12-user tests, one-third of the users execute this query while the others execute order transactions.
Query 4. This test uses a five-table join. For a range of order number values, the program lists the customer name, all books ordered, and each book's authors. The highest order number, generated at random, falls within 200 values of the lowest order number. Because no values are selected that are updated by transactions, background transactions are not run with this query.
Query 5. The final query is a grouped query with an outer join. For a range of ISBN numbers, the program lists the total number of orders placed for that book, the total number of books ordered, and the total cost of the books orde
red for all books with two or fewer orders. To include those books with no orders placed, the program executes an outer join between the book table and entry tables. During the six- and 12-user tests, one-third of the users execute this query while the others run order transactions.
About the Results
For transactions, the NT version was a clear winner. For queries, the NLM (NetWare loadable module) version and the NT version both exhibited very good performance. The OS/2 version did relatively poorly, even when compared to a cache-constrained NT.
Why did we use OS/2 1.3? SQL Server for OS/2 remains a 16-bit application, and Microsoft recommends OS/2 1.3, rather than OS/2 2.1, as the best operating system on which to run it. Since OS/2 1.3 could use only half the RAM on the Model 95, that left at most 13 MB of RAM for SQL Server. So we ran the NT tests twice--once with the standard 16 MB of RAM and once with 13 MB.
SQL Server for NT obtains a high cache-hit ratio on our test (approximat
ely 98 percent), even with 24 active-client sessions. When the amount of memory allocated to SQL Server is set the same on NT and OS/2 1.3, the two versions perform similarly on the transaction-processing tests. The NT version performs better on queries, however. One explanation may be its more sophisticated use of threads. Another reason may be that it incorporates some query optimization that appears in Sybase System 10 but is not present in the OS/2 (or NetWare) versions of SQL Server.
SQL Server On NetWare, OS/2, AND NT: Transactions
16 MB OF RAM 13 MB OF RAM
SYBASE MICROSOFT MICROSOFT MICROSOFT
SQL SQL SQL SQL
SERVER SERVER 4.2 SERVER 4.2B SERVER 4.2
NLM FOR NT FOR OS/2 FOR NT
One station
ISBN order 2.2 1.5* 1.8 1.5*
Author o
rder 3.7 2.0* 2.4 2.0*
Title order 3.0 1.7* 2.0 1.8*
Payment 0.3 0.2* 0.2* 0.2*
Shipment 0.5 0.4* 0.5 0.4*
Six stations
ISBN order 2.8 2.4* 2.3* 2.7
Author order 4.5 3.8* 3.6* 4.0
Title order 3.9 3.6* 3.9* 3.9*
Payment 0.4 0.3* 0.2* 0.3
Shipment 1.1 1.0* 1.0* 1.1
12 stations
ISBN order 4.5* 4.5* 5.3* 5.3*
Author order 6.8* 6.9 7.1 7.0*
Title order 5.9* 6.4 8.0 6.9*
Payment 0.5* 0.6 0.4* 0.6
Shipment 2.0 1.6* 1.9* 2.1
24 stations
ISBN order 8.9 8.8* 11.6 10.9*
Author order 13.7 13.2* 15.3* 15.9
Title order 11.4* 12.6 15.8 15.5*
Payment 0.8* 1.1 0.7* 1.1
Shipment 3.8 2.9* 3.9* 4.2
System: IBM PS/2 Model 95 (a 33-MHz 486DX with 32 MB of RAM, two IBM 400-MB SCSI hard drives, and an IBM SCSI drive controller).
Numbers represent average response time in seconds.
* = best.
SQL Server On NetWare, OS/2, And NT: Queries
16 MB OF RAM 13 MB OF RAM
SYBASE MICROSOFT MICROSOFT MICROSOFT
SQL SQL SQL SQL
SERVER SERVER 4.2 SERVER 4.2B SERVER 4.2
NLM FOR NT FOR OS/2 FOR NT
One user
Quer
y 1 6.80* 10.43 11.48** 10.20
Query 3 24.00** 15.46* 17.57 16.40
Query 4 51.10** 28.22* 46.49 31.10
Query 5 7.60** 5.30 5.45 5.20*
Six users
Query 1 8.00* 21.16 27.22** 26.30
Payment with query 0.70** 0.48 0.52 0.40*
Query 3 36.10* 39.86 129.46** 76.50
ISBN order with query 3.20 2.91* 7.38** 3.40
Query 4 56.40 40.16* 74.87** 50.40
Query 5 9.40* 10.39** 10.12 9.90
ISBN order with query 5.50* 12.93 38.23** 15.20
12 users
Query 1 13.20* 44.22 54.63** 55.70
Payment with query 1.30** 0.86 0.88 0.70*
Query 3 60.20* 112.11 328.62** 157.00
ISBN order with query 5.40* 6.36 41.66** 10.40
Query 4 76.00 74.47* 179.24** 86.80
Query 5 12.30* 17.33 19.45** 16.70
ISBN order with query 43.60* 74.99 115.71** 69.50
System: IBM PS/2 Model 95 (a 486DX/33 with 32 MB of RAM, two IBM 400-MB SCSI hard drives, and an IBM SCSI controller).
Numbers represent average response time in seconds.
* = best
**= worst.
Charles Vogt is manager of performance testing at NSTL. You can reach him on the Internet or BIX at
editors@bix.com
.
How We Tested SQL Server on NetWare,OS/2, and NT
