Software Gems' Sybase Gem Documents
Content & Veracity
The documents herein have been made up from the Memory Tag
pages of our Sybase education materials, or derived from our presentations.
They are intended to improve your understanding of Sybase ASE,
and thus increase the Quality and Performance of your Sybase server, and your effectiveness as a DBA.
These documents are far and above the Sybase
manuals, as follows:
- they contain information that is not in the manuals (ie. they overcome the lack of information)
- they bring all relevant information about a subject together, in one place
- where the manuals contain contradictory information, the correct version only,
is provided (ie. confusion and half-truths are eliminated)
- where misleading or false technical terms are used, correct technical terms are used instead
- where non-technical terms are used, technical terms are used instead
However, they are not a substitute for, they do not replace, the Manuals, which remain as product
reference. The information therein (updated with each release) is not repeated here.
Technically Correct & Relevant
There is an awful lot of shallow, inaccurate, misleading and false information on the Internet.
Unfortunately some of that false or misleading information is published by Sybase
both in the manuals, and on the web.
These documents provide full, complete and technically correct information (albeit very condensed)
about each subject, such that the reader is no longer vulnerable to false or confusing information.
Not the Full Monty
Customers paying for Performance Enhancement with Consulting obtain a somewhat richer version
of these documents, with site-specific configuration directives.
The documents published here are provided free of charge, as a service to the Sybase
- they are one major release behind the current release of Sybase ASE (which is not
an issue from an education standpoint, because the product is mature: if you do not understand, or
cannot configure the previous major release, you will not be able to understand or configure the
- while retaining configuration concerns, they have been stripped of configuration planning
information that paying customers receive (including a spreadsheet to perform the arithmetic across
related configuration parameters).
- Note that they are quite condensed and we are told, have that Gem quality about them.
- If you are new to the material, take your time and consume it in small quantities.
- This site is for people who appreciate that Truth is objective.
If you believe in subjective "truth", or you do not have tertiary qualifications for computing,
or you have a short attention span, this site is not for you.
While Memory Tag pages look great, cover the named subject fully, and work well for experienced people,
they are not as effective for people who have not had the benefit of the course: there is nothing in the
memory to tag. Therefore I get a lot of questions, and I keep upgrading the content (please check for updates).
The material has now left Memory Tag territory, it is approaching textbook level.
Two formats are provided.
These documents were originally formatted and produced as stand-alone PDFs for download.
This will not change because the portability, quality and nagivation are unmatched; the complex illustrations show
up best in PDF; and they are heavily cross-referenced via links. Blue glass buttons provide navigation across the major headings.
Use the full power of your PDF viewer capabilities, including Search, etc, if you can open them.
Unfortunately the combination of certain browsers::PDF viewers cannot open the PDFs
(so much for the ability of commercial programs to read Standard-compliant files).
- HTML Image Map
Based on your feedback most of the documents have been re-published as HTML,
from the same source, for viewing in sub-standard browsers.
This is rendered as one A4 PNG image per webpage, with image quality understandably poorer than PDF.
Use your browser capabilities: either use the Next
links and follow the material in the sequence it is presented;
or select the objects and jump around to follow your interest.
Blue glass buttons provide navigation across the major headings.
- Document Map
Decades ago, a few single pages derived from our education materials were posted, as reference to my
discussions on fora. Over time, on the basis of feedback and continued interaction, this has grown
into several documents, now integrated and cross-linked.
The sections in each document keep growing as well.
What was once a number of single pages which were issue-oriented, has evolved into structured,
reference-oriented, multi-page documents.
A Document Map is now called for; this allows you to review the structure and subject sections and to
go directly to them.
- Sybase ASE Architecture V2.0
This is offered as an alternative to the pathetic diagrams purporting to be "architecture" diagrams, that squeeze a thousand
objects onto a single page, which are merely an inventory of parts, devoid of meaning.
Essential information for administering or tuning a Sybase server a complete and structured
narrative of the Adaptive Server Enterprise.
The subject is introduced progressively, in ten pages of 'edible' illustrations.
The Process Kernel must be thoroughly understood first,
before the Threaded Kernel can be contemplated,
therefore they are presented chronologically, and full detail is provided for each.
- Explains the architecture progressively, in four increments
- Explains Kernel operation, limits, and common misunderstandings
- Explains all major features of ASE, such as Parallelism, in context
- Enumerates all componentry and resources, enabling sysmon reports to be interpreted effectively
- Enables configuration parameters to be understood: what they are; their purpose;
their relation to other parameters
- And finally, the Threaded kernel is explained.
- When contemplating a problem in the server, this enables accurate determination (location) of the
relevant component and resources
- Answers a thousand questions, such as, I have doubled the number of locks, why am I still
- Neither ASE, nor ASE Architecture, nor a diagram of ASE Architecture, is for the faint-hearted.
Oracle vs Sybase ASE V1.0
PDF (No HTML)
For Oracle types, and others without technical qualificiations,
this is a pre-requisite to understanding Sybase ASE Architecture.
The facts re the product "architecture" are clear to technically qualified people,
they know it is not a "server" by any stretch of the imagination.
Technicallly unqualified people however, are easily fooled by much-repeated marketing;
and the occurrence of the word "server" on every page in the manuals,
thus they think their product is somehow a "server",
and therefore that they already understand what a server, or server architecture, is.
This prevents them from learning about servers and server architecture,
it subverts genuine understanding.
Therefore, a quick illustrated comparison is provided, to unlearn the marketed "server",
and to set the stage, to learn a genuine server.
The document incidentally proves why the obese orable product needs ten to twenty times
the resources as Sybase, for the same workload,
and why it is a hundred times slower, but that is not the main intent.
Freeware "SQL" Platform
Note that the 'orrible creature has the same non-architecture as freeware/shareware/vapourware
SQL platforms, thus;
- The "architecture" of oracle places it squarely in the freeware
SQL platform category. With all the wonderful high maintenence issues that go with it.
- Although oracle components are explicitly named, the document applies to all such platforms.
In other words, if you would like to understand the "architecture" of any such product,
why your freeware flatporn performs like a tortoise in heat, examine this document.
- Data Storage & Fragmentation V2.5
The three documents below, provided in a single PDF, with all links and references resolved.
32 condensed pages.
- Data Storage V2.5
Detailed and illustrated.
Defines Units of storage and their relations; the five DataStructures and Partitions; all Index
Structures and their relations; and introduces Devices and Segments in context. Understanding
Data Storage and DataStructures is essential, in order to administer them effectively,
and to understand performance of the DataStructures, as well as the performance of a server that uses them.
Those hunting the snark or the DOL "clustered" index will find it here.
Those seeking a deeper understanding or intending to write utilities may appreciate
the Sybase Catalogue pertaining to DataStructures; and the exposition of all DataStructures and their relations
in terms of a full Data Model.
- Fragmentation V2.5
Detailed and illustrated. Version 2.5 is substantially expanded, based on your feedback (thank you!).
- Defines all the Types of Fragmentation, within three distinct Levels, as it applies to each Data Storage Unit,
and the impact at each Level/Unit
- New chapter on Determination of each Type of Fragmentation, detailing not only the methods,
but the considerations for each metric
- Each type of Fragmentation illustrated and discussed
- For DOL-only Fragmentation, the effect of each command is illustrated and discussed
- Explains why common de-fragmentation operations do not work
- Provides a solid foundation for prevention of Level I Fragmentation; returning great speed to the Data Structures;
and for reducing the frequency of de-fragmentation operations
- Provides methods of correcting Level I & II Fragmentation
- Where the difference between APL and DPL/DRL lockschemes is relevant, these are compared side-by-side.
- Index Type V2.5
An older document, it describes and compares APL vs DPL/DRL Index Types in summary form, on a single page.
- Cache Behaviour V2.3.1
A full explanation of all aspects of the behaviour of ASE caches and pools, using illustrations: how they operate;
considerations re the Wash Area;
the two cache strategies; overload scenarios; large I/O pools, in terms of both reading and writing;
partitioning the cache; and Asynch Pre-Fetch.
The information is essential for understanding ASE caches, and for configuring and tuning them correctly.
The only element not detailed is site-specific cache sizing and configuration guidelines
(our High Performance Configuration,
which includes the configuration of a full set of site-specific resources, is available for purchase or
as an implementation within an assignment).
- Statistics Demystified V2.1 (Draft)
Simplifies and fully explains Sybase ASE Statistics.
The subject of Statistics is neither complicated, nor simple.
The mechanics and considerations simply needs to be understood.
However, it is made hugely complicated by the denizens of the web, particularly the self-styled "gurus".
This document explains Statistics in a straight-forward manner, and removes the unnecessary complexity.
Presented with many illustrations, in three parts,
with two examples of meaningful Statistics reports from our utilities:
- Concepts: that need to be understood,
in order to identify which Stats are relevant, and to maintain them properly
useful for a deeper understanding of issues, and for writing your own Statistics reports
- UPDATE STATS:
the effect of each of the different flavours of the command.
- Help Statistic:
displays Statistics and their relations to all relevant items (Indices; other Stats; columns) on one page,
at both the Table and Partition levels.
- Help Histogram:
displays the Histograms in a human-readable format,
and has advantages over optdiag.
A consolidated report (Statistics plus Histograms in 3 columns) intended for landscape printing is also provided,
again at both Table and Partition levels.
- Lock Manager V2.6
An illustrated overview of the operation of the Lock Manager, in three pages.
It shows all commonly encountered types of locks within ASE; how they are used; how they are escalated and promoted.
It tabulates ISOLATION LEVELS vis-a-vis locks,
and clarifies the transaction/session/query/table context.
Configuration considerations and the different types of contention resolution metods (locks, latches, LW Protection,
and Spinlocks) are discussed.
- MDA Collector V1.4
MDA Source PDF
In our Technical Audits, Performance Tuning & Verification, we use Sysmon exclusively,
due to its richness and zero overhead;
and provide a full-featured Sysmon Processor.
For those customers who insist on MDA, we support the Monitoring aspect of MDA tables
(of which the overheads are bad enough)
as a second preference to Sysmon, but not the Diagnostic aspect
(of which the overhead and processing requirements are horrendous;
there are much cheaper and less onerous methods of diagnosis),
we provide a full-featured
These documents posted here because we are told they assist people in understanding MDA,
and in writing their own MDA Collector.
The first document is a single page overview of the MDA Collector database, s
howing the ERD and the command syntax for this utility.
The second document identifies the MDA source for each table; the cofiguration options required;
and the MDA overhead for each; it may be laid over the first document.
- RAID5 vs RAID1+0
A substantiated review, with many links, for those who need to overcome the false information provided by SAN salesmen and lazy StorageTeams,
that one great big RAID5 volume is acceptable for databases. Page 5 provides an illustration.
- SAN Configuration Guide for Sybase ASE
The several levels of configuration of the Logical Volumes in the SAN is very important,
as it seriously affects the performance and isolation of your ASE server and your databases.
We provide a complete Guide, that addresses the issues and consequences in detail,
currently over 50 pages, available for purchase.
The document above identifies the first line of issues that require address in response to the pathetically slow RAID5,
which is commonly pressed as the answer to every data storage requirement; it is provided free of charge.
Page Updated 12 Sep 17 ·
Gratitude or feedback