Hp COMPAQ PROSIGNIA 720, COMPAQ PROSIGNIA 200, COMPAQ PROSIGNIA 500, COMPAQ PROLIANT 2000 Configuration and Tuning of Sybase System 11 for Microsoft Windows NT

Configuration and Tuning of Sybase System

11 for Microsoft Windows NT on Compaq

Servers

White Paper

________________________________________________________________

Prepared By

Database Engineering

Compaq Computer Corporation

May 1996

NOTICE

The information in this publication is subject to change without notice. COMPAQ COMPUTER CORPORATION SHALL NOT BE LIABLE FOR TECHNICAL OR

EDITORIAL ERRORS OR OMISSI ONS CONTAINED HEREI N, NOR FOR I NCIDENTAL OR CONSEQUENTIAL DAMAGES RESULTING FROM THE FURNISHING, PERFORMANCE, OR USE OF THIS MATERIAL.

This publication contains inform ation prot ected by copyright . No part of t his publicat ion may be photocopied or reproduced in any for m without prior written consent from Compaq Computer Corporation.

The software described in this guide is furnished under a licens e agreement or non disclosure agreement. The software may be used or copied only in accordance with the terms of the agreement.

This publication does not constitute an endorsement of the product or product s that were tested. The configuration or configurations tested or described may or may not be the only available solution. This test is not a determination of product quality or correctness, nor does it ensure compliance with any federal, state, or local requirements. Compaq does not warrant products other than its own strictly as stated in Compaq product warranties.

Product names mentioned herein may be trademarks and/or registered trademarks of their respective companies.

 1996 Compaq Computer Corporation.

Compaq, Fastart, Compaq Insight Manager, Systempro, Systempro/LT, SmartStart, and NetFlex

Registered United States Patent and Trademark Office.

ProLiant, ProSignia, Qvision, and Systempro/XL are trademarks of Compaq Computer

Corporation.

Microsoft, MS-DOS, and Windows are registered trademarks of Microsoft Corporation and

Windows NT and Windows 95 are trademarks of Microsoft Corporation.

Configuration and Tuning of Sybase System 11 for Microsoft Windows

NT on Compaq Servers

First Edition (143A/0596)

Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers Page i

Table of Contents

Introduction......................................................................................................................................... 1

Planning and Installation..................................................................................................................... 1

Domain Controller, Backup Domain Controller or Server............................................................. 1

System Processor Planning...........................................................................................................2

UniProcessor and MultiProcessor Support from Compaq ....................................................... 2

Processor Scalability..............................................................................................................2

Memory Planning......................................................................................................................... 3

Initial Memory....................................................................................................................... 3

Virtual Memory.....................................................................................................................4

Disk Subsystem Planning.............................................................................................................. 4

Array Accelerator: Its Function and Benefit in a Sybase SQL Server Environment................. 4

Checkpoints, Housekeeper and Transaction Log Writes............................................ 4

Fault Tolerance Considerations.............................................................................................. 5

1. RAID-based Disk Controllers............................................................................... 6

2. Windows NT-based RAID.................................................................................... 6

3. Sybase SQL Server Mirroring/Duplexing .............................................................6

FAT vs. NTFS .......................................................................................................................7

Compaq System Configuration Utility Considerations............................................................ 7

Network Planning......................................................................................................................... 8

Network Characteristics of a Sybase SQL Server Environment...............................................8

Network Protocols: Packet Sizes ............................................................................................ 8

Multiple Network Protocols ................................................................................................... 9

Performance Monitoring and Tuning................................................................................................... 9

System Processor Utilization, Monitoring and Optimization.........................................................9

Optimizing Windows NT & SQL Server Operations.............................................................. 9

Unnecessary Services............................................................................................................. 10

CPU Performance Monitor Objects and Their Meaning.......................................................... 11

Memory Utilization, Monitoring and Optimization....................................................................... 11

Window NT Paging: Detection and Avoidance ...................................................................... 11

Sybase SQL Server Tuning Parameters.................................................................................. 12

Memory................................................................................................................... 12

Dbcc Memusage....................................................................................................... 13

Procedure Cache ...................................................................................................... 13

Disk Subsystem Utilization, Monitoring and Optimization ........................................................... 13

Recovery Interval................................................................................................................... 13

Diskperf................................................................................................................................. 14

Disk Performance Monitor Objects and Their Meaning.......................................................... 14

Sybase SQL Server.............................................................................................................................. 15

Runserver Command File and the Windows NT Registry.............................................................. 15

Sybaudit Performance Considerations........................................................................................... 15

Compaq Insight Manager.................................................................................................................... 15

Conclusion .......................................................................................................................................... 17

Check List of Recommendations................................................................................................... 17

Configuration and Tuning of Sybase

System 11 for Microsoft Windows NT on

Introduction

The purpose of this document is to share the knowledge acquired by Compaq Systems Engineers in the area of configuration and tuning of Sybase SQL Server release 11 on Compaq servers. It is our desire to deliver the best technical information possible on a specific topic in a timely manner and in a highly usable format. Any comments, suggestions and feedback are appreciated.

The information presented in this document is applicable to Sybase SQL Server release 11.0.1 running on Microsoft Windows NT Server Edition 3.51. Because some tuning techniques might differ between releases, we strongly encourage you to upgrade to the latest available versions of Windows NT, Windows NT Software Support Diskette (NT SSD) from Compaq, and Sybase SQL Server.

The reader should already be familiar with the installation and reconfiguring of Windows NT Server, Compaq SSD for Windows NT, and Sybase System 11 for Windows NT.

Planning and Installation

This section discusses the planning and installation of Microsoft Windows NT on your network server. These planning considerations consist of discussions on topics such as the system processor, initial and virtual memory, disk subsystem, and network planning.

Compaq Servers

Domain Controller, Backup Domain Controller or Server

During the installation of Windows NT, you can designate a server to be a Primary Domain Controller , Backup Domain Controller, or a Windows NT server.

The Primary Domain Controller provides a centralized management location for the network and performs the validation of all user logins and permissions. To provide the best performance, avoid running Sybase SQL Server on a Primary Domain Controller server. The performance impact can vary depending upon the number of user logins, level of security, and the available system bandwidth.

The Backup Domain Controller and administration as the Primary Domain Controller. The Backup Domain Controller server is part of a domain and incurs overhead in maintaining a replicate copy of the Primary Domain Controller server.

CAUTION:

Controller running Sybase SQL Server is elected (automatically) and promoted to Primary Domain Controller. Do not install Sybase SQL Server on this server. The impact on the server can vary depending upon the number of user logins, level of security, and the available system bandwidth.

A Server can be member of a Windows NT Domain or a Windows NT Workgroup. As a member of a Windows NT Domain, the Primary Domain Controller system handles all of the

You must have a Domain Controller installed and active on the network before you install a Backup

Domain Controller.

, does not incur as much overhead in the network management

If the Primary Domain Controller goes off-line; the Backup Domain

Page 2 Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers

administrative tasks for that domain. This leaves all of the server resources available for running Sybase SQL Server. This is the optimal system to install and run Sybase SQL Server. As a Windows NT Workgroup server, the management of the entire network, users, and shared resources is on a per system basis. The throughput of the Sybase SQL Server depends on the level of requests from other users on the network.

To receive optimal performance of Sybase SQL Server on your Windows NT network, ideally use four servers. The first server would be configured as the Primary Domain Controller with Windows Internet Name Service (WINS), Dynamic Host Configuration Protocol (DHCP), and Gateway system. This system would incur all of the overhead of user logins, network gateway functions and user permissions. The second server would be configured as the Backup Domain Controller. The third server, configured as a server participating in a Windows NT Domain, would be dedicated as the Sybase SQL Server. The fourth server, also configured as a server participating in a Windows NT Domain, would provide shared file and print services.

For additional information on Domain Controllers, refer to the

and Planning Guide

Windows NT Server, Concepts

System Processor Planning

The objective of this section is to outline the configurations of the Compaq ProLiant Family of Servers and to provide some performance information necessary to determine the best configuration for your environment. As is the case throughout the document, certain level of experimentation and internal performance testing in your own environment can be expected.

UniProcessor and MultiProcessor Support from Compaq

Windows NT utilizes different versions of the following files for single processor and multiple processor systems:

■

HAL.DLL

■

KERNEL32.DLL

■

NTDLL.DLL

■

NTOSKRNL.EXE

■

WINSRV.DLL

The Windows NT installation program automatically selects the correct versions of these files according to the number of processors installed. However, if you upgrade your system from one system processor to two or more, you must install the multi-processor support files

the

SETUP.CMD

file on the Windows NT SSD from Compaq

by running

Processor Scalability

This procedure is not necessary if you already have a MultiProcessor configuration and you add another

processor.

The Compaq Support Software Diskette for Windows NT 3.5x (NT SSD) contains the latest drivers and utilities that enable you to take advantage of specific capabilities offered on Compaq products. Always make sure you have applied the latest available NT SSD. You can verify the currently installed versions of these files via the File Manager, Properties option.

Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers Page 3

The following chart shows the performance increases when adding additional processor boards to a ProLiant 4500 system. The test was based upon an OLTP test of a parts inventory control database.

System 1 1 for Windows NT Scalability

Proliant 4500 5/166

3.0

2.5

2.0

1.5

1.0

Transaction Rate (normalized)

0.5

0.0 1234

Number of Processors

NOTE: Under Windows NT 3.51, if you need to run with 3 processor boards installed in your system you will need to edit the Windows NT Registry. Edit the \hkey_local\machine\system\currentcontrolset\services\lanmanserver\parameters entry to Edit, Add Value, RawWorkItems REG_DWORD = 0x100 to the registry. Save the changes, shutdown and restart Windows NT for the changes to take effect.

Memory Planning

The objective of this section is to provide you with starting point for system base memory and to expose important considerations regarding the system memory on Compaq servers.

Initial Memory

The

Sybase SQL Server Installation Guide for Microsoft Windows NT

memory requirement for running Sybase SQL Server is 32 megabytes, which includes the memory requirements for Windows NT.

: For satisfactory performance, do not run Sybase SQL Server on a system with

NOTE

less than 32 megabytes of memory.

states the minimum system

Some general guidelines to use to calculate starting memory values are shown in the following formula. Refer to the

Sybase SQL Server, System Administration Guide

for additional memory

requirements and calculations.

Sybase SQL Server memory =

Page 4 Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers

5 megabytes for database kernel and data structures + (2% * total data and index space) + (51 kilobytes * number of users)

The 51 kilobytes for each user includes 10 kilobytes for procedures. Make appropriate calculations to determine the percentage value of procedure cache to total memory using the result for the "procedure cache" value of sp_configure.

Memory requirements are environment-specific. In the “Memory Utilization, Monitoring, and Optimization” section found later in this document, we provide suggestions on how to detect and correct low memory situations.

Virtual Memory

During the initial installation of Windows NT, a paging file is setup automatically on the default boot drive with a size of physical memory + approximately 64 megabytes. In a properly configured and tuned Sybase SQL Server environment, no paging should occur after the initial startup of the dataserver. Leave the paging file at its recommended default size.

Sybase System 11 now uses the Windows NT paging file in place of its’ own kernel memory region file. The paging file is accessed by Sybase SQL Server during the initialization and startup of the database. After the database is online there should be little or no i/o activity to the Windows NT paging file.

Refer to the

Microsoft Windows NT Server, Concepts and Planning Guide

for a more in-depth

discussion of the paging file.

Disk Subsystem Planning

The objective of this section is to provide information on the benefits of the Compaq SMART and SMART-2 SCSI Array Controller Array Accelerator, the pros and cons of various fault tolerance methods, the selection of the file system, and how disk space is consumed by the combination of Sybase and Windows NT system level files.

Additional information on disk subsystem configuration and Compaq drive array technology can be found:

■

Configuring Compaq RAID Technology for Database Servers

Compaq TechNote, P/N 184206-001

■

Microsoft Windows NT Server, System Guide

■

Microsoft Windows NT Server, Concepts and Planning Guide

■

Sybase SQL Server, System Administration Guide

Array Accelerator: Its Function and Benefit in a Sybase SQL Server Environment

The main function of the Array Accelerator is to boost performance of write operations by storing data in cache memory on the controller. The drive controller informs the host that the write operation has completed, and writes the data to the hard drives at a more convenient time. The onboard rechargeable battery pack and mirrored memory banks guarantee data integrity at all times.

Without the Array Accelerator, the application must wait until each write request is written out to the disk. Writing to a disk device can be slower than posting the write request in the Array Accelerator, thus resulting in very different performance characteristics.

Checkpoints, Housekeeper and Transaction Log Writes

+ 15 hidden pages