Hp COMPAQ PROSIGNIA 500, COMPAQ PROSIGNIA 300, COMPAQ PROSIGNIA 720, COMPAQ PROLIANT 2000, COMPAQ PROSIGNIA 740 Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1

Download

Config uration and Tuning of

Sybase SQ L Server 11 for

SCO UnixWare 2.1 on

Compaq Servers

Whit e Pa per

________________________________________________________________

Prepared By

Database Engineering

Compaq Computer Corporation

June 1997

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 INCIDE NT AL OR CONSEQUENTIAL DAMAGES RESULTING FROM THE FURNISHING, PERFORMANCE, OR USE OF THIS MATERIAL.

This publication contains information protected by copyright. Except for internal use distribution, no part of this publication may be photocopied or reproduced in any form without prior written consent from Compaq Computer Corporation.

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 warr a nties.

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

COMPAQ, SYSTEMPRO, PROLIANT, PROSIGNIA

Registered US Patent and Trademark Office.

Configurat i o n a nd Tuning of Syba se SQL Server 11

for SCO UnixWare 2.1

on Compaq Servers

Second Edition (June 1997)

Document Number 541A/0697

Compaq Computer Corporation

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Page i

Table of Contents

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

Tuning Goals.......................................................................................................................................2

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

CPU Scalability............................................................................................................................ 2

CPU Tuning................................................................................................................................. 5

Disk Subsystem Planning .................................................................................................................... 6

Array Accelerator: Its Fu nction an d Benefit i n a Sybase SQL Server E nvir onmen t ....................... 6

I/O Tuning..........................................................................................................................................12

Separate Sequential and Random I/O’s......................................................................................... 12

Layout of Tables and Files............................................................................................................ 12

Checking Disk I/O Rate................................................................................................................ 13

Enabling Asynchronous I/O.......................................................................................................... 14

Memory Tuning .................................................................................................................................. 15

Initial Memory Recommendations for Sybase ............................................................................... 15

UnixWare Patches for 4GB Support and Sybase Support............................................................... 15

UnixWare Shared Memory Parameters......................................................................................... 15

UnixWare PSE Feature................................................................................................................. 17

UnixWare User Process Capacity Parameters................................................................................ 18

Sybase Installation Issues.....................................................................................................................18

Installation with CD-ROM........................................................................................................... 18

Sybase Devices.............................................................................................................................18

SQL Server 11 Configuration and Tuning Parameters......................................................................... 19

sp_configure and Configuration file (

servername.cfg

)................................................................... 19

Recovery Interval in Minutes ........................................................................................................ 19

Number of User Connections........................................................................................................19

Total Memory...............................................................................................................................20

Procedure Cache Percent .............................................................................................................. 20

SQL Server Clock Tick Length..................................................................................................... 21

HouseKeeper Free Write Percent...................................................................................................21

Named Data Caches......................................................................................................................21

Large I/O...................................................................................................................................... 21

Network Characteristics of a SQL Server Environment........................................................................ 21

Compaq Insight Man a ger (CIM) .........................................................................................................21

Conclusion .......................................................................................................................................... 22

References........................................................................................................................................... 22

 1997 Compaq Computer Corporation, Al Rights Reserved Doc No 541A/0697

Introduction

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

The information presented in this document is based on Sybase SQL Server 11 for UnixWare 2.1 and is a result of numerous performance tests executed within the context of an industry-standard TPC-C benchmark, intern al benchmarking for hardware development and optimization, and analyzing customer-reported expectations, performance trends, and solutions.

Compaq is an active member of the Transaction Processing Performance Council, and publishes a number of benchmarks every year proving the superior performance and price-performance ratios of Compaq servers. The results of these benchmarks can be obtained directly from Compaq Computer Corporation or from the Transaction Processing Performance Council. The contact information is listed below.

Configuration and Tuning of Sybase SQL

Server 11 for SCO UnixWare 2.1 on

Compaq Servers

Compaq Computer Corporation Database Performance Engineering P.O. Box 692000 Houston, TX 77269-2000 http://www.compaq.com

Even though most of the testing that provided basis for this document was done in the area of online transaction processing, much of the information presented does apply to other environments, such as decision support and batch processing. We recommend that you always experiment before applying any changes to your production server.

Other publications covering these and related topics are listed below:

• Configuring Compaq RAID Technology for Database Servers, Compaq TechNote, P/N

184206-001

• Sybase SQL Server System Administration Guide

• Sybase SQL Server Performan ce and Tun i ng Guide

• Compaq T ech C omm u niqué ‘COMPAQ INSI GHT Ser ver Ma nagemen t’

Transaction Processing Performance Council c/o Shanley Public Relations 777 North First Str eet, Suite 6000 San Jose, CA 95112-6311 http://www.tpc.org

Page 2

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Tuning Goals

In order to achieve the best performing system possible there are several factors which must be reviewed. These include optimization of the hardware, the Sybase SQL Server, the operating system and the application software. This paper will focus on the hardware, Sybase SQL Server and the OS. It is also important to tune the Sybase application to take advantage of the system. Due to the diversity of database applications, they are beyond the scope of this paper. This paper will discuss CPU scalability and tuning, disk controller optimization and I/O tuning, memory tuning, and network tuning. Also, specific Sybase and UnixWare configuration and tuning issues will be presented throughout the paper.

System Processor Planning

This section is provided to demonstrate the scalability of various processor configurations on the Compaq Proliant family of servers and to provide you with some performance information necessary to determine the best configuration for your environment.

CPU Scalability

The performance information presented in this section was gathered on three Compaq Proliant models, Proliant 4500 and Proliant 5000 and Proliant 6000, with different system processor configurations and different amounts of memory. All configurations had one aspect in common: all were CPU bound.

The tests run were all disk-intensive tests. Disk-intensive tests are designed to access th e entire range of a database that is many times larger than the data cache of the server. The net effect is that only a very small portion of the database can fit into the data cache at any point in time, and a large amount of physical I/O is generated in addition to the transaction log I/O. Such an environment tests heavily the CPU, memory, and the disk subsystem.

The tests generated a large number of update-intensive transactions and utilized heavily the transaction log. No hardware or software options were disabled to sacrifice data integrity of the system.

Figure 1 shows scalability on the Proliant 4500 with Pentium processors, using 1GB RAM. Figure 2 shows scalability on the Proliant 5000 with PentiumPro PentiumPro 6000 with PentiumPro



/166MHz processors, using 1GB RAM and 2GB RAM in the server. The



/166MHz has a 512KB level 2 cache. Figure 3 shows the scalability on the Proliant



200MHz processors with 512K level 2 cache and 4GB RAM. Figure 4

shows memory scalability on the Proliant 6000 with 4 PentiumPro



/100MHz and Pentium/133MHz



200MHz processors with

512K level 2 cache.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Figure 1: CPU Scalability of the Proliant 4500

CPU Scalabilty on Proliant 4500

2.5

Page 3

1.5

Transaction Rate Normalized

0.5

124

Number of CPUs

Figure 2: CPU Scalability for Proliant 5000

CPU Scalabilty on Proliant 5000 with 1GB vs. 2GB RAM

2.5

1.5

100MHz Pe nt ium

133MHz Pentium w/ 512K cache

133MHz Pentium w/ 2M cache

Transaction Rate Normalized

0.5

1234

Number of CPUs

166MH z PentiumPro w / 1GB RAM

166MH z Pentium Pro w/ 2GB RAM

Page 4

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Figure 3: CPU Scalability for Proliant 6000

CPU Scalabi lity for Proliant 6000

2.5

1.5

Transaction Rate Normalized

0.5

1234

Number of CPUs

Figure 4: Memory Scalability for Proliant 6000

Memory Scalability for ProLiant 6000

1.6

1.4

1.2 1

0.8

0.6

0.4

Transaction Rate Normalized

0.2 0

1GB 2GB 3GB 4GB

Memory

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Initial CPU Re c ommendations

The choice of the rig ht system p rocessor d ep ends on your envir onmen t. As t echnol ogy evolves, more powerful processors are becoming available, pushing performance to new levels at very competitive costs per processing unit. PentiumPro application servers, especially the PentiumPro



-based system s have become the sta ndard for



166MHz and 200MHz processors.

Our recommendation is to carefully evaluate your environment, and experiment with various processor configurations, if possible. Always have future growth and expansion in mind. You may want to start with Pentium



processors for smaller departmental systems, and PentiumPro processors for higher demand systems. If your environment has many concurrent users, you should evaluate benefits of multiprocessing.

Befor e upgr a ding the system processor(s), you sh ould closely monitor performance of the system and tune it from the software perspective. If the performance bottleneck is in software, har dware upgrades can only par tially improve per formance. It may be more cost effective to tune the software rather than to purchase hardware upgrades. If the performance bottleneck is clearly at the system processor(s), upgr a ding to a higher speed processor, or adding another processor can dramatically improve performance.

CPU Tuning

To monitor CPU performan ce and determine whether your system is CPU bound or not, you can run the UnixWare per formance monitor, rtpm. This should be run during a normal workload to get an accur ate reading. You also may use the sar utility to save the information to a file. A system that is tuned well will have the following CPU char acteristics:

Page 5



• Most of the CPU utilization is in user mode (%usr). Again, this is verified by runnin g

and looking at the percentage of CPU time spent in system and in user time. System

rtpm

sar

time (%sys) can be thought of as operatin g system overhead such as time spent in the I/O subsystem or in system calls. The higher the percentage of user to system time th at you have, the better. For a system performing mostly Sybase processes, 83 %usr and 17 %sys is a very good split, assuming there is no CPU idle time. The key is that Sybase should be getting most of the CPU time. Here is some sample output from

sar -u

> sar -u 5 5

09:00:10 09:00:15 09:00:20 09:00:25 09:00:30 09:00:35

Average

%usr %sys 82 17 83 17 80 18 82 17 81 18

82 17

%wio %idle 01 00 02 01 00

The above exam ple shows a system that is CPU bou nd if all other areas had already been tuned and optimized for performance. CPU bound means that in spite of your efforts to tune the system, you cannot get more per formance from it because there is no mor e processin g power left on the CPU(s). If your database and application are well tuned, and if there is no idle time an d

Page 6

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

no waiting on I/O on the CPU(s), then you may be CPU bound. In that case, an addition al system processor or higher speed processors(s) could greatly improve response times.

User s should see good response times. A system that appears to be tuned well an d is experien cing poor respon se times could have any or all of the following problems:

• An inefficient database design. This could include poor indexing sch emes or inefficient

layout of the data on the drives when the database was created.

• Inefficient statements in the database application, such as poor SQL statements in stored

procedures.

• Excess latencies in the I/O subsystem or network.

Adding engines to Sybase

If you add a p rocessor( s) to a ser ver, you may need to reconfigur e the n umber of Sybase en g ines. The parameter that sets th e maximum number of Sybase engines that can be started when SQL Ser ve r boots is called max online engines. If this parameter is set to a number greater than the number of processors in the machine, then Sybase automatically starts with one en gine per processor. The Sybase server

errorlog

will have a message indicating that the number of engines configured could not all be started. This is because a maximum of one Sybase engine per processor is allowed.

For a one processor environment, max online engines should be set t o one. For SMP environments, one engine per processor is common. For example, if you have 4 processors in the server an d most or all processes being performed on the system are Sybase processes, then you should set max online engines to 4. If th ere is a need to execute non-Sybase applications on the system, then it may be appropriate to set th e parameter to 3, using on ly 3 of the 4 processors for Sybase engines. This will leave one processor available at all times to perform the non -Sybase application processes.

Disk Subsystem Planning

The objective of this section is to provide information on the benefits of the Compaq SMART-2 SCSI Arra y Con troller Array Accelerat or and the p ros and cons of various fau lt tol eran ce methods.

Additional information on disk subsystem configuration and Compaq drive ar ray techn ology can be found in the following manuals:

Configuring Compaq RAID Technology for Database Servers

Compaq TechNote, P/N 184206-001

Compaq SMART-2 Array Controller User Guide, P/N 184482-001

Sybase SQL Server System Administration Guide, Doc. ID 32500-01-1100-02

Array Accelerator: Its Function and Benefit i n a Sy ba se SQ L S erver Environment

The Arra y Acceler ator i s a featur e of the SMART-2 SCSI Arra y Cont rollers. The main function of th e A rray Accelerator is to boost performa nce of di s k operations by storing data in the cache memory on the con t roller. T he SMART-2 Array Controll er has 4MB of ECC mem or y. The Array Accelerator is sha red among all logical drive volumes configured on the controller, and can be enabled/disabled on a per -logical-volume basis.

SMART-2 SCSI Array Controller characteristics

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

The SMART-2 controller allows the Array Accelerator to fun ction a s write ca che, read-ahead cache, or a combination of both. You can, for example, configure the Array Accelerator on the particular contr oller to function as 50% read-ahead and 50% write cache. Then every logical volume on this contr oller i s supported by the Array Accelerat or in the 50 /50 mode. Th e only excepti on is when the Array Acceler ator i s d isabled for th e volu me.

Write Cache

When the Arra y Accelerator per forms wri te caching, th e d rive controller writes data to the cache memor y on the Arra y Accelerator ra ther than directly to the drives. The system ca n access thi s cache memory more than 100 times fa ster than accessing disk stor age. The controller writes the data in the Array Accelerator to the dri ve a t a later time, when the controller is otherwise idle.

Without the Array Accelerator ’ s write ca che, t he app lication must wait until each write request is written out to the disk. Writing to a disk device is slower than postin g th e write request in the Array Accelerator, th u s possibly resu ltin g in decreased per formance.

Read Cache

The SMART - 2 control ler uses t he Array Accelera tor to increa se perform ance in some cases by anticipating possible future read requests. The Array Accelerator uses a mu lti-th readed algorithm to predict the next likely r ead operation for the drive ar r ay. That data is pre-read into the cache on the Arra y Accelerator an d therefore is ready before you access it. Wh en the SMART-2 cont roller receives a read request for t he cach ed data, it can be burst read immediately into system memory, thus avoiding a disk access after the read request.

Page 7

The r ea d- a head option of the SMART-2 SCSI Array controller can boost performance in environments that utilize sequential scans of data; for example, range lookups, data loads, table scans, decision support environments, etc. Envir onmen t s with a very random I/O profile, such as on-line transaction processing, typically do not take advantage of the read-ahead capabilities of the controller, and in most cases it is beneficial to configure 100% of the Array Accelerator for write cachin g.

Housekeeper, Checkpoints, and Transaction Log Writes

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



During idle time on th e SQL Server, the housek eeper writes dirty pages from the data cache to the disk at a lower priority than the checkpoint process. Unlike a ch eckpoint process which must write all dirty pages from the data cache to disk before termin atin g, th e housekeeper writes only what it can during idle times of the system. If th e system is idle for a long enough period of time the housekeeper may actually write all dirty pages from the data cache to disk. When this occurs the housekeeper notifies th e checkpoint process and requests th at a checkpoint be performed on the database so that the transaction log will h ave a record that all dirty pages wer e written to disk at that time. (For details on configuring the housek eep er, see Housekeeper Free Write Percent)



During checkpoints, Sybase SQL Server genera t es a l arge nu m ber of writ e requests in a short time interval. The main objective of the checkpoint is to write al l dirty pages from the data cache to the disk. The time it takes to write the dirty pages depends on sever al factors, such as the configuration of th e housekeeper and the recovery interval of t he SQL Server. (See the explanation of recovery interval)

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

satu rate the Arra y Accelerator, thus interfering with read req u es t s p ending at the contr ol ler. Proper tuning of the housekeeper can help alleviate this problem.

Page 8

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers



The transaction log activity is composed exclusively of sequential writes and does not

satu rate the Arra y Accelerator. However, t he benefits of caching the transacti on log writes at the SMART or SMART-2 SCSI Array Cont roller level with t he Array Accelerat or can have a significant beneficial impact on performance. For optimal performance the Array Acceler ator should be enabled. It i s very importan t to make s u re you follow the g u idelines below for data integrity if you choose to enable the Array Accelerator on the transacti on log.

Integrity of cached data in the Array Accelerator

The A rra y Accelerat or contains batteries that maintain any data in the cach e if a system power failure occurs. Make sure you maintain the batteries in a good condition an d fully charged (they are automatically recharged while system power is present). At a fully charged state, th e batteries can preserve data in th e Array Accelerat or for four d ays. When power is restored to your system, an initialization feature writes the preserved data to the disk drives.

Another step to insure data integrity in case of system failure is to install an Uninterruptible Power Supply (UPS). Installation of a UPS will allow the controller to flush all data out to disk in the event of a power failure. The UPS does not, h owever, in sure data integrity in the case of a controller failure, when valid data exists in the Array Accelerator. In that ca s e, th e Arr ay Accelerat or may be removed as a com p lete un it from one SMART-2 controll er an d installed on another, while preserving any data cach ed. The data will be written to disk upon power up.

Array Accelerator: Its Impact on Performance

The fol lowing figure shows perfor man ce impact of variou s A rray Accelerator confi g urations in an on-line transaction processing environ ment. For our baselin e we chose to disable the Array Acceler ator for the t ran s action log volume( s ) and enable t he Ar ray Acceler ator with 100% write cache for all data volumes.

When the Arra y Accelerator is disabled on th e d atabase volume(s), the perfor man ce greatly decrea s es since write r eq u es ts can not be cached in the A rray Accelerator. On the other ha nd, allowing the controller to cach e the transaction log wr ites can improve performance in some cases.

As already mentioned, partially configuring the Array Accelerat or for r ead-ahead wh en the I/O profile is purely random does n ot improve the perfor mance. On the contrar y, the attempts of the controller to perform read-ahead wh en little-to-none is possible can h urt performance, as well as the effect of decreasing the write cache to allocate some read-ahead cache. As shown in Figure 3 below, t he I/O profile for that test could not take advantage of read-ahead, so configuring 50% of the A rra y Accelerat or cach e for read- ahea d actually decr eased performan ce.

In our envi ronm ent, the h ighes t perfor man ce was achieved wh en the Array Acceler ator was enabled for both the transaction log volume(s) and the database volume(s) an d both were configured for 100% write cache. In Figure 3 below, each bar shows whether the Arr ay Accelera tor was enabled (ON) or d i sabled (OFF) for the log a nd for the data volumes. Also “W” shows the percent of the Array Acceler ator cache ded icated to caching wr ites, and “R” s hows the percent dedicated to cach ing reads.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Figure5: Array Accelerator Performance Impact

Arra y Accelerator P er fo rmance Impact

1.01

0.99

0.98

0.97

0.96

0.95 Log=OFF,DB=ON

(100%W)

Log=ON, DB=ON

(100%W)

Log=ON (100%W),

DB=ON (50%R,

50%W)

Log=ON (100%W),

DB=ON (100%R)

Page 9

Configuring the Array Accelerator The Arra y Acceler ator of the SMART-2 SCSI Arra y controller is config u red via t he Compaq

Array Configuration Utility. Always obtain the latest copy of the appropriate utility for your

Compaq SCSI Array controllers. With t he SMART-2 Array controller, you can select th e ratio of read-ahead cache to write cache

for every cont roller. Once selected , th is Array Acceler ator ratio will apply to all logical volumes on this con troller that have the Array Accelerator enabl ed . You can ena bl e or disa bl e the Arra y Acceler ator on a per-logical-volu m e basis.

To select t he Ar ray Acceler ator ratio for a SMART-2 Array contr oller, you must install and run the Compaq Array Configuration Utility from diskettes or from the System Partition. Using the Array Configuration Utility, h ighligh t th e appropriate con tr oller and use the “

Controller/Settings...

” option.

Figure 4 below sh ows h ow to enable/ d isable t he Array Accelerator for a pa rticu lar logical d rive. Using the Array Configuration Utility, highlight the appropriate logical volume and use th e “

Logical Drive/Modify...

” option.

Page 10

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Figure 6: SMART-2 Array Controller - Logical Drive Array Accelerator Settings

Fault Tolerance Considerations

Your transaction log needs to always be protected against a disk failure. Most mission critical sites protect both the transaction log and the database devices and they usually choose har dwarebased fault tolerance. Non-mission critical sites are often satisfied with the protection of the transaction l og only and performing frequent backups/dumps.

You have two choices of pr otecting your data:

Use a RAID-based disk contr oller such a s t he Compaq SMART-2 SC SI Array Controller

Use Sybase SQL Server-based mirrorin g/dupl exin g.

Below are some key points to be aware of when choosing the appropriate fault tolerant method. The performan ce differ ences 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 recommen d that you evaluate the various fault tolerance methods using your own applications.

1. RAID-based Di sk Controllers



Hardwar e 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 con figured.



Hardwar e 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 th e new drive and restores the system to its full fault tolerant capabilities.



Since the data protection occurs at the hardwar e (controller) level, there is no overhead on the system processor. This type of RAID is totally transparent to the operating system and

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

the application s. With a CPU-intensive application/envir onment, such as Sybase SQL Server, hardware-based fault tolerance can provide the best performance. Please refer to

Configuring Compaq RAID Technology for Database Servers

TechNote, published by

Compaq (P/N 184206-001) for a more com plete discussion.

2. Sybase SQL Server Mirroring/Duplexing



Sybase SQL Server mirr oring i s 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 cont roller duplexing if you place t he mirrored device on a different

for guidelines on choosing which devices to mirror.

disk controller.



Sybase SQL Server supports both serial and nonserial 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 n onserial write mode allows the writes to both devices to be queued immediately, one to each mirrored device. The nonser ial write mode will incur less overhead than serial writes that results in a slight performan ce improvement.



Mirroring through Sybase SQL Server induces an additional processin g overhead on the system, resulting in a lower performan ce.

Page 11

Sybase SQL

Page 12

I/O Tuning

In well tuned Sybase systems, I/O is not a limiting factor. In order to assure that this is not a problem, the followin g factors need to be ver ified.

• Sequential I/O’s are isolated to a controller volume, separate from volumes with random

• Random I/O’s are balanced acr oss all drives allocated to data and indexes.

• Physical disk I/O limits are not exceeded. (See T able 1 below).

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

I/O’s. This mean s a sequential I/O volume should be alone on a controller or on one port of the controller.

• There will be little or n o waiting on I/O (

the UnixWare tools some work to do while there are outstan din g I/Os. If asynchronous I/O is enabled for Sybase, then UnixWare will never show waiting on I/O. The exception to this is if some non-Sybase processes issue I/O requests th a t are n ot a synchronous, t hen you may see some waiting on I/O. (See section on AIO).

sar

. No waiting on I/O indicates that the CPUs always have

rtpm

%wio

) by the CPU(s). This is verified by running

Separate Sequential and Random I/O’s

In order to ach ieve maximum performance on data files being accessed sequen tially, the disk(s) need to be dedicated to this purpose. Of primary importance are the Sybase transaction log files, which are accessed in a sequential, write-only fashion. Other partitions with little I/O activity can share the disk(s) with the transaction logs, such as the OS partition and swap.

In typical multi-user database systems, data access is random. This data should be spread out over as m any physical dis k s as necessary to achieve random I /O ra tes th at do n ot ex ceed recommendations (see Table 1 below). This is best achieved by using the disk str ipin g available with the SMART-2 SCSI Array Controller. Spreadin g out t he disk r equ est s a m ong man y disks allows a high degree of parallelism to occur on accesses. Using the Compaq SMART-2 SCSI Array Controller ensures that the load will be balanced equally across th e disks. For mor e information on optimizing array configurations refer to the Compaq TechNote,

Compaq RAID Technology for Database Servers

Configuring

Layout of Tables and Files

In order to improve performance where disk I/O is a problem, keep in mind the following:

• Transaction log access is 100% sequential I/O and needs to be isolated if possible. Speed of

the log is essen tial to the performance of th e system. If possible, these drives should be fault tolerant, either mirrored or distributed data guarding. Hardware fault toleran ce provides the maximum performan ce and reliability. See the Compaq Database Engineering TechNote

Configuring Compaq RAID Technology for Database Servers.

• Data file access is usually random and needs to be spread across as many drives as necessary.

By increasing the number of physical drives, greater I/O rates can be achieved. Using a striped array will assure that the I/O’s are well distributed.

Use th e following guidelines wh en monitoring and optimizing the drive subsystem. You should not have more I/O requests (disk transfers) per second per disk drive than the values in the table on the next page.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Table 1: Maximum I/O operations per Second per Disk Drive

Page 13

1.0GB drives

(Max I/Os per

Second p er Drive)

Sequen tial Writes (Transaction Log)

Random Reads/Writes (Database Access)

NOTE: With th e A rray Accelerator en abled, you ma y act u ally see substantially higher I/O per second per drive rates than suggested above, especially during checkpoint. This increase is due to th e A rray Accelerator wri te posting some of t hese I/Os . In the Com p aq Database Performance labs we have actually measured rates of up to 90 random I/Os per second per drive, some of which were being temp orar ily cached by the Array Accelerator.

≈150 ≈160 ≈180

≈30-40 ≈50 ≈55-60

2.1GB drives

(Max I/Os per

Second p er Drive)

4.3GB drives

(Max I/Os per

Second p er Drive)

Checking Disk I/O Rate

Try not to overload any individual disk with random I/Os. To determine the I/O rate per drive, first determine the number of I/O’s per second to each logical volume. This can be done with

sar -d, rtpm

divide by the number of physical disks in th at logical volume. This will pr ovide the number of I/O’s p er second p er disk. If this number ex ceed s recommended I/O’s per second rating, adding more physical disks should improve average system performance. The followin g provides an example for determining the reads and writes per second, r+w/s, for each logical controller volume on a system.

or 3rd party tools. Take the number of I/O’s per second to each logical volume and

command

sar

> sar -d 10 20

10:51:47…device %busy avque

Average sd011 77 1.5 125 1001 9.3 6.1

sd012

sd013 88 2.7 257 1031 9.1 3.4 sd014 95 3.5 317 1271 10.5 3.0

It is best to use values from the above output as an e xam ple, if volu me sd012 contained 7-2.1GB disk drives that were used for random data access, then the calculation to find how many I/O’s per second per drive were done on that volume follows:

301 r+w/s divided by 7 drives in volume = 43 r +w/s per drive (< 50 )

Therefore, the sd012 logical volume is within Compaq’s recommended I/O limit of 50 I/O’s per second for the 2.1GB dr ive.

94 3.2

average over a period of time to calculate I/O rates. Using the

sar

r+w/s

301

blks/s avwait avserv

1504 10.0 3.1

Page 14

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Enabling Asynchronous I/O

Under vir tually all circumstances disk I/O runs faster asynchronously th at synchronously. This is because when SQL Server issues an asynchronous I/O, it does not have to wait for a response before issuing further I/O’s.

Asynchr onous I/O (AIO) is available on the UnixWare 2.1 operating system. AIO can be used with databases built using RAW devices or file systems and will achieve the best perfor man ce. Using AIO to RAW devices is prefer able because it is more efficient than AIO to file systems. To enable AIO through UnixWare you need to do two things:

chmod 666 /dev/async

1. Do system administrator) who star ts up the SQL Ser ver.

to give permissions on th at file to the user (possibly the

2. Edit the file

The Sybase parameter you do not have to make any ch anges to Sybase to use AIO. If you choose must set Administration Guide for more details.

The Sybase parameter OS (1=en abled and 0=disabled). You cannot configur e this parameter as it is read-only. It is an easy way to double check that AIO is enabled through the OS. See page 11-60 of Sybase SQL Server System Admin istration Guide for more details.

Asynchronous I/O UnixWare Parameters

There are two UnixWare parameters that may need tuned for AIO,

AIO_LISTIO_MAX NUM_AIO

needed each time an asynchronous request is initiated. If no control blocks are available, then an asynchronous call will fail, therefore this tunable determin es the maximum number of outstanding AIO’s. Th e default value is 256, which may be sufficient.

AIO_LISTIO_MAX

request to the kernel. The default value is 128, which may not need tunin g. For I/O-intensive application s, you may need to increase these two parameters. Doubling each of

the default values should be sufficient.

allow SQL server async i/o

sets the number of AIO control blocks available for ker n el use. One contr ol block is

/etc/conf/node.d/async

allow SQL server async i/o,

= 0. See page 11-26 of Sybase SQL Server Syst em

o/s async i/o enabled

sets the maximum number of I/O’s that can be submitted in a single

, and change the mode number there to

by default of 1, en ables AIO for Sybase. So

not

to use AIO, you

indicates whether or not AIO is enabled through the

NUM_AIO

and

666

also.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Memory Tuning

Initi a l Memory Rec o mme ndati o ns for Syba se

Sybase recommends a minimum of 16 MB memory for SQL Server, plus 70K of RAM per additional user . Th ese initial memory requirements may need to be increased based upon the number of user s, complexity of quer ies, number of disk controllers, amount of total disk stor age, number of network cards, and intensity of the workload in your environment. To estimate the minimum memory requiremen ts for a 512 user system simply multiply 512 * 70KB for a total of 35,840KB (i.e. 35 MB) and add this to the Sybase minimum requirement of 16 MB for a total of 51 MB.

Do not tune Sybase memor y up at the expense of swapping. Swapping will degrade system performance. (See next page to learn how to check for swapping. )

UnixWare Patches for 4GB Support and Sybase Support

UnixWare 2.1 currently supports up to 4GB of RAM. You will n eed to install two SCO patches to get this support, ptf3190 and ptf3191. Another pat ch you need in order to start Sybase on UnixWare 2.1 is ptf3142. These three patches can be downloaded from the SCO web site at

www.sco.com. Look under “Support”, then “Supported Patches and Supplements”, “UnixWare

2.1.x”. Read the ptf3190.txt, ptf3191.txt, and ptf3142.txt files for an explan ation of the patches. To install the patch es on your system login as root and do for both:

Page 15

1. uncompress ptf3190.Z

2. pkgadd –d /

directory/…/

ptf3190

UnixWa re Sh ared Mem ory Pa rameters

The amount of shared memory allowed in UnixWare needs to equal or exceed the amount of shared memor y required for Sybase. The OS tun able single shared memory segment. The maximum amount of sh ared memory that can be used by a single process is equal to the OS tun ables segmen t size * maximum number of shared memory segments). Setting total amoun t of RAM available in your system for Sybase use will ensure that only on e shared memory segment will be allocated for Sybase. Having more than one sh ar ed memory segmen t allocated to Sybase is less efficient than having one large shared memory segment. The number of sh ar ed memory segmen ts that h ave been allocated on your system can be verified with the OS command

(see example on next page).

ipcs

SHMMAX*SHMSEG

SHMMAX

sets the maximum size of a

(maximum shared memory

SHMMAX

equal to th e

Page 16

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

> ipcs -b IPC status from /dev/kmem as of Sun Jan 29 10:33:19 1995 T ID KEY MODE OWNER GROUP QBYTES Message Queues: T ID KEY MODE OWNER GROUP SEGSZ Shared Memory: m 900 0x10043232 --rw-r----- sybase dba 50745344 T ID KEY MODE OWNER GROUP NSEMS Semaphores: Note: Only on e shared memory segment h as been allocated with a size of 50745344 Bytes. By increasing the amount of shar ed memory allocated, you are reducing the amount of memory

available to non-Sybase processes. Be careful n ot to increase Sybase’s memory size to a point where swapping may occur. Swapping can be detected by notin g swapping activity with or with

. To see how much memory is free, use

rtpm

. If there is always a significant

sar -r

number of MB of free memory, then you may want to allocate a greater amount of memory to the Sybase server to use for data and procedure cache. If you do, check again for swapping. Below are sample outputs from the

sar -w

and s

commands.

ar -r

sar -w

> sar -w

10:09:28 10:09:33 10:09:38 10:09:43 10:09:48 10:09:54

Average

swpin/s

0.00

bswin/s

0.0

swpot/s

0.00

bswot/s pswch/s

0.0 150

0.0 123

0.0 134

0.0 152

0.0 144

0.0 140

The blocks swapped in a nd out per second (bswin/s & bswot/s) are given in 512 byte blocks. The number of swpin/s an d swpot/s per second is zero. This indicates that no swapping is occurring.

If you do see that swapping is occurring (swpot/s or swpin/s > 0), reduce the memory size given to SQL Server (ru n the sp_configure total memory command in isql, or edit the Sybase configuration file), restart the SQL Server, and check the system monitors again. In the case of a system with non-Sybase applications running as well, tr y reducing the memory used by those application s also. Continue this cycle until you see no swappin g. In some cases, adding more memory to the machine may be the best solution.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

>sar -r 5 5

Page 17

10:09:54 10:09:59 10:10:04 10:10:09 10:10:15 10:10:20

Average

freemem 28635 28627 28637 28637 28637

28636

freeswp 196608 196608 196608 196608 196608

196608

Freemem is given in number of 4K pages a nd freeswp is in 512-Byte disk blocks. The output above shows that abou t 111MB of memory is fr ee. In this case, it may be helpful to allocate more memory to Sybase to use for data and procedure cache.

UnixWare PSE Feature

To make the most efficient use of system memory on an Intel Pentium or Pen tiumPro processor you should take advantage of the Page Size Extension feature (PSE). The default page size allocated in memory is 4Kbytes. En abling the PSE feature allows memory to be allocated in contiguous 4 Mbyte pages that can be used for shared memory. The larger page size allows more efficient page lookup on Intel Pentium following:



processors. To configure and enable PSE, do the

1) Add the kernel tuning parameter called PSE_PHYSMEM to the stune file. The size you

give specifies how much memor y will be allocated using PSE, 4Mbyte pages. The number is in bytes and is rounded up to the nearest 4 Mbyte. To allow Sybase to use this shared memory area, both PSE_PHYSMEM and SHMMAX must be tuned to a size equal to or greater than the memory size given to SQL Server. Setting PSE_PHYSMEM equal to the total memor y size given to Sybase is recommended wh en r unn in g mostly Sybase user processes.

2) If you need to in crease the amount of memory to use for PSE beyond the default, you must

edit the file /etc/conf/mtune.d/pse. (If you have configured beyond the default, you will see an error message when trying to rebuild the kernel). Change the last number entry in the line for PSE_PHYSMEM to be th e same number of bytes that you specified in the stune file for PSE_PHYSMEM. Otherwise, you never need to edit this file.

3) Now that you have th e memory size specified, you must enable PSE. To do this, edit the file

/etc/conf/sdevice.d/pse. Change the “N” to a “Y”.

4) Now you must relink the UnixWare kernel by running, as root, /etc/conf/bin/idbuild -B and

reboot t he system.

Page 18

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

UnixWare User Process Capa city Parameters

There are two UnixWare parameters relating to user processes that may n eed tuning,

NPROC

and The OS parameter

on a per user basis. The OS parameter allowed on the system. and user processes to run. Both amount of memory in your system. To check on th e current value, use the OS command You may need to increase these values if th ey are not sufficient. Just add them into the file , re link t he Un ixWare kernel, and reboot the machine.

For descriptions of these and mor e of the OS system parameters, refer to the System Tuner program on your UnixWare desktop or to the UnixWare on-line documentation.

MAXUP

Sybase Installation Issues

Installation with CD-ROM

If you are installing Sybase from a CD-ROM, you must mount the CD-ROM to a directory such as

to run the

/mnt

> mount -F -r cdfs /dev/cdrom1 /mnt

sybload

specifies the maximum number of processes allowed on the system

NPROC

must be at least 50 greater than

MAXUP

program. Her e is an example:

specifies the maximum number of processes

and

NPROC

MAXUP

are tuned automatically based on the

to allow for other OS

MAXUP

sysdef

stune

Log out and log back in as your System Administrator and do:

> cd $SYBASE > /mnt/sybload -D

Now you should be able to go to the

$SYBASE/install

directory and run

sybinit

Sybase Devices

Managing disk parti t i ons

After creating the disk par titions through UnixWare, it is helpful to link each partition, or disk slice, to a more readable logical name for Sybase use. You could create a directory, for example /dev/sybase, and link each disk par tition to a filename, in that directory, that would specify which table or in dex is on that partition. In the case of a table called customer, which has its data on disk slice /dev/rdsk/c1b0t0d0s1 and its index on slice /dev/rdk/c2b0t0d0s1, the link commands could be:

ln -s /dev/rdsk/c1b0t0d0s1 /dev/sybase/cust_data ln -s /dev/rdsk/c2b0t0d0s1 /dev/sybase/cust_index

Now, the device can be referred to by the linked name, /dev/sybase/cust_data, rather than the UnixWare device name, /dev/rdsk/c1b0t0d0s1. It is a good idea to put all the link commands into a shell scr ipt so they can be easily executed in the future.

Device Permissions

You must give Sybase permission to access the devices you’ve cr eated before you can install the master and sybsystemprocs databases in Sybase server needs permissions on all devices that will be used by Sybase. Simply use the

. The user that is going to install and start the

sybinit

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Page 19

UnixWare commands administrator (who will start up SQL Server), uses the login name director y, /home/sybase, is the director y where SQL Server will be installed), and the gr oup is called

chown sybase /dev/sybase/cust_data chgrp sybgrp /dev/sybase/cust_data chmod 666 /dev/sybase/cust_data

Do the same for all Sybase devices.

, then the commands would be:

sybgrp

chown, chgrp

, and

. For exam ple, if the Sybase system

chmod

SQL Server 11 Configuration and Tuning Parameters

sp_configure and Configuration file (

With Sybase SQL Server 11 there are two ways to configure your server. One way is by using the sp_configure command that was used for SQL Server 10. With sp_configure you can set a parameter while the server is runn in g, and you may or may not have to restart the server for the new value to take effect, depending on whether the parameter is a static or dynamic one. You can check in the Sybase SQL Server System Administrator Guide, chapter 11, under the section “Details on Configuration Parameters” to find whether a parameter is static or dynamic.

The second way to configure your ser ver is new for SQL Server 11. A server configuration file, named

servername.cfg

you install SQL Server. All of the configuration parameters are found in this file and their values can be edited. To make a changed parameter value take effect, simply shutdown and restart SQL Server. See page 11-9 of Sybase SQL Server System Administration Guide for more details.

, where

servername

servername.cfg

is the name given to your SQL Server , is created when

(and his home

sybase,

)

Note: The configuration file eliminates the need for the SQL Server 10

which was used to change a Sybase parameter while the server was down.

buildmaster

command,

Recovery Interval in M i nutes

Recovery interval in minutes sets the maximum number of minutes per database that SQL

Server should use to complete its r ecover y procedures in case of a system failure. The recovery procedure recovers transaction s that occurred after the last checkpoint. SQL Server uses the recovery interval in minutes to decide when to checkpoint each database. (See Sybase SQL Server System Admin istration Guide, section “Details on Configuration Parameters” for more detail on this parameter.)

During a checkpoint, the changed or ‘dirty’ pages in the data cache are written to disk, leaving the data cache buffers ‘clean’. During the checkpoint, user tasks continue to run, but their response times may become longer because the high n umber of disk writes being done by the checkpoint take up CPU time. Immediately after a checkpoint, user response times will be slightly faster than normal because SQL Ser ver is more likely to find a clean buffer when it needs a new on e, and because buffer s that move into the wash area are more likely to be clean and will not need to be written to disk. Once the data cache is filled, user respon se times will slow down to ‘normal’ levels due to necessary disk accesses and memory management.

This recovery interval in minutes should be left at its default value of 5 minutes unless you are willing to take the risk of setting it to a higher value. If the recovery interval is set too long, the user response times will deterior ate and become intolerable wh en a checkpoint does occur . Setting the recovery interval too short will waste valuable CPU cycles and generate excessive disk I/O. See page 11-17 of Sybase SQL Server System Administration Guide for more details.

Number of User Connections

Page 20

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

This parameter sets the maximum number of users that can be connected to SQL Ser ver at the same time. It should be set as low as possible to leave more memory for the data cach e. Setting this parameter too high wastes memory and increases the size of the table that Sybase needs to scan when looking for new user logins or existing user s logging out. The default is 25.

SQL Server allocates approximately 70KB of memory as overhead per user connection. You may have to r eadjust the total me mory value depending upon the num ber of user connections your environment requires. If you increa s e the default network packet size or stack size configuration parameters, the amount of memory per user connection increases also. See page 11-100 of Sybase SQL Server System Administration Guide for more details.

Total Memory

This setting can be initially left at its default value. If you h ave a large number of active user connections or users are unable to connect to the database you will need to increase this value. The va lue expressed by sp_configure and in the memory area is used to store the data and procedure caches. Refer to the sections in this document on “Initial Memory Recommendations” and “Number of User Connections” for more details on determining the proper value for this parameter. Further tuning can be done by adjusting this value up or down for best user response times. In general, more memory allocated to Sybase gives better performance, up to th e point where swapping starts to occur.

One method to improve performance would be to set total memor y lar ge enough for h eavily used tables and indexes to fit into the data cache area. Be careful in doing this because setting this value hig her than necessary may cause a LOSS of performan ce n ot a gain . SQL Server may end up spending too much time attempting to manage the data cache memory ar ea instead of utilizing it. If you set the value for memory higher than the amount of memory available to the server, you will be unable to start SQL Server. If th is occurs, edit the parameter total memory in the

servername.cfg

Administration Guide for more details.

file and try to restart. See page 11-64 of Sybase SQL Server System

servername.cfg

file is in 2KB pages. This

Procedure Cache Percent

This setting is a percentage of the memory allocated to SQL Server that is reserved for cach ing of stored procedures. The initial default setting of 20 percent should be sufficient for most database environments. You may want to experiment running with a lower percentage for procedure cache, to leave more memory for data cach e. On the other hand, if you run a lot of different procedu res or ad hoc qu eries, you may want t o incr ease th is value. Th e p rocedure cache is not only used to stor e the compiled stored pr ocedures. The space is also used during the creation of stored procedures and to compile queries. See page 11-24 of Sybase SQL Ser ver System Administration Guide for more details.

To find the number of 2K pages that a stored procedure requires in memory, you can run this isql statement:

select (count(*)/8) +1 from sysprocedures where id=object_id(“procedure_name”)

Or you can use the isql command dbcc memusage which gives information on the twenty largest stored p rocedures.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

SQL Server Clock Tick Length

sql server clock tick length

The microseconds. The default value for the UnixWare platform is 100,000. CPU-bound tasks will benefit from increasing the clock tick length (up to 1,000,000) because it allows the tasks to execute longer between context switches. For mixed applications with lots of CPU-bound tasks, decreasing the clock tick length will help I/O-bound tasks. A recommended value to try is 20,000. Shortening the clock tick len gth will cause CPU-bound tasks to cont ext switch more frequen tly and allow oth er tasks greater access to the CPU. See pa ge 11-95 of Sybase SQL Server System Admin istration Guide for more details.

parameter specifies the duration of the server’s clock tick, in

HouseKeeper Free Write Percent

Page 21

Housekeeper free write percent

task can increase database writes. Valid values r ange from 0 to 100. Th e default is 1. A value of 0 disables th e housekeeper.

For example, to allow the housekeeper to increase disk writes up to 25 percent above nor mal, set

housekeeper free write percent

the Administration Guide for more details.

specifies t he ma x imum percen tage by which th e housekeeper

to 25. See page 11-75 of Sybase SQL Server System

Named Data Caches

Dedicated named data caches pr ovide a method of reserving a portion of the Sybase data buffer area for a specific database, in dex or table. For more information on using dedicated named caches r efer to Chapter 9 “Configuring Data Caches” in the Sybase SQL Server System Administration Guide. For more infor mation on performance tun in g of the data cach es refer to Chatper 15 “Memory Use and Per formance”, Section “Named Data Caches and Performance” in the Sybase SQL Server Performa nce and T u ning Guide.

Large I/O

By default, Sybase utilizes an I/O block size of 2K. By using dedicated named caches you can enable I/O block sizes larger than 2K. If a dedicated named cache is configured to use a block size of 16K, a request for 16K of data will be filled in a single read request to the operating system. Without large I/O the request would generate 8 separate reads of 2K each. For more information on perfor man ce tuning of large I/O refer to Chatper 15 “Memory Use and Performan ce”, Section “Large I/Os and Performance” in the Sybase SQL Server Performance and Tuning Guide.

Network Characteristics of a SQL Server Environment

Typically a client workstation assembles a gr oup of SQL commands and submits them for execution by the database server. The server processes the commands and returns the resultan t data. Rather than having the clien t workstation sen d a huge grouping of SQL commands, profile the q ueries . Deter mine if any of th e q ueries are candid ates for conversion to stored pr oced u res. A stored procedure is a grouping of ‘standardized’ SQL query commands that ar e pre-compiled and placed into th e procedure area of the database by the System Administrator. The stored procedure can then be referenced by name for execution. Utilizing stored procedures for most of the standard DBMS activities will reduce th e amount of network traffic and will use less server CPU resources to process the query.

Compaq Insight Manager (CIM)

Page 22

CIM is a Win dows based utility that uses SNMP in conjuncti on wit h OS and Driver Agen t s on the server to report h ar dware failures and system degradation due to a har dware problem. CIM can be configured to page the System Administrator if a component is failing. Using CIM prefailure warr an ty will allow a hardware component to be replaced under warranty before it fails. CIM monitors system hardware and a few OS components.

Conclusion

The information in this paper is not a complete tuning guide but a supplemen t to other tuning information provided by Sybase and SCO. To achieve an optimal configuration, there are several factors to include. The application, the hardware, and the OS are all areas that must be carefully planned and tuned. The tuning process is iterative an d will be done several times in order to achieve the most optimal performance possible. We hope that the information provided in this paper will help in this process. The information given is based on experience in tuning Sybase on UnixWar e 2.1, however, each configur ation is unique. Although all of the hints given here have been tested extensively, do not assume that tuning a specific parameter will always give th e desired result. Do n ot be afraid to experiment.

We welcome feedback on your configuration s and experiences to improve our in formation products in the future. Please send us any comments or suggestions on the attached form, attaching addition sheets if necessary. This will help us tailor future information products to your n eeds, and will enable us to make future revisions of this document and related n ew information products available to you.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

References

Sybase SQL Server System Administra t or Guide, Document ID: 32500-01-1100-02 Sybase SQL Server Performance and Tuning Guide, Document ID: 32645-01-1100-02 Compaq SMART-2 Array Controller User Guide, P/ N 184482-001

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Page 23

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

Please in dicate the type of environment you have at your site:

12345 12345 12345 12345 12345

Operating Systems RDBMS Processing Type



SCO Unix Open Server



Microsoft W indows NT



IBM OS/2



Novell NetWare



SCO UnixWare



Other:

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

Topic Operating Systems RDBMS



Configuration and Tuning



Capacity Planning



Integration Infor mation



Competitive Analysis



Systems Management



Other:

Additional Comments:

Return to:

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



Microsoft SQL Server



Sybase SQL Server 11



Oracle 7



Other:



Microsoft W indows NT



Novell NetWare



IBM OS/2



SCO UnixWare



SCO Unix Open Server



Other:



On-Line Transaction Processing



Decision Support



Batch Pr ocessing



Other:



Microsoft SQL Server



Sybase SQL Server 11



Oracle 7



Other:

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Serv ers

Doc No 541A /0697

Hp COMPAQ PROSIGNIA 500, COMPAQ PROSIGNIA 300, COMPAQ PROSIGNIA 720, COMPAQ PROLIANT 2000, COMPAQ PROSIGNIA 740 Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1

Specifications and Main Features

Frequently Asked Questions

User Manual