Site Server - Update Incorporating SQL Server 7.0 and Xeon Architecture
Update Incorporating SQL Server 7.0 and Xeon Architecture
August 1999
Glossary
Membership-specific Terms |
Meaning |
|---|---|
User |
An individual user connected to a service. |
Add record |
Adding a user to the Membership Directory. |
Concurrent user |
User active on the system (a subset of total users calculated based on user profile). |
Attributes |
Specific information associated with a file or an object. |
Capacity and Performance Terms |
Meaning |
Pentium II-equivalent MHz (P2EM) |
A unit of measure for processor usage. A computer with a 400-MHz Pentium II processor delivers 400 Pentium II-equivalent MHz, at 100 percent processor usage. A computer with two 400-MHz Pentium II processors delivers 800 Pentium II-equivalent MHz at 100 percent processor usage. |
Pentium Pro-equivalent MHz (PPEM) |
A unit of measure for processor usage. On a 200-MHz Pentium Pro processor, 100 percent CPU utilization delivers 200 PPEMs. A computer with two 200-MHz Pentium Pro processors delivers 400 Pentium Pro-equivalent MHz at 100 percent processor usage. |
Per transaction cost |
The amount of computer resources used by a single transaction, the primary factor being the processor usage of an individual transaction. |
Transaction rate |
The number of transactions per second that a computer can accommodate at a given point in time. |
Processor clock speed |
Speed rating of a computer's processor measured in megahertz. |
Data point |
Testing term referring to a point at which a measurement is taken. This can be a point in time, a transaction, or a set load. |
Context switching |
A Microsoft® Windows NT Performance Monitor counter that measures the quantity of dynamic switching occurrences per second, between running processes, conducted by a processor or a group of processors. This dynamic switching occurs in order to run several processes simultaneously. The running processes must share processor time in order to run all threads and processes simultaneously. Context switching results in a slight reduction in the activity of the processes involved, and is not user controlled. |
Transaction Cost Analysis (TCA) |
TCA is a methodology developed at Microsoft for estimating system capacity requirements. It can be used to generate performance and scaling analysis of specific transactions. These analyses are used to assist customers with scaling recommendations to increase productivity within their networks and Internet services. |
Introduction
This document is an update to the Microsoft Site Server 3.0 Membership Directory Capacity and Performance Analysis white paper first included in the Microsoft® Commercial Internet System (MCIS) Resource Kit version 1.1. The original document analyzed the capacity and performance of the Membership Directory using Microsoft® SQL Server™ 6.5 database architecture with Pentium Pro hardware architecture.
Recent changes in the design of both hardware and software have made it possible for computers to operate with more speed and efficiency. In this update, the capacity and performance of the combination of Pentium II Xeon hardware architecture and SQL Server 7.0 will be analyzed as it pertains to the Membership Directory. This document will not address all of the testing performed in the original analysis, but will provide data and analysis for some of the key transactions for the Membership Directory.
Pentium II-equivalent MHz (P2EM)
P2EM represents the processor usage percentage of a transaction or transactions, for a Pentium II processor with respect to the clock speed of the processor.
For example, a 400-MHz Pentium II processor used at 100 percent capacity would produce 400 Pentium II-equivalent MHz or 400 P2EM. A four-processor Pentium II 400-MHz computer with a transaction rate of 16 transactions per second would have the following result:
P2EM= processor clock speed * number of processors utilized by the computer / transaction rate.P2EM = (400(MHz) * 4(processors))/16(transactions/sec)P2EM = 1600/16P2EM = 100 (P2EM units)
Note Remember that the transaction rate is variable.
P2EM is important in conducting transaction cost analysis (TCA)3 in that it provides one of the key elements in the TCA formula. For additional information about Transaction Cost Analysis, refer to the Capacity Model for Internet Transactions white paper included in the MCIS Resource Kit, version 2.5.
Per Transaction Cost
The overall cost of a transaction includes any disk and memory usage minus system overhead. The per-transaction cost is calculated as shown in the following equation:
P2EM + %disk utilization + %memory usage - base system usage = per transaction cost
The per-transaction cost is the actual resource usage of an individual transaction, and is used to perform TCA.
Advances in Hardware Technology
New hardware technology has increased the rate of disk transactions. This has resulted in dramatic increases in throughput. The latest upgrades for disk controllers and hard drives have increased the disk read and write capacity to as many as 18,000 transactions per second in a properly-configured fiber channel system. The resulting I/O headroom has removed the bottleneck for the disk-intensive operations of the Membership Directory. Massive throughput on the SQL Server computer can now be obtained by adding more spindles and faster individual disks.
Fiber controllers are now running processes and containing more cached memory. This enables greater scheduling efficiency for read and write operations to the hard drive.
Xeon Processor
The Xeon processor improves on the Pentium II architecture. It enables programs to execute more efficiently and reliably by providing the following performance enhancements:
Increased processing speed
Higher caching capacity
Error checking and correcting
Functional redundancy checking
A system management bus
Faster clock rate
Larger L1 cache
Xeon-capable motherboards utilize a 64 bit 100-MHz PCI bus. This enables greater data transfer through the processor than in previous testing with lower-end processors.
Advances in Software Technology
Software continues to improve the efficiency, reliability, and availability of the Membership Directory.
SQL Server 7.0
The following improvements, included in SQL Server 7.0, increase the functionality of the Membership Directory:
Dynamic adjusting of configuration settings relieves the administrator of the task of manually adjusting file sizes.
A redesigned query processor provides improved support for large databases and complex queries.
Row-level locking reduces the contention for access to data and indexes. SQL Server 7.0 automatically adjusts the resources it uses for larger databases.
Microsoft Data Access Components (MDAC) 2.1
Microsoft® Data Access Components (MDAC) 2.1, which is included in SQL Server 7.0, includes the database connectivity pieces that allow the LDAP server to communicate with SQL Server. This code has been improved to scale linearly to multiple processors.
Important Do not install the LDAP Service on the SQL Server computer unless you have also installed ADSI version 2.5. Otherwise, the Site Server LDAP Service cannot function correctly with MDAC 2.1, which installs with SQL Server 7.0. For more information, see Using Site Server with SQL Server 7.0, available in the MCIS Resource Kit version 2.5.
Windows NT Service Pack 4
Microsoft® Windows NT® Service Pack 4 improves scalability. It reduces ACL lookup context switching by increasing the multi-thread efficiency of the memory allocation device.
LDAP Processor Scaling
The test scenarios utilized two server computers with the following configurations.
Web Server |
Use |
Provides customer access to content. Security is provided by Membership Authentication or Windows NT Authentication. |
|
CPU |
4 x 400-MHz Pentium II (Xeon chip) |
|
Memory |
2 GB of RAM |
|
Disk |
2 x 4.5-GB SCSI, Raid 0 (Internal) |
|
Network |
100 BaseT (switched) |
|
Software |
Windows NT 4.0 Service Pack 4 |
LDAP Server |
Use |
Interfaces with database containing membership data and schema. |
|
CPU |
1 and 4 x 400-MHz Pentium II (Xeon chip) |
|
Memory |
2 GB of RAM |
|
Disk |
4.5-GB SCSI, Raid 0 (Internal) |
|
Network: |
100 BaseT (switched) |
|
Software |
Windows NT 4.0 Service Pack 4 |
|
|
|
SQL Server |
Use |
Contains Membership Directory contents in database objects. Provides physical drive space for Membership Directory schema and data. |
|
CPU |
4 x 400-MHz Pentium II (Xeon chip) |
|
Memory |
2 GB of RAM |
|
Disk |
SCSI System drive and SQL Server TempDB: 2 GB, Raid 0 |
|
Network |
100 BaseT (switched) (High-speed fiber-channel connection between SQL Server computers and drive arrays) |
|
Software |
SQL Server 7.0 Service Pack 1 (For information about SQL Server 7.0 Service Pack 1, go to https://www.microsoft.com/downloads/details.aspx?FamilyID=79c3b1bf-21be-404c-a921-139a6a17f8eb&DisplayLang=en ) |
Procedures
The testing procedures were conducted in the same manner as the original testing with the SQL Server 6.5/Pentium Pro architecture. The following is an overview of that test procedure.
Testing was conducted by utilizing the InetMonitor load generation tool, which is available with the MCIS Resource Kit. InetMonitor makes use of a command script that simulates the user profile of a common user and the type of transaction being tested. The tool then connects to the designated port of the LDAP server with the assigned user load and runs through the command script for each user. While InetMonitor simulates the user actions through the command script, the LDAP server recognizes the load as real connections.
While the load is being generated by InetMonitor, certain responses of the LDAP server computer and SQL Server computer are monitored with the Performance Monitor (PerfMon) tool that comes with Microsoft® Windows NT®. This tool provides objects and counters that can be selected and monitored by the graph lines and data boxes in the PerfMon user interface. The objects may also be logged to a file and appropriate data extrapolated, as needed. Both methods were used during this testing. The graph function was used to obtain the maximum transaction rate for each transaction with respect to the user load. The logging function was used to obtain data over an extended period of time under varying user loads.
A test pass was conducted for each transaction. The resulting data was parsed by exporting the log file from PerfMon to an Excel spreadsheet as a tab-delimited file. This file was divided into sections by user load, and each section was averaged. The averages are used in the comparison tables.
.gif)
Figure 1
Transactions
The following are the transactions that were tested for the purposes of this document.
Tested Membership Directory Transactions
Transaction |
Description |
|---|---|
Add Record |
Add a 20-attribute user to the Membership Directory |
Base Object Search |
Search for a user by common name (cn) |
Modify Add Attribute |
Add one attribute to an existing user record in the Membership Directory |
Modify Replace Attribute |
Change the value of one attribute in an existing user record in the Membership Directory |
User Profile
The User Profile identifies the actions of a typical user logging on to the system and performing typical transactions. The following profile captures typical Membership Directory transactions. The database contains 200,000 actual user records in a single partitioned Membership Directory. The Membership Directory was populated using the load generation tool, InetMonitor 3.0. The InetMonitor 3.0 load generation tool also generated the user load during the testing. This is the same user profile used for the SQL Server 6.5/Pentium Pro architecture testing.
Typical Membership Directory Transactions
Elements |
Value1 |
Description |
|---|---|---|
Add record |
3% |
Adds one user with 20 additional attributes to the Membership Directory. |
Modify record |
12% |
Retrieves, modifies, and replaces existing record. |
Base search |
75% |
Retrieves record, returning all attributes. |
Operations per visit |
10 |
|
User life cycle |
20 min. |
Average time user remains connected conducting various operations. |
1 The term value, as used here, refers to the functional weight given to each transaction.
Scalability and Performance
Processor Calculations
The LDAP server is the primary component utilized for the Membership services. The first step in determining the processor utilization of the LDAP server is to determine the per-transaction rate for each of the defined transactions. Both architectures yield a different processor calculation for each transaction type.
The following tables are generated using Microsoft® Excel, based on the user profile previously described.
The following tables and graphs show the per-transaction load comparison between the two architectures in quad-processor and single-processor modes.
|
4-Processor 200-MHz SQL Server 6.5 |
4-Processor 400-MHz SQL Server 7.0 |
|---|---|---|
|
Intel Pentium Pro |
Intel Pentium II Xeon |
Transaction |
Valid |
PPEM |
Valid |
P2EM |
Base Object |
1 - 180 |
2.6782 |
1 – 796 |
1.85 |
Modify Add |
1 - 110 |
1.4841 |
1 – 673 |
1.57 |
Modify Replace |
1 - 110 |
2.9435 |
1 – 269 |
1.44 |
Add |
1 - 4 |
2 |
1 – 146 |
3.27 |
.gif)
Chart 1 Processor Utilization Rate (4-Processor)
.gif)
Chart 2 Maximum Rate (4-Processor)
|
1-Processor 200-MHz SQL Server 6.5 |
1-Processor 400-MHz SQL Server 7.0 |
|---|---|---|
|
Intel Pentium Pro |
Intel Pentium II Xeon |
Transaction |
ValidRange |
PPEM |
ValidRange |
P2EM |
|---|---|---|---|---|
Base Object |
1 - 100 |
1.9903 |
1 - 602 |
2.40 |
Modify Add |
1 - 75 |
1.5 |
1 - 477 |
2.52 |
Modify Replace |
1 - 80 |
1.9686 |
1 - 437 |
2.72 |
Add |
1 - 4 |
5.7469 |
1 - 250 |
2.43 |
.gif)
Chart 3 Processor Utilization Rate (1-Processor)
.gif)
Chart 4 Maximum Rate (1-Processor)
SQL Server Data
SQL Server performance relies heavily on data disk performance. The best way to measure this is to observe the disk read and disk write rates. Performance can be judged by how many disk transactions occur per second and what effect the transactions have on the CPU.
The following tables show the processor and physical disk usage for each transaction. The combined disk write and disk read rates determine the ability of SQL Server to process storage transactions received from the LDAP server. A single LDAP transaction instigates an average of six disk transactions. All transactions generate read and write events. Add transactions primarily initiate write events, but disk read events occur in a normal state during any SQL Server operation. A comparison between SQL Server 6.5 and SQL Server 7.0 is not shown in the tables because no verification script was conducted for SQL Server 7.0. However, the following tables show true processor usage at specific concurrent user levels. This information is useful in determining system scalability issues.
Add Transactions
AddTransactions/Sec |
CPU% Processor |
DiskWrites/Sec |
DiskReads/Sec |
Clients |
|---|---|---|---|---|
6 |
12 |
383 |
155 |
50 |
3 |
18 |
533 |
73 |
60 |
14 |
30 |
814 |
10 |
100 |
17 |
34 |
931 |
2 |
120 |
20 |
42 |
1071 |
2 |
160 |
21 |
46 |
975 |
12 |
200 |
Modify Transactions
Modify-Add
Modify-AddTransactions/Sec |
CPU% Processor |
DiskWrites/Sec |
DiskReads/Sec |
Clients |
|---|---|---|---|---|
70 |
6.3 |
1322 |
19 |
80 |
140 |
13 |
470 |
24 |
160 |
330 |
31 |
677 |
32 |
400 |
375 |
38 |
933 |
30 |
480 |
580 |
53 |
1220 |
31 |
680 |
673 |
62 |
1290 |
34 |
800 |
Modify-Replace
Modify-ReplaceTransactions/Sec |
CPU% Processor |
DiskWrites/Sec |
DiskReads/Sec |
Clients |
|---|---|---|---|---|
37 |
4 |
99 |
52 |
40 |
71 |
7 |
190 |
33 |
80 |
140 |
13 |
396 |
34 |
160 |
206 |
20 |
544 |
33 |
240 |
269 |
26 |
681 |
28 |
320 |
Search Transactions
Base-SearchTransactions/Sec |
CPU %Processor |
DiskWrites/Sec |
DiskReads/Sec |
Clients |
|---|---|---|---|---|
486 |
18 |
10.3 |
354 |
160 |
598 |
21 |
11.7 |
422 |
200 |
715 |
23.6 |
22.7 |
500 |
240 |
788 |
24 |
35 |
570 |
280 |
796 |
33 |
53 |
624 |
320 |
As expected, the Add and Modify transactions make extensive use of disk writes, while the Search transactions primarily make use of disk reads. The optimum transaction rates, disk writes or disk reads/sec will increase linearly with the number of client requests, as will processor usage.
Summary of Scalability and Performance
Based on the data collected during testing, the following assertions can be made about scaling and performance for the Membership Directory:
The Modify and Add transaction rates are increased by the speed of the SQL Server disk subsystem, which utilizes a high-speed fiber channel. Because the Modify and the Add transactions generate write events to the SQL Server database, the high-speed fiber channel disk subsystem is quite effective at allowing more read and write events at a higher yield. This increases the number and speed of the LDAP transactions.
Before Windows NT 4.0 Service Pack 4 was released, an LDAP search that was performed by a user not having administrator privileges on the LDAP computer would reduce multi-processor scalability in the ACL lookup code. This problem was fixed with the addition of Service Pack 4. Using the same usage profile, the Xeon/SQL Server 7.0 architecture shows increased productivity across the board. The transaction rate has increased by a factor of 2 and the more expensive Modify-Add transaction rates have increased by a factor of .5.
Appendix A: LDAP Service Processor Scaling
Every user who accesses an LDAP server computer increases the hardware utilization on that server. The following table shows the maximum rate of the transactions on a server with one and four Pentium II 400-MHz Xeon processors. The same information is shown graphically in Chart 5.
Maximum Transaction Rates per Processor
Transaction |
4 Processors |
1 Processor |
|---|---|---|
Base Object Search |
796 |
602 |
Modify Add |
673 |
477 |
Modify Replace |
700 |
437 |
Add 20 Attribute User |
376 |
249 |
.gif)
Chart 5 Maximum Transaction Rates for Scalability