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

Download

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

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

There are three main write-intensive operations Sybase SQL Server performs: checkpoints, housekeeper and transaction log writes.



During checkpoints, Sybase SQL Server generates a large number of write requests in a short time interval. The main objective of the checkpoint is to write all dirty pages from the data cache to the disk in the shortest time possible by simultaneously writing as many pages as possible. This technique requires careful tuning considerations for the Compaq SMART and SMART-2 SCSI Array Controllers.

In some environments, the amount of write activity that the checkpoint generates can

saturate the Array Accelerator, thus interfering with read requests pending at the controller. Proper tuning of the housekeeper can help alleviate this problem.



The housekeeper, if enabled, waits until it detects idle time in the Sybase SQL Server then begins to write dirty pages from the data cache to the disk at a lower priority than the checkpoint process. The main objective of the housekeeper is to write as many dirty pages from the data cache to the disk in the shortest time possible with minimal impact on the throughput of the database application. Unlike a checkpoint process which must write all dirty pages from the data cache to disk before terminating, the housekeeper writes only what it can during idle times. If the system is idle for a long enough period of time the housekeeper may actually have written all dirty pages from the data cache to disk. When this occurs the housekeeper notifies the checkpoint process and requests that a checkpoint be performed on the database.



The transaction log activity is composed exclusively of sequential writes and does not saturate the Array Accelerator. However, the difference between writing to the Array Accelerator and writing to the disk drives can be substantial. For optimal performance the Array Accelerator should be enabled.

The Array Accelerator improves performance of all write activity, including the transaction log writes, the housekeeper writes and the checkpoint writes.

Fault Tolerance Considerations

Your transaction log needs to always be protected against a disk failure. Protecting your database devices is at your own discretion. Most mission critical sites protect both the transaction log and the database devices and they usually choose hardware-based fault tolerance. Non-mission critical sites are often satisfied with the protection of the transaction log only and do frequent backups/dumps.

You have three choices of protecting your data:

■

Use a RAID-based disk controller such as the Compaq SMART and SMART-2 SCSI Array Controllers

■

Use Windows NT-based RAID

■

Use Sybase SQL Server-based mirroring/duplexing.

Keep in mind that in an OLTP environment the transaction log activity is characterized by sequential writes, whereas the database activity is composed of random reads and writes. In a DSS environment the transaction log activity is characterized by sequential writes, whereas the database activity is composed mostly of sequential read with few random reads and writes.

Below are some key points to be aware of when choosing the appropriate fault tolerant method. The performance differences between fault tolerance implementations can vary from insignificant to very significant, depending on your configuration and environment. Therefore, we omitted supplying performance differences for the purpose of not misleading our readers. We recommend that you evaluate the various fault tolerance methods using your own applications.

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

1. RAID-based Disk Controllers

Hardware RAID is configurable on a logical volume basis



. Therefore, the whole logical volume is protected by the appropriate fault tolerance. The capacity reduction depends on the size of the logical volume and the type of fault tolerance configured.

Hardware RAID offers the best reliability and automatic recovery. When a drive fails, the



system continues operating. Once the failed drive is replaced, the controller automatically rebuilds the new drive and restores the system to its full fault tolerant capabilities.

Since the data protection occurs at the hardware (controller) level, there is no overhead on



the system processor. This type of RAID is totally transparent to the operating system and the applications. With a CPU-intensive application/environment, such as Sybase SQL Server, hardware-based fault tolerance can provide the best performance. Please refer to

Configuring Compaq RAID Technology for Database Servers

white paper, published by

Compaq (Document Number 267A/0294) for a more complete discussion.

2. Windows NT-based RAID

To take advantage of Windows NT-based fault tolerance, you need to have Windows NT



Server installed. The current versions of Windows NT Workstation do not provide software based RAID.

You have an option of controller duplexing, if your mirrored partition resides on a different



disk controller. Controller duplexing adds redundancy at the controller level, but you must make sure that your mirrored partitions are on physically different drive controllers.

Windows NT fault tolerance is based on logical drives (partitions). The capacity reduction



depends on the size of the logical drive and the type of fault tolerance installed. Windows NT fault tolerance offers reliability in a sense that you will not lose your data when



your disk crashes. Your system will continue running after a drive failure. However, the recovery procedure is not automatic, and you will need to bring your system down.

The data protection occurs at the operating system level, which induces an additional



overhead on the system processor. Consequently, in a CPU-intensive environment, Windows NT fault tolerance will impact performance. Similar to hardware-based fault tolerance, mirroring is preferred to striping with parity.

3. Sybase SQL Server Mirroring/Duplexing

Sybase SQL Server mirroring is based on Sybase SQL Server devices. This offers you the



finest granularity and lowest capacity reduction due to duplicated data. Refer to

Server, System Administration Guide

You have an option of controller duplexing, if you place the mirrored device on a different



for guidelines on choosing which devices to mirror.

Sybase SQL

disk controller. Sybase SQL Server supports both serial and noserial write mode of mirroring. When



utilizing the default, serial write mode, writes to the first device must finish before writes to the second device begin. Changing from serial to noserial write mode allows the writes to both devices to be queued immediately, one to each mirrored device. The noserial write mode will incur less overhead than serial writes that results in a slight performance improvement.

Logical volumes for the Compaq SMART SCSI Array controller are defined using Compaq System Configuration Utility. Logical volumes for the Compaq SMART-2 SCSI Array controller are defined using Compaq Array Configuration Utility

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

Reliability and recovery procedures are very similar to those of Windows NT fault tolerance.



Your system remains running, but you will have to take the system off-line and upon a failed drive replacement you will have to manually remirror the devices.

Again, mirroring through Sybase SQL Server induces an additional processing overhead on



the system, resulting in a lower performance.

For further information on Compaq RAID technology with Sybase SQL Server, refer to the following document:

Configuring Compaq RAID Technology for Database Servers

, February 1994, Document

Number 267A/0294

FAT vs. NTFS

Our testing showed only a slight difference in performance between FAT and NTFS file systems in a Sybase SQL Server environment. It is recommended that NTFS be used for both transaction log and database files for optimal performance, ease of disk management and data recoverability.

Be aware that the Windows NT C2-level security features are only available on NTFS volumes. A complete list of advantages and disadvantages of FAT and NTFS file systems is available in the

Microsoft Windows NT, Concepts and Planning Guide

Compaq System Configuration Utility Considerations

Compaq System Configuration5 utility allows you to select the operating system for each Compaq SMART and SMART-2 SCSI Array controller. This selection allows the configuration utility to choose the best physical characteristics, such as striping block size, number of sectors per track, etc., for a given environment.

Once you choose the

MS Windows NT

option as your selection for the operating system, you will

be presented with the following options:



MS Windows NT 3.1



Choose this option only if you’re running Microsoft Windows NT version 3.1.



MS Windows NT 3.5x



This option provides with best performance under Microsoft Windows NT version 3.5x.



MS Windows NT 3.5x - Maximum Boot Size



This option provides you with maximum boot partition size available under Microsoft Windows NT version 3.5x. The partition limitations outlined below still apply.

WARNING: Changing controller characteristics such as the operating system through the Compaq System Configuration utility causes the controller to apply different physical characteristics to the drive array. As a result, you may lose data on such a drive array. If you have existing data on such a drive array, and need to change these controller characteristics, we strongly suggest you back up your data completely before doing so.

Current partition limitations are as follows:



Windows NT 3.5x limit the boot partition to 4GB, regardless of the file system.

Make sure you obtain the latest available version of Compaq System Configuration Utility from Compaq.

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

Non-bootable partitions to be formatted with FAT file system are limited to 4GB. Non-



bootable partitions to be formatted with NTFS file system are typically limited by the physical space.

Network Planning

The objective of the influence performance of Sybase SQL Server. This section is somewhat incomplete at this time, and as we continue investigating various areas of performance, optimization and interpretability, additional information will be released when it becomes available. The information in this section is being provided to give the reader a heads up to evaluate alternative implementations. Results presented below are with Windows NT workstations and a Windows NT server, and may not be indicative of other workstation/server combinations.

Network Characteristics of a Sybase SQL Server Environment

On-line transaction processing (OLTP) environments typically generate a large number of small packets, and the amount of data being transmitted between the clients and the server is relatively small. On the other hand decision support systems (DSS) environments typically generate a large number of large packets, and the amount of data being transmitted between the clients and the server is relatively large. Many applications heavily utilize stored procedures, which not only decrease processing at the server, but also reduce network traffic. Stored procedures are invoked by passing the name of the stored procedure along with its parameters to the server. Even though stored procedures can return any amount of data, a typical stored procedure returns only a very small result set.

However, using stored procedures or not, the bulk of OLTP and DSS processing still occurs at the server, with the physical network layer (network interface controller or cables) seldom having a significant influence on the overall performance.

From the hardware perspective, you should have a 16- or 32-bit bus-master network card installed in the server to minimize the processing overhead associated with non-bus master network cards.

Network Planning

section is to provide information on how the network can

Check to insure that the Sybase SQL Server system is not the server responsible for being the Default Gateway, Primary WINS Server, Secondary WINS Server, or Domain Name Service Server (DNS). Having these services enabled on the Sybase SQL Server system will impact the overall performance of the server and increase the amount of network traffic to this server. When possible, move these services to another Windows NT server on the network.

Refer to the how to configure the server and the clients to support a particular network protocol.

Network Protocols: Packet Sizes

Sybase SQL Server communicates with clients through packets have a default size of 512 bytes. Applications can manipulate the packet size using the DB-Library dbsetlpacket() call; with BCP and ISQL utilities you can change the packet size using the

[-a packetsize]

SQL Server for the clients to connect properly to the server. From sp_configure alter the setting for

maximum network packet size

Utilizing larger TDS packet sizes also requires that you increase the memory requirements for the Sybase SQL Server kernel. The sp_configure value for this kernel memory allocation and will need to be increased. Please refer to the

Server, Systems Administration Guide additional netmem

Sybase SQL Server, Installation Guide for Microsoft Windows NT

Tabular Data Stream

parameter. The TDS packet size also needs to be incremented at the Sybase

to match or exceed the maximum application packet size.

additional netmem

for more guidelines on computing the amount of

required.

for information on

(TDS). TDS

controls the size of

Sybase SQL

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

For larger data transfers between the clients and the server, such as BCP loads and unloads, it may be beneficial to increase this packet size to a higher value (2048 bytes and higher). In our testing of an OLTP parts order entry system a TDS packet size of 4096 bytes provided the best throughput. In a DSS environment a TDS packet size of 16384 bytes provided the best throughput. Experimentation is necessary to determine the best packet size for your environment.

Multiple Network Protocols

Windows NT and Sybase SQL Server support multiple network protocols. From the Control Panel, Network, Bindings, select Server to list the protocol bindings that are used by this Server. Take note of the protocol bindings listed. The Server will accept whatever protocol the Workstation/Client logs in with if it is listed here. Remove or disable any unused protocols to reduce the amount of system memory used by the network handlers. Adjusting the protocol binding order of the Server, Network Bindings has no effect on the server performance.

If the same physical hardware platform that is running Sybase SQL Server is also acting as a Workstation/Client running Sybase SQL Monitor and/or Sybase Server Manager check the Workstation Network Bindings. If more than one protocol binding is listed, highlight and promote the protocol that is being utilized by Sybase SQL Monitor and/or Sybase Server Manager to the top of the listing. This will reduce the chances for failed logins and network protocol timeouts on a heavily loaded system.

Refer to the

Microsoft Windows NT Server, System Guide

for more detail on this subject.

Performance Monitoring and Tuning

A concise method exits for the altering and verifying the many tuning parameters that effect the performance of Sybase SQL Server release 11 for Windows NT. All configurable items in System 11 are stored external to the database in an ASCII format data file of the name, servername.cfg. This makes it possible to quickly fix any configuration errors with a text editor like Notepad without the hassles of previous version via the buildmaster utility.

System Processor Utilization, Monitoring and Optimization

This section provides information on parameters that you can set to influence performance from the system processor perspective and tools you have available to monitor the behavior of the system.

Optimizing Windows NT & SQL Server Operations

By default, all Sybase SQL Server threads fall into the from 1 to 15. Most threads in the system, including applications, fall into this class, and the Windows NT dispatcher can vary the priority of these threads to optimize system response time. Another priority class exists, called 31, and is used by time-critical applications requiring immediate processor attention. Once a thread runs in the variable priority class, its priority cannot be dynamically raised to the real-time class, and vice versa.

real-time priority class

variable priority class

, with priorities ranging from 16 to

, priorities ranging

The Tasking option setting determines how Windows NT schedules base thread priorities. The default setting of base thread priority at level 9 and the background base thread priority will be level 7. If you choose

Foreground Application More Responsive than Background

thread priority is at level 8, background base thread priority remains at 7. If you choose the setting

Foreground and Background Applications Equally Responsive

background base thread priority will be at level 7.

Best Foreground Application Response Time

schedules the initial foreground

the initial foreground base

the initial foreground and

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

If the server is dedicated to Sybase SQL Server, change the setting to

Application Response Time

. If the server is not dedicated to Sybase SQL Server and is being

used to run other application programs or printer services then change the setting to

and Background Applications Equally Responsive

Refer to the

Windows NT Resource Kit, Volume 4, Optimizing Windows NT

Best Foreground

Foreground

for more detailed

information on this topic. By default, Sybase SQL Server threads

are started as variable priority class threads having a base priority of 7-9. You can increase the base priority of the Sybase SQL Server threads before starting Sybase SQL Server with the -P option. This starts the Sybase SQL Server threads with a base priority of 13-15. We recommend using the -P option to boost Sybase SQL Server priority on dedicated Sybase SQL Server MultiProcessor systems. On dedicated Sybase SQL Server UniProcessor systems do not use the -P option.

The Sybase -P parameter will have a greater effect on the performance of your server than the Tasking option setting.

Server Optimization should be set to

Maximize Throughput for Network Applications

this tells Windows NT memory manager to allow Sybase SQL Server to manage its’ own memory area(s). What you really are doing is altering the way Windows NT controls its’ memory optimization routine from disk cache management to process working set management.

Refer to

Microsoft Windows NT Resource Kit, Volume 4, Optimizing Windows NT

Windows NT Server, System Guide

for a more detailed discussion.

and

Microsoft

The tunable parameter schedspins is now renamed as runnable process search count. The default value of 2000 is satisfactory for most environments and no longer needs to be tuned except in rare cases.

The sp_configure

max online engines

setting, determines how many CPU resources should be dedicated exclusively to Sybase SQL Server. It is recommended on dedicated Sybase SQL Server MultiProcessor systems that

max online engines

value be set equal to the number of CPUs in the system. This option greatly increases performance, but since it can degrade performance of nonSybase SQL Server applications running on the same machine, it needs to be used only on dedicated Sybase SQL Server MultiProcessor systems. Experimentation will be necessary to obtain the best performance for your non-dedicated database server environment. If you set the value for

max online engines

higher than the number of CPUs installed in your server, you will still be able to run Sybase SQL Server but your overall system performance will be severely impacted.

Unnecessary Services

You may turn off or stop all unnecessary services running on the server, since they add a processing overhead on the system processor(s). Even though you can run with as few as two services ( disable services such as

Network DDE DSDM, Schedule

Server

and

Sybase SQL Server

ClipBook Server, Computer Browser, Messenger, Network DDE,

), we recommend also running

EventLog

. You can

and others if you do not need them. You can turn these services off through Control Panel/Services. Always monitor the behavior of your system, and reverse any changes that impact performance or impair stability of your system.

You can check the priority of Sybase SQL Server threads via the Performance Monitor, Thread, Priority

Base and Priority Current for the Instance of sqlsrvr -> 0 through 15.

Some software packages may alter this setting so after installing any new software or changing any other Windows NT settings check to insure that the proper setting of “Maximize Throughput for Network Applications” is enabled.

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

CPU Performance Monitor Objects and Their Meaning

This section provides you with some important Performance Monitor objects that you can monitor to gain insight into the system processor utilization.

Object: Counter:

The Privileged Mode and User Mode threads) running on the system. This counter is very useful in determining whether or not you have a CPU bottleneck. Consistent CPU utilization of over 95% on all processors is a safe indicator of a CPU bottleneck. In such case, add another system processor or upgrade the existing processor(s) to the next generation processor(s). You may also review your logical database design to make sure it is designed as efficiently as possible. As stated elsewhere, hardware tuning and upgrades can only partially compensate for an inefficiently designed database.

Object: Counter:

In a dedicated Sybase SQL Server environment, time the system processor is spending executing NT kernel commands, much of this time is associated with processing Sybase SQL Server I/O requests. time the system processor is spending executing user applications, such as Sybase SQL Server.

If Sybase SQL Server finds all (or most of the frequently used) objects in the data cache, and does heavy processing with these objects (such as table joins), very little I/O is generated. As a result,

% Privileged Time

as 85-95%. On the contrary, if Sybase SQL Server generates a large amount of I/O,

be substantially higher (30-40%) and

Processor

% Processor Time

Processor

% Privileged Time, % User Time

counter monitors the percent of CPU utilization of all threads (both

can be low, may be in the 5-15% range, whereas

% Privileged Time

% User Time

is typically the percentage of

% User Time

will be substantially lower (60-70%).

is the percentage of

% User Time

% Privileged Time

can be as high

will

Both of these counters are very useful in determining how different types of operations are utilizing the system processor(s). If your system is spending too much time doing I/O, you may need to further investigate the disk subsystem and how to relieve it of some I/O. You may also need to add more memory. If your system is spending most time doing Sybase SQL Server computing, you may want to investigate denormalization, reduction of the number of joins, horizontal partitioning, or adding/upgrading your system processor.

Memory Utilization, Monitoring and Optimization

This section provides information on parameters that you can set to influence performance from the system memory perspective and tools you have available to monitor the behavior of the system.

Window NT Paging: Detection and Avoidance

In a dedicated Sybase SQL Server environment, Window NT Paging can occur when you allocate too much memory to Sybase SQL Server and the remaining memory does not satisfy Windows NT. In a non-dedicated Sybase SQL Server environment, memory allocated to other programs also plays a significant role.

Keep in mind that to reduce or eliminate paging, you can either add more physical memory or reduce the amount of memory allocated to your processes, such as Sybase SQL Server. We recommend starting with allocating a minimum of 16 megabytes to Windows NT and the rest to Sybase SQL Server and carefully monitoring the behavior of the system.

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

Object: Counter:

Memory

Page Faults/sec

This counter monitors total page faults by all running processes. A page fault occurs when a process makes a reference to a virtual memory page that is not in its working set in the memory. You can also monitor Page Faults/sec per individual process, such as the number of page faults per second Sybase SQL Server generates. On a dedicated Sybase SQL Server system, Sybase SQL Server-generated page faults and total page faults per second will often be identical.

You should have very little or no paging once the system reaches a steady state; occasional paging prior to steady state is often acceptable.

Sybase SQL Server Tuning Parameters Memory

The correct amount of memory allocated to Sybase SQL Server varies from one environment to another. As a rule of a thumb, the more objects (tables, indexes, etc.) you can cache, the better performance you will have. If you can’t cache them all, attempt to cache the more frequently accessed objects in the following priority clustered indexes, non-clustered indexes and data pages.

The Sybase Logical Memory Manager feature in System 11 permits the refinement of which database objects you want to cache, how much space to allocate for the cache, and how to manage the cache. To determine the initial size of a dedicated named cache, use the following procedure.

1. Use sp_spaceused to determine the size of the database object

2. Edit the server.cfg file to establish the dedicated named cache object for the database object [Named Cache:c_tempdb]

cache size = 2M cache status = mixed cache cache status = HK ignore cache

[2K I/O Buffer Pool]

pool size = 2M wash size = 512K

3. Use sp_bindcache to associate the database object to the dedicated named cache

4. Use sp_helpcache to verify the cache binding

Please refer to the Sybase SQL Server System Administration Guide for more in depth information on the tuning of the LMM.

Dbcc Memusage

is a very useful tool in monitoring what objects are in the Sybase SQL Server cache. Remember that out of the total memory allocated to Sybase SQL Server, the Sybase SQL Server engine itself will allocate enough memory for itself and its required data structures, and then will divide the remaining memory between the procedure cache and data cache according to the

procedure cache percent

parameter. Also remember that Sybase SQL Server allocates

memory in 2 Kbyte pages.

For accurate results, set the database to single user mode before issuing this command. Also it maybe necessary to issue the dbcc traceon (3604) command to redirect the output to your command window instead of the Sybase SQL Server errorlog.

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

Dbcc Memusage

Sybase SQL Server memory allocation at startup



Memory used by 20 largest objects in the data cache



Memory used by 12 largest stored procedures, triggers, views, rules and defaults in the



procedure cache

Run

Dbcc Memusage

cache. Refer to the information on how to use this feature.

Procedure Cache

The amount of procedure cache you need for your stored procedures, triggers, views, rules and defaults depends on their number and size. Keep in mind that multiple users accessing the same stored procedure, for example, will cause Sybase SQL Server to store one copy of the same stored procedure for each user.

Since reading stored procedures from disk to the procedure cache is costly, you want to keep all needed stored procedures in the procedure cache. In other words, you want to prevent stored procedures not in the procedure cache from pushing out those stored procedures already in cache because your procedure cache is insufficient to hold them all.

Use

Dbcc Memusage

If you have more than 12 stored procedures in the procedure cache, the 12 largest ones, and you will have to get a little creative to find out if you have enough procedure cache. Below is an example.

reports the following very useful information:

occasionally to determine what is and what is not in the Sybase SQL Server

Sybase SQL Server System Administration Guide

to monitor the 12 largest procedures in the procedure cache.

for more detailed

Dbcc Memusage

only shows

1. Make a copy of one of your largest stored procedures, call it a different name, and execute it. This step will place this stored procedure in the procedure cache. Use verify this.

2. Execute the rest of your stored procedures, except for the one created in step 1, several times.

3. Use

Dbcc Memusage

procedure cache. If you have enough procedure cache, your dummy stored procedure should have remained in the list of top 12. If you don’t have sufficient procedure cache, your dummy stored procedure should have aged out and has been pushed out of the procedure cache by other stored procedures.

to determine which 12 largest stored procedures remained in the

Dbcc Memusage

Disk Subsystem Utilization, Monitoring and Optimization

This section provides information on parameters that you can set to influence performance from the disk subsystem perspective and tools you have available to monitor the behavior of the system.

Recovery Interval

Sybase SQL Server uses the checkpoint. Sybase SQL Server internally computes the frequency of the checkpoint based on the

recovery interval

database(s) would be recovered within the time specified by the For example, in a heavy transaction processing environment, a

(minutes) means that at the present transaction rate Sybase SQL Server would recover the database within 5 minutes after restarting from a crash. Taking into consideration the present

value and the transaction rate to guarantee that in case of a system crash the

recovery interval

parameter to calculate how often to perform a

recovery interval

parameter.

value of 5

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

transaction rate, Sybase SQL Server would perform the checkpoint approximately every 2 minutes. Also if

trunc. log on checkpoint

is set then a checkpoint will occur every minute

Refer to the

Disk Subsystem Planning

section for more detailed information on how

checkpointing can effect disk throughput.

Diskperf

The

diskperf

option9 allows you to monitor the disk subsystem activity. It is very useful when monitoring performance of the drive subsystem. If this option is disabled, performance monitor will not be able to monitor low level disk-related activity, such as

LogicalDisk

and

PhysicalDisk

counters. Having this option enabled slightly degrades performance. Enable

diskperf

only when needed,

then disable it to get maximum performance. You can enable or disable

diskperf

command from the system prompt. When using the Control Panel, set start at boot time to enable. You must restart your system for the

using Control Panel/Devices or by issuing the

diskperf

to automatically

option to become

diskperf -y|n

effective.

Disk Performance Monitor Objects and Their Meaning

You can monitor or

Disk Transfers/sec

have the

diskperf

LogicalDisk

option enabled, which slightly reduces performance.

Note that resetting the

Object:

Logical Disk

. Monitoring

diskperf

- and

PhysicalDisk

LogicalDisk

-related objects, such as

and

PhysicalDisk

-related objects requires you to

option requires you to reboot your machine.

Avg. Disk sec/Transfer

Counter:

The

Avg. Disk sec/Read, Avg. Disk sec/Write

Avg. Disk sec/Read

and

seconds for read or write operations from or to a disk, respectively. Use the following guidelines when optimizing the disk subsystem. Do not have more average

disk seconds per read or write operation than the values in the table below.

Transaction Log Access (with the Array Accelerator enabled)

Database Access (with the Array Accelerator enabled)

If your values are much higher than those suggested in the above table, you may want to increase the speed of your disk subsystem by adding more drives or using faster drives.

Object: Counter:

The

Logical Disk

Disk Transfers/sec

counter monitors the rate of read and write operations on the disk. It is important to realize that if you have a Compaq SMART or SMART-2 SCSI Array controller and several drives allocated to a logical volume, this counter monitors the total number of disk

The

diskperf

option is described in a greater detail in the

Avg. Disk sec/Write

counters monitor the average number of

Avg. Disk sec/Read Avg. Disk sec/Write

n/a <=10msec

20-40msec <=10msec

Microsoft Windows NT Resource Kit

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

transfers per logical volume. To calculate number of disk transfers per second per drive, you must divide the

Use the following guidelines when optimizing the drive subsystem. You should not have more I/O requests (disk transfers) per second per drive than the values in the table below.

Disk Transfers/sec

value by the number of drives in the logical volume.

1.0GB drives

(Max I/Os per

Second)

Sequential Writes (Transaction Log) Random Reads/Writes (Database Access)

If your values are much higher than those suggested in the above table, you should increase the throughput of your disk subsystem by adding more drives or using faster drives.

150

≈

30-40

≈

Sybase SQL Server

Runserver Command File and the Windows NT Registry

The recommended method for altering the startup parameters for Sybase is through the Sybase Configuration Server Utility. But because there are other methods of editing the Runserver file and the Windows NT Registry mainly Edit and Regedt32, an inconsistency may exist between the two. When this happens the manner in which Sybase runs is now predictable. If you start Sybase SQL Server via the Services Manager or the Control Panel, Services menu, the Registry values are utilized. If you manually start Sybase SQL Server at the DOS prompt or use the Servername.bat file then the parameters specified will take effect. If the Registry has an entry for a parameter not specified at the DOS prompt or the Servername.bat file then the Registry parameter value(s) will be used as the default.

2.0GB drives

(Max I/Os per

Second)

160

≈

4.0 GB drives

(Max I/Os per

Second)

200

≈

60-65

≈

The Sybase Server Configuration Utility does not verify or resolve any inconsistencies between the Runserver file and the Registry. You will need to manually check that the command line parameters specified in the C:\SQL11\install directory Servername.bat file match the Registry entries for arg0-argx under the heading of HKEY_LOCAL_MACHINE, SOFTWARE, Sybase, Server, Servername, Parameters. In the Servername.bat file, if multiple entries are listed for the same parameter, the last parameter is the one utilized. In the Registry, if multiple entries are listed for the same parameter, the arg with the highest number associated with it is the one utilized.

This also applies to the entries for Backup Server and Monitor Server.

Sybaudit Performance Considerations

To avoid having the Sybase Audit database file becoming the limiting factor on your Sybase SQL Server it is suggested that it be treated in the same manner as a transaction log file. Place the Sybase Audit database files on a separate disk partition, use Compaq SMART or SMART-2 SCSI Array Controller with the Array Accelerator enabled and RAID-1 via hardware.

Compaq Insight Manager

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

Use the Compaq Insight Manager to remotely monitor the health of the Compaq Server running Sybase SQL Server. Compaq Insight Manager can provide pre-failure warnings of potential hardware problems. This allows the scheduling of preventative maintenance during non-peak hours of operation.

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

Conclusion

We would welcome feedback from your configurations and experiences to improve our information products in the future. Please send us any comments or suggestions on the attached form, attach addition sheets if necessary. This will help us tailor the future information products to your needs, and will enable us to make future revisions of this document and related new information products available to you.

Check List of Recommendations

Initial recommendations for Sybase SQL Server 11.0.1 system running on Microsoft Windows NT 3.51:

design your database with performance in mind from the start

•

start with minimum of 32MB RAM

•

check revision levels of Compaq NT SSD files, update to latest revisions

•

when possible, use System Configuration Utility setting of MS Windows NT 3.5x as

•

operating system choice for SMART and SMART-2 SCSI Array Controller(s) when possible, avoid installing Sybase SQL Server on the Primary Domain Controller,

•

Default Gateway, Primary WINS Server, Secondary WINS Server or Domain Name Service Server

dedicate the server to Sybase SQL Server, use other servers for file and print services

•

use SMART and SMART-2 SCSI Array controller(s) with array accelerator enabled on data

•

file volumes for increased performance use SMART and SMART-2 SCSI Array controller(s) with the array accelerator enabled on

•

transaction log volumes for increased performance use SMART and SMART-2 SCSI Array controller(s) with the array accelerator enabled on

•

sybaudit volumes for increased performance use SMART and SMART-2 SCSI Array controller hardware fault tolerance features, if fault

•

tolerance is needed use Windows NT software fault tolerance with caution, CPU overhead can impact

•

performance use NTFS for transaction log, sybaudit and database files

•

use Sybase database mirroring with caution, CPU overhead can impact performance

•

use database striping methods to balance workload across multiple controllers

•

standardize on one network protocol and frame type

•

if not required, do not load multiple frame types

•

Start sqlsrvr with the -P option on dedicated multi-processor systems

•

On dedicated Sybase SQL Server systems set Tasking to

•

Response Time Responsive

otherwise set to

Foreground and Background Applications Equally

Best Foreground Application

set Server Optimization to

•

use Compaq Insight Manager to monitor system hardware, CPU and EISA bus utilization

•

Maximize Throughput for File Sharing

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

use the Windows NT Performance Monitor to monitor CPU, memory and disk utilization

•

enable diskperf only when necessary to monitor disk performance

•

do not set Sybase

•

on dedicated multi-processor systems set

•

user connections

higher than necessary

max online engines

equal to the number of CPUs

installed in the system monitor database utilization and access methods to determine system bottlenecks

•

use stored procedures to reduce network traffic and server CPU load

•

increase size of TDS packets to speed up activities and reduce CPU workload when

•

performing BCP and DSS activities install Sybase sybaudit device on separate disk partition for optimal performance

•

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

User Registration/Evaluation Form

Please fill out and return to us this registration/evaluation form to help us keep you up to date with future revisions of this document and related new information products. Your effort will help us improve the quality of the future information products.

Name: Title: Company: Address:

Phone Number:

Please evaluate the quality of this document:

Excellent Poor

Technical Accuracy Organization Clarity Completeness Timeliness

12345 12345 12345 12345 12345

Please indicate the type of environment you have at your site:

Operating Systems RDBMS Processing Type

Microsoft Windows NT



SCO UnixWare



Novell NetWare



SCO Open Server



IBM OS/2



Other:



Please indicate the type of information you would like us to provide in the future:

Topic Operating Systems RDBMS

Configuration and Tuning



Capacity Planning



Integration Information



Competitive Analysis



Systems Management



Other:



Additional Comments:

Sybase System 11



Microsoft SQL Server



Oracle 7



Other:



Microsoft Windows NT



SCO UnixWare



Novell NetWare



SCO Open Server



IBM OS/2



Other:



On-Line Transaction Processing



Decision Support



Batch Processing



Other:



Sybase System 11



Microsoft SQL Server



Oracle 7



Other:



Return to:

Database Engineering Compaq Computer Corporation MailCode 090803 20555 SH 249 Houston, Texas 77070

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

Specifications and Main Features

Frequently Asked Questions

User Manual