Membership Directory Capacity and Performance Analysis Update

Incorporating SQL Server 7.0 and Xeon Architecture

August 1999

Microsoft Corporation

Glossary

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/16

P2EM = 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.

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.

Figure 1

Transactions

The following are the transactions that were tested for the purposes of this document.

Tested Membership Directory Transactions

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

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.

Chart 1: Processor Utilization Rate (4-Processor)

Chart 2:Maximum Rate (4-Processor)

Chart 3: Processor Utilization Rate (1-Processor)

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

Modify Transactions

Modify-Add

Modify-Replace

Search Transactions

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

Chart 5: Maximum Transaction Rates for Scalability

Information in this document, including URL and other Internet web site references, is subject to change without notice.  The entire risk of the use or the results of the use of this resource kit remains with the user.  This resource kit is not supported and is provided as is without warranty of any kind, either express or implied.  The example companies, organizations, products, people and events depicted herein are fictitious.  No association with any real company, organization, product, person or event is intended or should be inferred.  Complying with all applicable copyright laws is the responsibility of the user.  Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document.  Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 1999-2000 Microsoft Corporation.  All rights reserved.

Microsoft, Windows and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries/regions.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.