Compaq 117755-003 - ProSignia - 740 User manual

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 OMISSIONS CONTAINED HEREIN, NOR FOR INCIDENTAL OR
CONSEQUENTIAL DAMAGES RESULTING FROM THE FURNISHING, PERFORMANCE,
OR USE OF THIS MATERIAL.
This publication contains information protected by copyright. No part of this publication may be
photocopied or reproduced in any form without prior written consent from Compaq Computer
Corporation.
The software described in this guide is furnished under a license 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 products 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.
All rights reserved. Printed in the U.S.A.
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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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

1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
Configuration and Tuning of Sybase
System 11 for Microsoft Windows NT on
Compaq Servers
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.
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 Controller1, does not incur as much overhead in the network management
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: If the Primary Domain Controller goes off-line; the Backup Domain
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
1You must have a Domain Controller installed and active on the network before you install a Backup
Domain Controller.

Page 2 Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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 Windows NT Server, Concepts
and Planning Guide.
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 files2by running
the SETUP.CMD file on the Windows NT SSD from Compaq3.
Processor Scalability
2This procedure is not necessary if you already have a MultiProcessor configuration and you add another
processor.
3The 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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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 11 for Windows NT Scalability
Proliant 4500 5/166
0.0
0.5
1.0
1.5
2.0
2.5
3.0
1234
Number of Processors
Transaction Rate (normalized)
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 states the minimum system
memory requirement for running Sybase SQL Server is 32 megabytes, which includes the
memory requirements for Windows NT.
NOTE: For satisfactory performance, do not run Sybase SQL Server on a system with
less than 32 megabytes of memory.
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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
1. RAID-based Disk Controllers

Hardware RAID is configurable on a logical volume basis4. 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 Sybase SQL
Server, System Administration Guide for guidelines on choosing which devices to mirror.

You have an option of controller duplexing, if you place the mirrored device on a different
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.
4Logical 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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596

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 Configuration5utility 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.
5Make 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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596

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 Network Planning section is to provide information on how the network can
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.
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 Sybase SQL Server, Installation Guide for Microsoft Windows NT for information on
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 Tabular Data Stream (TDS). TDS
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] parameter. The TDS packet size also needs to be incremented at the Sybase
SQL Server for the clients to connect properly to the server. From sp_configure alter the setting
for maximum network packet size to match or exceed the maximum application 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 additional netmem controls the size of
this kernel memory allocation and will need to be increased. Please refer to the Sybase SQL
Server, Systems Administration Guide for more guidelines on computing the amount of
additional netmem required.

Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers Page 9
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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 time-
outs 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 variable priority class, priorities ranging
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 real-time priority class, with priorities ranging from 16 to
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.
The Tasking option setting determines how Windows NT schedules base thread priorities. The
default setting of Best Foreground Application Response Time schedules the initial foreground
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 the initial foreground base
thread priority is at level 8, background base thread priority remains at 7. If you choose the
setting Foreground and Background Applications Equally Responsive the initial foreground and
background base thread priority will be at level 7.

Page 10 Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
If the server is dedicated to Sybase SQL Server, change the setting to Best Foreground
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 Foreground
and Background Applications Equally Responsive.
Refer to the Windows NT Resource Kit, Volume 4, Optimizing Windows NT for more detailed
information on this topic.
By default, Sybase SQL Server threads6are 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,7 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 and Microsoft
Windows NT Server, System Guide for a more detailed discussion.
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 non-
Sybase 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 (Server and Sybase SQL Server), we recommend also running EventLog. You can
disable services such as ClipBook Server, Computer Browser, Messenger, Network DDE,
Network DDE DSDM, Schedule 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.
6You 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.
7Some 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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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: Processor
Counter: % Processor Time
The % Processor Time counter monitors the percent of CPU utilization of all threads (both
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: Processor
Counter: % Privileged Time, % User Time
In a dedicated Sybase SQL Server environment, % Privileged Time is typically the percentage of
time the system processor is spending executing NT kernel commands, much of this time is
associated with processing Sybase SQL Server I/O requests. % User Time is the percentage of
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 can be low, may be in the 5-15% range, whereas % User Time can be as high
as 85-95%.
On the contrary, if Sybase SQL Server generates a large amount of I/O, % Privileged Time will
be substantially higher (30-40%) and % User Time will be substantially lower (60-70%).
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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
Object: Memory
Counter: 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 Memusage8is 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.
8For 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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
Dbcc Memusage
Dbcc Memusage reports the following very useful information:

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 occasionally to determine what is and what is not in the Sybase SQL Server
cache. Refer to the Sybase SQL Server System Administration Guide for more detailed
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 to monitor the 12 largest procedures in the procedure cache.
If you have more than 12 stored procedures in the procedure cache, Dbcc Memusage only shows
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.
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 Dbcc Memusage to
verify this.
2. Execute the rest of your stored procedures, except for the one created in step 1, several times.
3. Use Dbcc Memusage to determine which 12 largest stored procedures remained in the
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.
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 recovery interval parameter to calculate how often to perform a
checkpoint. Sybase SQL Server internally computes the frequency of the checkpoint based on the
recovery interval value and the transaction rate to guarantee that in case of a system crash the
database(s) would be recovered within the time specified by the recovery interval parameter.
For example, in a heavy transaction processing environment, a recovery interval value of 5
(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

Page 14 Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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 option9allows 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 using Control Panel/Devices or by issuing the diskperf -y|n
command from the system prompt. When using the Control Panel, set diskperf to automatically
start at boot time to enable. You must restart your system for the diskperf option to become
effective.
Disk Performance Monitor Objects and Their Meaning
You can monitor LogicalDisk- and PhysicalDisk-related objects, such as Avg. Disk sec/Transfer
or Disk Transfers/sec. Monitoring LogicalDisk and PhysicalDisk-related objects requires you to
have the diskperf option enabled, which slightly reduces performance.
Note that resetting the diskperf option requires you to reboot your machine.
Object: Logical Disk
Counter: Avg. Disk sec/Read, Avg. Disk sec/Write
The Avg. Disk sec/Read and Avg. Disk sec/Write counters monitor the average number of
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.
Avg. Disk sec/Read Avg. Disk sec/Write
Transaction Log Access (with the Array
Accelerator enabled) n/a <=10msec
Database Access (with the Array
Accelerator enabled) 20-40msec <=10msec
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: Logical Disk
Counter: Disk Transfers/sec
The 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
9The diskperf option is described in a greater detail in the Microsoft Windows NT Resource Kit.

Configuration and Tuning of Sybase System 11 for Microsoft Windows NT on Compaq Servers Page 15
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
transfers per logical volume. To calculate number of disk transfers per second per drive, you
must divide the Disk Transfers/sec value by the number of drives in the logical volume.
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.
1.0GB drives
(Max I/Os per
Second)
2.0GB drives
(Max I/Os per
Second)
4.0 GB drives
(Max I/Os per
Second)
Sequential Writes
(Transaction Log)
≈150 ≈160 ≈200
Random Reads/Writes
(Database Access)
≈30-40 ≈50 ≈60-65
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.
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.
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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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
1996 Compaq Computer Corporation, All Rights Reserved Doc No 143A/0596
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 Best Foreground Application
Response Time otherwise set to Foreground and Background Applications Equally
Responsive
• set Server Optimization to Maximize Throughput for File Sharing
• use Compaq Insight Manager to monitor system hardware, CPU and EISA bus utilization