The Ultimate Guide to SAP ASE (Sybase ASE) Administration and Management

Part 2 — ASE Architecture, Memory Management, Storage Design, Transaction Logs, Devices, and Real Telecom Operations

This section is extremely important because most large production outages happen due to misunderstandings about:

Memory
Storage
Devices
Transaction logs
Cache management
I/O bottlenecks
Database layout design

If you master these concepts, you will already be ahead.

Understanding the ASE Architecture Properly

Most DBAs think ASE is “just a database.” That is incorrect.

ASE is actually a very large memory-driven transaction processing engine.

The database files on disk are only one part of the system.

The real work happens in:

Memory
Caches
Buffers
Transaction logs
Worker engines
Disk I/O coordination
Locking systems

How ASE Processes a Query

Let us simplify the process. Suppose an application runs:

select * from customer where customer_id = 100
go

ASE performs these steps:

Client connects
Authentication occurs
SQL parser validates syntax
Optimizer builds execution plan
Cache manager checks memory
Required pages loaded into cache
Locks acquired
Data returned
Transaction committed
Locks released

Every one of these steps can become a bottleneck.

The Most Important ASE Concept: Data Cache

If you remember only one thing from this entire section, remember this:

ASE performance is heavily dependent on cache efficiency.

ASE tries to avoid reading from disk repeatedly.

Why?

Because disk access is expensive. Memory access is extremely fast.

So ASE stores frequently used database pages in memory caches.

Understanding Data Cache

Think of data cache like a kitchen refrigerator. Frequently used food stays in the refrigerator because it is faster to access. Rarely used food stays in storage.

ASE does the same thing. Frequently used database pages stay in memory.

Why Cache Matters So Much in Telecom Systems

Telecom databases process enormous workloads:

Subscriber lookups
Billing transactions
Call detail records
SMS records
Authentication requests
Provisioning events
Roaming events

Millions or billions of transactions occur daily. Without efficient caching:

Disk I/O explodes
CPU increases
Applications slow down
Response time increases
Outages occur

Main ASE Memory Components

ASE memory is divided into several areas. You must understand these.

1. Total Logical Memory

This is the total memory ASE wants. Example:

sp_configure "total logical memory"
go

2. Total Physical Memory

Actual memory allocated from the operating system. Example:

sp_configure "total physical memory"
go

3. Procedure Cache

Stores:

Execution plans
Stored procedures
SQL plans

Important because telecom systems often use thousands of procedures.

4. Data Cache

Stores database pages. Usually the most important memory area.

5. Statement Cache

Caches SQL statements for reuse. Improves performance.

Understanding ASE Engines

ASE uses worker processes called engines. You can think of them as CPU workers.

Example:

sp_configure "number of engines at startup"
go

Important Rule

More engines does NOT always mean better performance. Too many engines can create:

CPU contention
Context switching
Scheduler overhead

Always size carefully.

ASE Devices

ASE does not store databases directly as files the way some databases do like Microsoft SQL Server.

Instead, ASE uses devices. Devices can be:

Raw devices
Filesystem files
Logical volumes

Example device:

disk init
name = "prod_data01",
physname = "/sybase/devices/prod_data01.dat",
size = "500G"
go

What Is a Database Device?

A device is storage ASE can use. Inside devices, ASE stores:

Databases
Tables
Indexes
Transaction logs

Important Telecom Best Practice

Separate these physically:

Data devices
Log devices
Tempdb devices
Backup devices

Never place everything on the same storage.

Why Separate Data and Logs?

Transaction logs are sequential writes. Data access is random I/O.

Mixing them causes performance problems.

Understanding Transaction Logs Properly

This is one of the most critical ASE topics. Many DBAs never fully understand logs.

That becomes dangerous.

What Is the Transaction Log?

Every database change is first written to the transaction log.

Example:

INSERT
UPDATE
DELETE

All changes go to logs first. Logs are required for:

Recovery
Rollback
Replication
Point-in-time recovery
Crash recovery

Very Important Concept

ASE writes to transaction log BEFORE writing data pages.

This is called: Write Ahead Logging (WAL)

Critical for consistency.

What Happens When Logs Fill Up?

Applications stop.

Common error: Transaction log is full

Possible causes:

Missing log backups
Long running transactions
Replication delays
Bulk operations
Failed truncation

How to Check Log Usage

Example:

dbcc checktable(syslogs)
go

Also:

sp_helpdb mydb
go

Telecom DBA Reality

In large telecom systems: Transaction logs can grow extremely fast.

Examples:500 GB/hour

2 TB/day
20 TB/week

Especially for:

CDR systems
Billing systems
Event processing systems

Best Practice for Large Telecom Databases

Always have:

Frequent log backups
Dedicated log devices
Log growth monitoring
Emergency free space
Replication monitoring

Critical Log Backup Strategy

Typical telecom approach:

Full backup

Daily or weekly depending on size.

Transaction log backups

Every:

5 minutes
10 minutes
15 minutes

Depending on recovery requirements.

Example Log Backup Script

dump transaction billingdb
to "/backup/logs/billingdb_log_01.dmp"
go

Important Warning About dump tran with truncate_only

Senior DBAs sometimes use:

dump transaction mydb with truncate_only
go

Avoid this except in emergencies.

Why?

Because it breaks recovery chain. In modern best practices, this is discouraged.

Tempdb — The Most Ignored Database

Many ASE performance issues involve tempdb. Tempdb handles:

Sorts
Temporary tables
Worktables
Hash operations

Heavy workloads can overwhelm tempdb.

Telecom Systems and tempdb Problems

Telecom queries often involve:

Massive joins
Aggregations
Billing calculations
Reporting

These create huge tempdb usage. Symptoms:

Slow queries
Blocking
Disk saturation
Spinlock contention

Best Practices for tempdb

Use:

Dedicated devices
Fast storage
Multiple tempdb databases
Monitoring

Example tempdb Monitoring

sp_helpdb tempdb
go

Understanding ASE Locks

ASE uses locks to protect data consistency. Common lock types:

Shared locks
Exclusive locks
Intent locks

Shared Lock

Allows reading. Multiple sessions can share.

Exclusive Lock

Used during updates. Only one session allowed.

Common Telecom Locking Problems

Very large transactions create:

Blocking
Deadlocks
Long waits

Especially during:

Batch jobs
Billing cycles
ETL processing
Massive updates

Understanding Blocking

Blocking occurs when:

Session A holds a lock.

Session B waits.

Blocking is normal. Excessive blocking is dangerous.

How to Find Blocking

Example:

select
    spid,
    blocked,
    waittime,
    cmd
from master..sysprocesses
where blocked != 0
go

How to Kill Blocking Sessions Carefully

Example:

kill 123
go

WARNING:

Never kill sessions blindly in telecom production systems.

You may interrupt:

Billing
Financial processing
Replication
Provisioning

Always investigate first.

Understanding ASE Deadlocks

Deadlock example:

Session A locks row 1.

Session B locks row 2.

Then:

Session A wants row 2
Session B wants row 1

Neither can continue.

ASE kills one session automatically.

Deadlock Prevention Best Practices

Applications should:

Access tables consistently
Keep transactions short
Commit quickly
Avoid user interaction during transactions

ASE Isolation Levels

Isolation levels control locking behavior.

Common levels:

Level 0
Level 1
Level 2
Level 3

Level 0

Dirty reads allowed. Fastest. Least safe.

Level 1

Default in many systems. Balanced.

Level 3

Highest consistency. Most locking.

Important Advice

Do NOT change isolation levels globally without understanding consequences.

Understanding ASE Recovery

Recovery occurs after:

Server crash
Power outage
Storage failure
Forced reboot

ASE performs crash recovery automatically.

Recovery Phases

Phase 1 — Analysis

ASE analyzes logs.

Phase 2 — Redo

Committed transactions replayed.

Phase 3 — Undo

Incomplete transactions rolled back.

Large Database Recovery Reality

For 250 TB systems:

Recovery may take hours. Sometimes longer. Especially if:

Long transactions existed
Huge logs existed
Storage slow

Important Telecom Rule

Avoid extremely long transactions. They increase recovery time dramatically.

Understanding Checkpoints

Checkpoint flushes dirty pages to disk.

Example:

checkpoint
go

ASE also performs automatic checkpoints.

Why Checkpoints Matter

Without checkpoints:

Recovery becomes slower. Too many checkpoints also hurt performance.

Balance is important.

Monitoring ASE Performance

Performance tuning is a huge subject. DBAs should first monitor basics.

Most Important ASE Performance Metrics

CPU Usage

Check OS:

sar -u 5 10

Disk I/O

Check:

iostat -x 5

Watch:

await
svctm
util

Memory Usage

Check:

vmstat 5

ASE Cache Hit Ratio

Important metric. Higher cache hit ratio usually better.

Network Latency

Critical in replication systems.

Replication Basics

Replication Server is extremely common in telecom. You must understand basics.

What Replication Does

Replication copies transactions from source to target.

Example:

Primary server: ASEPRD

Target server: ASEDR

Changes copied automatically.

Why Telecom Companies Use Replication

Reasons:

Disaster recovery
Reporting servers
Geographic redundancy
Zero downtime maintenance

Important Replication Components

RepAgent

Extracts transactions from logs.

Replication Server

Moves transactions.

RSSD

Replication metadata database.

Very important.

Common Replication Problems

Queue Build-Up

Transactions accumulate. Possible causes:

Slow target Server
Network issue
Suspended replication

Latency

Delay between source and target.

Suspended Connections

Replication stopped.

Replication Monitoring

Daily check:

Queue sizes
Stable devices
Latency
Suspensions

Storage Architecture Best Practices

Storage design is critical in ASE.

Poor storage causes:

Slow queries
Recovery delays
Backup failures
Replication lag

Telecom Storage Best Practices

Separate different storage groups:

Data
Logs
tempdb
Backups
OS filesystem

Recommended Storage Layout

Example:

/sybase/data
/sybase/logs
/sybase/tempdb
/sybase/backups

SAN Best Practices

Most telecom systems use SAN storage. Monitor:

Latency
Queue depth
Multipathing
Storage failover

Linux Tuning Basics for ASE

ASE heavily depends on Linux tuning.

Important OS Parameters

Monitor:

Swapping
Huge pages
I/O scheduler
Network buffers
File descriptors

Never Allow Swapping

Swapping destroys ASE performance. Check:

vmstat 5

If swap increasing: Investigate immediately.

ASE Error Log

The error log is your best friend. Learn to read it constantly.

Common Serious Errors

Error 823

I/O problems. Often storage-related.

Error 605

Allocation corruption. Serious.

Error 3474

Log corruption. Critical.

Stack Trace Errors

Possible ASE bug. Save:

Error log
Stack trace
Configuration
OS logs

Incident Response Process

When outage occurs:

Step 1 — Preserve Information

Never destroy evidence. Save:

Logs
Screenshots
OS metrics

Step 2 — Determine Scope

Is it:

One database?
One server?
Entire environment?

Step 3 — Communicate Clearly

Avoid panic. Be factual.

Step 4 — Stabilize System

Do not experiment.

Step 5 — Root Cause Analysis

Only after stabilization.

Essential DBA Documentation

Every ASE DBA should maintain:

Server inventory
Backup schedules
Device maps
Database sizes
Contact lists
Recovery procedures
Escalation lists

Example Daily DBA Notes

Track:

Date
Issue
Root cause
Fix
Lessons learned

This becomes priceless over time.

The Most Valuable Skill

Not tuning. Not scripting. Not even replication.

The most valuable skill is: Calm systematic troubleshooting.

Monday, May 18, 2026