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