SAP ASE Internals

[Derek_Asirvadem]

I am an old-school  high performance consultant for high end customers in Australia.  I was active on the old ASE community website, which I understand is now archived.  I have retired, but I have been dragged back to do a few things.  New to the SAP environment and SCN community.  I have been using Sybase (now SAP ASE 15 & 16) since 1993.  Over the decades, I created a few ASE internals documents, which may be of interest to DBAs who are trying to understand ASE; its architecture; how to improve performance of both the server and your database.  Enjoy.

Sybase GEM Documents

(The "SAP Managed Tags" do not allow just SAP ASE !!!)

[sladebe]

Wow, really nice.

Unfortunately, I think some things are out of date now.  For example, in Sybase ASE Architecture Foundation, it says:

"All resources are located in a single Shared Memory Segment, allocated, and usually locked • which is shared by the Engines (Unix Processes)"

Is describing the old "process mode" for the ASE kernel.  The new "threaded mode" uses native OS threads.  Internally ASE creates "engines" which are really just thread queues mimicking the old process based engines.   Threaded mode makes output from monitoring tools like sp_sysmon weird because none of the "engines" (thread queues) are ever waiting for I/O (a ASE "process" (really a thread) goes idle waiting for I/O, but sp_sysmon doesn't show that)

I'm not sure where to look to see how much of the time a user process was waiting for I/O to complete

[Derek_Asirvadem]

> Wow, really nice.

Thank you.

> I think some things are out of date now

The document was published 13 years ago, yes, but the information is not out of date.

> ... describing the old "process mode" ...

1. The document is structured and incremental, one needs to understand the Process Kernel first,  in order to properly understand the Threaded Kernel.  You need to read past page 1.  
2. The applicable ASE version is stated at the bottom right of each page
3. Go to page 8 for the Threaded Kernel.

Your further comments are about ASE, not the subject document.

> Internally ASE creates "engines" which are really just thread queues mimicking the old process based engines.

No. 

• In both modes, ASE uses o/s Threads, but do not let that confuse you
• In Threaded mod, ASE runs as a single (unix) process,
  • with the Threads exposed and named properly such that you can monitor them
• In Process Mode, ASE does not "use threads to mimic engines",
  • ASE runs one (unix) process per Engine
  • with the o/s threads quite internal (for a simplified understanding only, you can conceptualise that as one o/s thread per Engine, but it is a different Mode, it is not running in Threaded Mode and mimicking Engines).

> Threaded mode makes output from monitoring tools like sp_sysmon weird because none of the "engines" (thread queues) are ever waiting for I/O (a ASE "process" (really a thread) goes idle waiting for I/O, 

Much confusion.

• In Threaded Mode, ASE does not have Engines, it has Engine Threads and I/O Threads. 
• Yes, Engine Threads never wait for an I/O, that is by design. 
  • I/O Threads wait for I/O to complete
  • Do not look for I/O issues in Engine Threads, or Engine issues in I/O Threads
• an ASE "process" is an internal concept, a spid, it is not an o/s thread or ASE Thread,  It is properly named Task in my document. 
  • Do not confuse ASE Threads or o/s threads with ASE internal Tasks, they have northing to do with each other, there is no equivalence
• an ASE Task (spid) does not "go idle" while waiting for I/O to complete, it goes "waiting for I/O to complete" (sp_who = "sleeping")
• an ASE Task (spid) "goes idle" when it has not sent a batch to ASE, when it is not waiting for anything from ASE, ie. ASE is waiting to receive a batch from the Task (sp_who = "recv sleep")
• In my document page 9, see if you can differentiate:
  • ASE = single unix process, dark grey 3D square
  • ASE Engine Thread Pool = blue 3D squares
  • ASE I/O Thread Pool = blue 3D squares
  • ASE internal Task (spid)
    • Run Queue = blue 3D spheres
    • Sleep Queue = red 3D spheres

> but sp_sysmon doesn't show that)

Sysmon does show that, in the I/O Thread Pool (syb_system_pool) metrics, not in the Engine Thread Pool (syb_default_pool) metrics. 

> Threaded mode makes output from monitoring tools like sp_sysmon weird 

Sysmon provides ASE (server level) metrics, not Task level metrics.  In Process Mode, sysmon provides Engine (Process) metrics (see example below), in Threaded Mode, sysmon provides metrics for each Thread Pool.  I have no problem at all reading and understanding sysmon metrics, but one needs to examine the overall metrics, over time, and aggregate and display the metrics in a meaningful way (see below).

Cheers
Derek

[Derek_Asirvadem]

Server Not Herd

ASE is not a herd of unix processes ala open farce "sql" offerings, a "server" in name only, wherein each user process is one unix process. ASE is a genuine server architecture, a single unix process, using the fewest o/s Threads, and running Tasks (spids) internally. Read the entire Database Server Architecture section on my webpage, and refer to the Oracle & Freeware vs Sybase ASE document for details.

> I'm not sure where to look to see how much of the time a user process was waiting for I/O to complete

1. That is a larger question, and performance monitoring and tuning needs to examine all the related metrics, looking for a single metric will not help. Likewise, changing a single configuration parameter without understanding the concert that ASE is playing will obtain very poor results and could even degrade performance.
2. That is an Oracle or Freeware mindset, it does not apply or help in understanding the ASE Architecture.

It is great that you are using my document in getting to know ASE Architecture, but it is not a performance tuning tutorial. To begin with, you need to read and understand it incrementally, do not stop at page 1 and make assumptions., read the whole thing.

In attempting to answer your question at least partially ...

• an user process (an ASE Task, a spid) does not "wait for I/O to complete", ASE throws it out and grabs the next Runnable Task from the Run Queue and runs it.  This is a Task Context Switch.
• so you need to look into why ASE is Context-Switching Tasks out, such as Read Lock; Write Lock ; etc (there are many).  
• That is, inspect the server and why it is doing what it is doing (why it is handling all the hay the way it does), do not try to inspect the task (that is. one particular straw, a needle in the haystack).

First grab some sysmons that cover a reasonable period such as 1 hour (or 24 x 60 mins) or the business day (8 x 60 mins), and aggregate the metrics such that they are meaningful for the intended purpose.   Sysmon Date Report (Before & After).  This shows 24 x 60 mins aggregated into single days,  two days compared: before and after I made an improvement to the I/O subsystem, and the difference (rightmost Delta column ).

• examine just the first two sections, Selection is KPIs
• look at the entries below Kernel/Context Switch, those are the reasons a Task that was being executed got thrown out (waiting for something)
• in terms of Server metrics, your  "I/O Wait" is either a Cache Miss (actual disk read) or a lock wait (other resource wait) of some kind
• the lock type (finer granularity) informs us re exactly what resource Tasks are waiting for.  Fully exposed in the Lock Manager section, and summarised as Write Lock & Read Lock in the Selection section.
• In this example, the server is not I/O bound, it is network bound: Kernel/Context Switch/Cache Miss is only 12% whereas Kernel/Context Switch/Network is 63%.

As generic advice, most servers are I/O bound, especially if you have done little or nothing to enhance the I/O subsystem.  Therefore anything and everything that you do to (a) enhance the speed of the I/O subsystem, and (b) each of the DataStructures in errant tables, will provide immediate benefits.

Cheers
Derek

[venkatamandavilli]

@Derek_Asirvadem 

This is a valuable deep-dive for anyone working with SAP ASE, especially because internals often matter more than surface-level setup when you are troubleshooting or tuning performance. I like that articles like this help make the database feel less like a black box and more like something you can reason about confidently. It seems especially useful for engineers who need a stronger understanding of how ASE behaves under the hood.

[sladebe]

@Derek_Asirvadem 

First of all, thanks for posting this really useful info.  There isn't that much like it online anymore.   Now I'll ask some questions about what you're saying:

Re: In Threaded mod, ASE runs as a single (unix) process

In my RedHat/Centos version of Linux, when running ASE in threaded mode, I see this in Linux:

> ps -eLf | egrep "PPID|dataserver" | cut -c1-123 | head -5
UID          PID    PPID     LWP  C NLWP STIME TTY          TIME CMD
syblogin   10056   10043   10056  0   21 May07 ?        00:00:30 /myhomedir/bin/dataserver  
syblogin   10056   10043   13287  0   21 May07 ?        00:05:48 /myhomedir/bin/dataserver  
syblogin   10056   10043   13288  0   21 May07 ?        00:00:03 /myhomedir/bin/dataserver  
syblogin   10056   10043   13289  0   21 May07 ?        00:00:02 /myhomedir/bin/dataserver  

So yes, ASE is running as a single process (with PID 10056 in this example), but there are clearly Linux light weight processes (LWP) for each thread in ASE.  I'm pretty sure these LWPs weren't used in the old "process mode" (but I don't have any process mode servers running anymore to check this).

My server has threads configured the following way:

myserver > sp_helpthread;
 Name              Type                 CurrentSize TargetSize IdleTimeout Description                                  InstanceName
 ----------------- -------------------- ----------- ---------- ----------- -------------------------------------------- ------------
 syb_blocking_pool Run To Completion              4          4           0 A pool dedicated to executing blocking calls NULL        
 syb_default_pool  Engine (Multiplexed)           8          8         100 The default pool to run query sessions       NULL        
 syb_system_pool   Run To Completion              3          3           0 The I/O and system task pool                 NULL        

So sp_helpthread is showing 15 threads.  Interestingly, at the Linux level, I see 21 threads/LWPs.  Probably a few ASE system threads not showing in sp_helpthread, that's fine.

I generally think of lightweight processes in Linux as OS implemented threads.   From what I'm reading online, threads in Linux are actually processes which happen to share some resources with the parent "thread" (see Light-weight process (Wikipedia))

FYI, you can run "top -H" to see ASE activity at the lightweight thread level.

Re: sp_sysmon stuff

Generally speaking, I was trying to say that sp_sysmon shows things from the engine/thread point of view, not from a user task (spid) point of view.  Ie., you can see that the overall ASE server is busy or now, but it's hard to tell what an individual spid is bottlenecked on.

It's possible to get some idea what individual spids are doing using the "appmgmt" option within sp_sysmon:

> sp_sysmon "00:00:15",appmgmt,@applmon='appl_and_login';
...snip...
  Application->Login:   MyApp->MyLogin

  Application Activity            per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------
    CPU Busy                          3.1           0.0          46       5.1 %
    I/O Busy                         57.3           0.0         860      94.5 %
    Idle                              0.3           0.0           4       0.4 %

    Number of Times Scheduled      4170.1           3.2       62552       n/a

  Application Priority Changes    per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Total Priority Changes              0.0           0.0           0       n/a

  Application I/Os Completed     per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Disk I/Os Completed            1143.1           0.9       17146      13.1 %
    Network I/Os Completed         7582.0           5.8      113730      86.9 %
  -------------------------  ------------  ------------  ----------
  Total I/Os Completed             8725.1           6.7      130876

  Resource Limits Violated      per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Total Limits Violated               0.0           0.0           0       n/a
...snip...

Where, according to the docs, CPU, I/O, Idle counts are defined as:

• CPU Busy – reports the number of clock ticks during which the user task was executing during the sample interval. When the numbers in this category are high, it indicates a CPU-bound application. If this is a problem, you may want to consider engine binding.

• I/O Busy – reports the number of clock ticks during which the user task was performing I/O during the sample interval. If the numbers in this category are high, it indicates an I/O-intensive process. If idle time is also high, the application could be I/O bound.

  The application might achieve better throughput if you assign it a higher priority, bind it to a lightly loaded engine or engine group, or partition the application’s data onto multiple devices.

• Idle – reports the number of clock ticks during which the user task was idle during the sample interval.

• Number of Times Scheduled – reports the number of times a user task is scheduled and chosen to run on an engine. This data can help you determine whether an application has sufficient resources. If this number is low for a task that normally requires substantial CPU time, it may indicate insufficient resources. Consider changing priority in a loaded system with sufficient engine resources.

The way I'm reading this, "Idle" ticks only occur for a spid if it's scheduled.  But if the spid just isn't doing anything at all, then I guess it doesn't get any idle ticks??  That's backed up by observations.    The sum of CPU busy + I/O Busy + Idle ticks doesn't add up to a constant number.

I'm not sure if a spid which is scheduled and waiting for an I/O to complete, shows up as I/O busy ticks or Idle ticks.  Maybe I'll add a separate posting about that.

[sladebe]

Actually, I'll correct/answer myself in the previous post. 

The help doc says I/O busy ticks are for a user task (spid) "performing I/O" (which is not precisely defined), but if a spid also has idle ticks, then it could be I/O bound.

So this seems to say that a spid waiting for an I/O to complete shows up as "idle"

[venkatamandavilli]

@Derek_Asirvadem 

This continuation of the SAP ASE internals discussion is valuable because it shows people actually digging into low-level details instead of just talking around the database. I like that the conversation touches on internals topics like signal handling and internal error levels, since those are the kinds of things that matter when you are troubleshooting deep issues. It feels especially relevant for engineers who need to understand ASE behavior beyond the surface-level documentation.

[Derek_Asirvadem]

@sladebe 

> In my RedHat/Centos version of Linux, when running ASE in threaded mode, I see this in Linux 
there are clearly Linux light weight processes (LWP) for each thread in ASE
No.  You are looking at the o/s utility (ps), the ASE Threads are visible inside ASE, not outside in the o/s.  Those LWPs are the [3] client connections to ASE, running as Sub-Processes, under the ASE Server [single] Process.  P & T Basics p31.  Eg 3 x isql sessions, unfortunately on the same host machine.

> My server has threads configured the following way:
No. you are looking at ThreadPools, not Threads.  syb_default_pool is the ThreadPool that runs Engine Threads (client tasks; spids).  My doc, page 9.

If you want Threads, do:
isql> sp_helpthread syb_default_pool

> So sp_helpthread is showing [a count of] 15 threads.
Yes, that there are 15 Threads, not the Threads themselves..

> Interestingly, at the Linux level, I see 21 threads/LWPs.
So what.  (Already explained above.)

> Probably a few ASE system threads not showing in sp_helpthread,
Please, this is IT, hopefully we are qualified technical people, we need to be precise and clear, arithmetic works, we are not baristas, where the coffee beans go missing, somehow, somewhere, and its "fine".

> that's fine.

1. If you can't find something, or the arithmetic does not add up, it is definitely not "fine." 
   The ASE Threads are visible in ASE/sp_helpthread <poolname>,
   the os/Threads are visible in Linux/ps.
2. You are conflating ASE Engine Threads and Tasks (spids).  Please stop. 
   They are not the same, please read my doc carefully, every word counts.  Page 2 first, and only when you fully understand that, page 9.  
3. If you still can't differentiate ASE Engine Threads and Tasks (spids), I am afraid I cannot help you.

> I generally think of lightweight processes in Linux as OS implemented threads.   From what I'm reading online, threads in Linux are actually processes which happen to share some resources with the parent "thread" (see Light-weight process (Wikipedia))

1. Don't worry about what you think, just learn about objective reality (we are scientific, yes ?).  Your thinking becomes relevant only when your mind conforms to objective reality.  I can best help you by declaring what ASE does (objective reality) and by not addressing your thinking.
2. Don't read online, the internet is a cesspool, people with two fingers and a bit of connective tissue eagerly post the most absurd bottom-up solutions, blissfully ignorant of the fact that they are ignorant (read the Dunning-Krüger Effect).  Wikipedia is particularly bad because it is (a) heavily biased, and (b) provides masses of irrelevant detail without proper definitions, leaving one confused and unresolved, in the Middle, breaching the Laws of Thought.  Here it gives the most generic description (not definition) of an LWP that could be on any host system.
   • ASE LWPs are a particular implementation that is not known to Wiki.
   • you have purchased SAP/ASE, it comes with a full set of manuals. 
     SAP ASE Books Online
     Download the PDFs, read every TableOfContent, so that you know what all the subjects are, and where to find them when you need to.
   • read the ASE manuals and nothing but the ASE manuals
   • My docs are not a substitute for the manuals, rather they bring together all the info on a subject that is otherwise situated in bits and pieces throughout various manuals
   • if there is anything that you do not fully understand, stop, and ask a specific question here

Repeating, there are, discretely:

• os/Processes = Engine Threads
• os/Sub-Processes = Client connections within (or "under") an EngineThread
  an EngineThread executes a spid, until it ContextSwitches, and then executes the next spid, etc
• ASE/ThreadPool x 3
  • ASE/Thread Pool/syb_default_pool = Engine Thread
  • ASE/Thread Pool/syb_system_pool = I/O Completion Thread
  • ASE/Thread Pool/syb_blocking_pool = Blocked Task Completion Thread
• ASE/Task (spid) = ASE Internal object that is executed by an Engine Thread
  much like on Linux, a pid is an internal object executed by an os/Thread
  NB. The thing that is executing, and the thing that is being executed by the thing that does the executing, cannot be the same thing.

> FYI, you can run "top -H" to see ASE activity at the lightweight thread level.
Not really.  Not to say that is not useful, but that is at the os level only, and concerns ASE overall, it says nothing about  spids, or a particular spid that you are trying to investigate.  And even for the full ASE (all Threads and all LWPs), you need all the Linux metrics, taken together., not just one.

Again, Unix level metrics are for determining issues to tune the server, not relevant fo spids.

> Generally speaking, I was trying to say that sp_sysmon shows things from the engine/thread point of view,
Yes.

>not from a user task (spid) point of view.
Yes.

> Ie., you can see that the overall ASE server is busy or now,
Yes.  (But the server is mostly waiting for I/Os to complete.  Idle means no load, and you cannot diagnose anything.)

>but it's hard to tell what an individual spid is bottlenecked on.
Yes.  sp_symons provide rather excellent server metrics. 

Why are you looking at the server or server metrics when you are trying to investigate a Task (spid) ???

Immediate Answer

To understand what a Task (spid) does, how it will be executed (eg. what I/Os it will cause; etc),

1. first and foremost, use
   sp_who <spid>
   that is its purpose, to provide info about the operation of a current spid
   • learn all the statuses, and what they mean
     
     
2. obtain the Query Plan to see what the spid is doing.  Do (session settings, not SQL commands):
   isql> SET SHOWPLAN ON
   
   
3. After having understood what actions your spid is going to cause in the server, to investigate the performance of a spid, do:
   isql> SET STATISTICS IO ON
   isql> SET STATISTICS TIME ON

Long Term Answer

• There are great ASE facilities to monitor the server, as well as spid activity, overall and particular, read up on it.  Monitoring (MDA) tables.  Much overhead, depending on what you capture.
  • I don't have need for them because I established a suite monitoring for Sybase (eg. decades before the advent of MDA tables, sp_sysmon is quite adequate for me, and zero overhead.  Another example, presented by the hour.
• There are great third party monitoring tools as well.  Expensive but brilliant.  Bradmark Surveillance. top shelf for ASE server level, and does a decent job for spids as well.
• If your application is third party (ie. you do not have the source code for the spid or sproc or whatever), set up ASE Auditing, and capture what it is doing, then the [1][2] above will make sense.

> It's possible to get some idea what individual spids are doing using the "appmgmt" option within sp_sysmon:
Not a snowball's chance in hell. 

Before you attempt to interpret an sp_symon,

1. make sure the sample you are collecting is relevant to your investigation (right now it is not ... looking at server metrics for task (spid) metrics ... there are hundreds or whatever Tasks running, plus various server internal tasks
2. please read the P&T/Monitoring manual, so that you (a) know what you are looking at, what each metric means, and (b) how to interpret it
3. Application Management is an ASE facility, which you have not configured, and are not using, that section is irrelevant to you, and has nothing to do with a spid or the spid that you are investigating

> Where, according to the docs,
Ok, so you are looking at the manual, but you have picked the detail under Application Management without reading the section on Application Management, which states:

• "This section is useful if you use resource limits, or if you plan to tune applications by setting execution attributes and assigning engine affinity."
• and useless otherwise

Repeating [3] above.

Please do not copy-paste the manuals, please link to them and specify a page.

> The way I'm reading this, "Idle" ticks only occur for a spid if it's scheduled. 
...<snip> all the way to ...
So this seems to say that a spid waiting for an I/O to complete shows up as "idle"

1. Those metrics are relevant to the server, specifically the Application Management breakdown, not the spid
   you have not specified the parameters that you gave to sp_sysmon
2. you need to understand the states (statuses) that a spid encounters, and what it means.  Again, start with sp_who, and read up on the statuses.
3. Then the SET STATISTICS, etc, as explained above.

Wait.  If you are running a limited, controlled number of Tasks exclusively on the server and then trying to interpret the Application Management section, that is a really long-winded, laborious and bottom-up way to find out what the task is doing.  That is like running a high speed long-distance train, fully equipped with staff and a restaurant carriage, say 8 passenger carriages, with just one passenger, and collecting train stats to determine how the single passenger is travelling.  Further, how poorly or how well your SQL is written (performance-wise and quality-wise) seriously affects the way the Engine has to deal with it.

• Go with the top-down, normal method explained above.  It will help you to progressively understand the server/EngineThread vs the Task (spid).
• Nevertheless, since you are already in the guts of the sp_sysmon report, in whatever context you have, the Task Management section will give you slightly more relevant metrics: why the [single ?] Task is being Context-Switched out by the EngineThread that is executing it.

> So this seems to say that a spid waiting for an I/O to complete shows up as "idle"
No.  That section is badly written, it is giving you Engine metrics but constrained to the AppManagement set up and parameters that you gave it (therefore you cannot expect the percentages or numbers to add up).  Look at the Kernel section for un-perverted CPU usage figures..

• Idle means the Task has not sent a batch of SQL to the sever.  The sp_who status is recv sleep.
• Scheduled means made [sp_who status] runnable, as in finished-waiting for I/O to complete, and now waiting for an EngineThread to pick it up and execute it.

In this non-applicable report for a tiny sample, waiting for [all, not one] I/Os to complete shows up in the CPU section, as I/O Busy.  The sp_who status is sleeping.

For this tiny, probably meaningless sample, your Tasks are I/O bound, more on the network than on the disk, and your network is suh-low.  Remove the sending of the result set back to the client (so that you do not incur network I/O, and obtain stats that better indicate the processing and disk I./o).

More

Here is something to consider, probably too detailed without an explanation.

[venkatamandavilli]

@Derek_Asirvadem 

This follow-up in the ASE internals discussion is valuable because it keeps the focus on real architecture and operational behavior rather than treating the database as a black box. I like that the conversation points back to threads, shared memory, and execution behavior, since those details are exactly what help engineers troubleshoot and tune ASE effectively. It feels especially useful for people who need a deeper working understanding of how the server behaves under load.