Purpose

The purpose of this page is to provide a deep understanding of the table buffer(s) and provide techniques for troubleshooting the table buffer(s). It will help connect concepts of the table buffer to the various monitors and screen at your disposal in any ABAP AS system.

What is table buffering?

Database Table Buffers

Types

For details on the types of buffering offered, see:
ABAP - Keyword Documentation : Table Buffering - Buffering Types

  As you can see, a generically buffered table is subdivided based on the values in the first n key fields to create generic key areas (or simply key areas for short; this term is important in load times and understanding the buffer state later on). 

The more key fields that are included in buffering, the more defined, and smaller, each generic key area becomes (also depending on the cardinality/uniqueness of each key field).

Every fully & generically buffered table is first divided by client number (MANDT field). This means that a table which is generically buffered by only 1 key field is equivalent to full buffering. (For evidence of this, see section explanation of the multiple state.)

Areas in memory

Depending on your kernel release, there may be one or two different table buffer areas in memory. In kernel releases 7.2x and lower there are two table buffers:

• Generic key : tables configured with Full buffering and Generic buffering are stored here.
• Single record : tables configured with Single record buffering are stored here.

In kernel releases 7.4x and above, there is only one table buffer, where tables of all buffering types are stored.

The basic memory information regarding the table buffer(s) can be checked via transaction ST02.

If your system is on SAP_BASIS release 740 with kernel 7.4x or higher, you can observe the proc memory usage of the table buffer per WP ( What is PROC memory? ).

In SM50 select the WP you are interested in and go to Aministration > Work Process > Write Stack. Then click the "View dev traces" button (  Ctrl + Shift + F8). At the bottom, the C-stack and proc memory will be written. In the proc memory section, there should be a DBI_MM tag. This is the amount of table buffer proc memory currently allocated by this work process.

Parameters

To control the size of the table buffer(s), refer to the following notes based on your kernel release:

7.2x and lower: SAP Note 480710 - Profile parameters for table buffers (for SAP Kernel Release 7.2x and below)
7.4x and higher: SAP Note 2103827 - Profile parameters for table buffer as of SAP Kernel Release 7.40

To influence buffer synchronization, refer to the parameters in the following:

• Buffer Synchronization in Help Portal
• SAP Note 14754 - Profile parameters for buffer synchronization.

Synchronization is generally not to be changed during normal operation of a NetWeaver system.

State of a table/key area

In ST10 and AL12 (in kernel > 7.4x: AL12 > Monitor > Buffers > Table Buffer > All Objects) you can find the current state of a table/generic key area in the buffer.

• • Valid – Data will be taken from buffer for next read access.
  • Invalid – The table/generic key area has been invalidated. Data is changed but changes are not committed. Data retrieval would be from the database.
  • Pending – Data needs to be refreshed. Any Open SQL that would access this table will instead go directly to the DB.
  • Loadable – Data will be refreshed on the next execution of an Open SQL statement that would access this table from the buffer.
  • Loading – The buffer data of this table is currently being refreshed.
  • Absent – The table is not in buffer at all. For example, no access on the table occurs from the server so far. Data would be loaded into memory upon next read request.
  • Multiple – Only visible in ST10 (not AL12). Occurs when the key areas of a buffered table are in different states. E.g. some generic key areas are pending but some are valid.
  • Error – Table data cannot be placed into buffer due to error like space, etc.
  • Displaced - Table was displaced to due insufficient space in the buffer.

State changes in a key area

Note that the terms table & generic key area are generally interchangeable in this section.

1.  Loading should be quick and difficult to spot in ST10/AL12. If Loading is frequent or long running, perhaps the size of the table/generic key areas & invalidation rate are not suitable for the current buffer settings.
2. As mentioned in the last section: I've never observed status Invalid in ST10/AL12. The state is generally seen to go from Valid to Pending.

Additional information on the buffer state can be found here:
How is buffer state and table call statistics changed?

Explanation of the Multiple state

In ST10, tables that are fully buffered or generic key buffered can display state Multiple. This is because different generic key areas can be in different states at the same time and ST10 simply aggregates the data for all generic key areas into a single entry, thus, if multiple generic key areas have different states, multiple is displayed in ST10.

If you want to see the individual statistics for the generic key areas of a table, you have to use AL12 > Monitor > Buffers > Table Buffer > All Objects.

Because fully and generically buffered tables are first divided by client number, even a fully buffered table can have a state of multiple in a system with multiple clients.

Accessing & Bypassing the table buffer

Only certain Open SQL statements will access the table buffer. For details of what statements can and cannot access the table buffer, see the ABAP - Keyword Documentation pages for:

Table Buffering - Buffering Types
&
Table Buffering - Restrictions

Testing & tracing access to the table buffer

To test access to the table buffer, use SE17.

SE17 is similar to SE16, however, SE16 always add a TOP N clause (or equivalent, depending on the database) to the native SQL statement, bypassing the buffer.

It is possible to directly trace buffer requests in ST05 using the Buffer Trace trace type. This is useful if you are unsure whether or not an ABAP statement uses the table buffer.

Database Interface (DBI) component

The DBI is responsible for managing & maintaining the table buffer (recall that the area in PROC memory where buffered tables are stored is named DBI_MM).

Once an Open SQL statement is encountered in an ABAP program, the first component to receive the Open SQL request is the DBI. The DBI then determines if it can fulfill the request based on the type of statement (see the Statements that access/bypass the table buffer section) and the state of the requested table in the buffer and handles the request accordingly or sends it to the DBSL.

Here is a rough overview of what happens when an Open SQL statement is encountered in an ABAP program (also, see the first diagram at the top of this page).

<to do: make this a graphic/flow chart>

1. An Open SQL statement is encountered and the request is sent to the DBI
2. The DBI determines if it can fulfill the request using the table buffer based on statement type
   1. Able:
      1.  For the table in the request, the DBI then checks the state of said table in the table buffer
         1. Valid:
            1. The request is fulfilled by the DBI using the buffered data
         2. Loadable:
            1. The DBI refreshes the data in the buffer by querying the database directly (via the DBSL; the state of the table will be Loading here; see next section for more information)
            2. The table goes into the Valid state (assuming no errors occur during table loading)
            3. The request is then fulfilled by the DBI using the newly buffered data
         3. Anything else:
            1. The request is forwarded to the DBSL
            2. Depending on the state of the table, it may go from Displaced to Pending, or Pending to Loadable.
   2. Not able:
      1. The request is forwarded to the DBSL where it is transformed into a native SQL statement the database will understand.

SQL statements that refresh the table buffer

When key areas of a table in the table buffer are refreshed, there is a recognizable pattern of SQL calls made to the DB to obtain the data needed to refill that key area.

It is good to be able to recognize these statements as we regularly encounter issues of:

• • sudden increase in DB response time
  • WP spending a lot of time on action sequential read (on a supposedly buffered table)
  • high CPU consumption in the DB

Which are caused by inadequate buffer configuration.

If n is the number of key fields by which to buffer a table (fully buffered table: n = 1 (only MANDT) ; generic buffered table: n >= 1), then, Generic key areas in the buffer:

• • Contain all fields/columns of the table -> SELECT * is used to retrieve all records from the table.
  • Are sorted by primary key -> the SQL statements uses an ORDER BY clause.
  • Are identified by the first n key fields (including MANDT) -> the where clause will contain n predicates (MANDT + n - 1 other key fields)

The actual statement will vary depending on the type of table (transparent, pool, etc).

 SELECT * FROM "A747" WHERE "MANDT"=:A0 ORDER BY "MANDT", "KAPPL", "KSCHL", "KNUMA_AG", "VKORG", "MATNR", "KFRST", "DATBI"

 SELECT * FROM "A747" WHERE "MANDT"=:A0 AND "KAPPL"=:A1 AND "KSCHL"=:A2 AND "KNUMA_AG"=:A3 ORDER BY "MANDT", "KAPPL", "KSCHL", "KNUMA_AG", "VKORG", "MATNR", "KFRST", "DATBI"

 SELECT * FROM "KAPOL" WHERE "TABNAME"=:A0 AND "VARKEY"=:A1 ORDER BY "VARKEY"

 SELECT * FROM "KAPOL" WHERE "TABNAME"=:A0 AND "VARKEY"=:A1 ORDER BY "VARKEY"

Signs which may indicate buffering issues

There are several different signs/symptoms that may indicate an inadequitely configured table buffer or poorly configured buffer settings for particular tables.

In case the problems below are noticed in your system, the troubleshooting steps are generally the same; see Assessing the health of the table buffer.

Runtimes of dialog/background steps are better on certain application servers

If you find runtimes are more favorable on certain application servers, and worse on others, it is worthwhile to check the table buffer configuration on those servers. The relevant profile parameters may be configured differently on other application servers and may not be sized adequately on those servers.

To determine if a table buffer is healthy on a particular server, see Assessing the health of the table buffer.

Runtimes of dialog/background steps are better after a restart

If you find runtimes are more favorable after restarting the application server, there may be a memory leak in the table buffer. To confirm this, see the Assessing the health of the table buffer section.

The most common table buffer leaks that we encounter are detailed in SAP notes 2404710 & 2466145.

Database activity in workprocess monitors (SM50/SM66)

There may be a buffering problem if:

• • when observing the workprocess monitor you observe work processes often and repeatedly waiting on Sequential Read and the table listed in the action detail column is a table for which buffering is configured in SE13
  • And, in the ST04 session monitor (database dependent) you find that the SQL statements being executed are statements that:
    • should be accessing the buffer: Accessing & Bypassing the table buffer 
    • or are refreshing the buffer: SQL statements that refresh the buffer

<to do: add example>

SQL activity in ST12 → SQL Summary traces

There may be buffering problems if, when you've collected an ST12 trace of the performance problem, you notice long runtimes of SQL requests on tables that are supposed to be buffered.

Buffer refresh & access in performance traces (ST05)

There may be buffering problems if, when a performance trace of the performance problem is collected, you notice many long running statements that match the pattern of a buffer refresh/load request ( SQL statements that refresh the buffer ), each followed by another request to the same table under a different cursor/SQL statement which should be accessing the buffer ( Accessing & Bypassing the table buffer  ). This indicates the buffer refresh attempt did not complete successfully and the original request had to be forwarded to the DB to be fulfilled.

Assessing the health of the table buffer

Evaluating the health of the table buffer requires a certain level of judgement and common sense. It's possible the values present in monitors such as ST02 are not conclusive; they may display questionable values, but the table buffer may not actually be the root cause of a performance issue.

Therefore, in this section, anytime guidelines are mentioned on what values they should be (or be above/below), you should consider whether or not you're actually seeing Signs which may indicate buffering issues in your system. If your performance issues are not actually related to buffering, it may not be worthwhile to focus efforts on the table buffer.

Additionally, the values recommended are intended for production systems; measurements are much more liberal for test and development systems.

Overall

The process of evaluating the health of the table buffer overall roughly equates to the following:

Check the KPIs in ST02

The first step in overall analysis is to check ST02 and see if the table buffer suffers from a low hit ratio or frequent swapping.

1. The hit ratio should be as close to 100% as possible.
2. The % Free Space should be >10
3. The % Free directories should be >10
4. The number of Swaps should be near zero

These values must also be kept in perspective of the startup time. For example:

• • • • The hit ratio can be lower than normal if the system has started only within the last several business days.
      • The number of swaps may be in the thousands, but if the system has been running for more than a month, it may be acceptable.
      • The amount of free space and the number of free directories are below 10% very shortly after system startup then concern should be raised

To better understand how the table buffer behaves over time, and to identify days where high swapping occurred, use the History view (Shift+F6 ) . (Note that depending on your basis release, the swap statistics in the History view may or may not be cumulative; use common sense when determining if the swap statistics are for one particular day, or aggregated for all previous days.)

In a distributed system, each application needs to be checked individually. You can check other application servers using SM51 followed by transaction ST02, or by using the Server names buttons in the History view to cycle through each application server.

If any questionable KPIs are noticed in ST02, it may indicate the following:

• • • • An undersized table buffer
      • An overabundance of buffered tables (by table size)
      • A memory leak (e.g. SAP notes 2404710 & 2466145)

More information is needed to determine the true reason behind said KPIs.

ST02 cannot tell you if:

• • • • The buffering performance issue is related to invalidation rates in specific tables (ST10 is required for this)

Check the KPIs in AL12

If you find the table buffer is low on space, suffering from frequent swapping or suffering from a low hit ratio; ensure the space being used by the table buffer is valid, and not wasted. Do this using AL12 > Monitor > Buffers > Table Buffer > Overview.

1. Space with Valid Objects should be within 30% of Used Space, if you notice the space with valid objects is very small compared to the used space, a memory leak may be indicated as per SAP note 2466145.
2. The number of Valid Objects should be as close to the number of Total objects as possible. If the number of Loadable obejcts is significant compared to Total Objects, it may indicate an undersized table buffer, or an overabundance of buffered tables.

If you don't notice any issues with the ratio of Valid to Used Space, then it means the space consumed inside the table buffer is legitimate and not due to a memory leak. This means the swapping is due to:

• • • • An undersized table buffer
      • Or an overabundance of buffered tables

Examples

Specific tables <WIP>

Even if you have isolated the performance issue to a single business process, transaction, or table, I recommend checking the Overall health of the table buffer first. As any tuning or changes made to single tables will be for naught it the root cause is actually an overall unhealthy table buffer. Once you've confirmed the table buffer is healthy overall, then continue with exmaining the specific table(s).

To be used in this section:

• 2404710 - Table buffer: Memory leak in shared memory

KPI Correlations in ST02, ST10, and AL12 <WIP>

ST02 -> Detail Analysis Menu -> Generic Key/Single Record  →   "AL12 -> monitor -> buffers -> table buffer- > overview "

1. Size Allocated  →  Buffer Size / 1024
2. Size Available  →  Data Space / 1024
3. Size Used  →  Space with Valid Objects / 1024
4. Size Available - Size Free  →  Used Space / 1024

ST12 -> Detail Analysis Menu -> Generic Key/Single Record  →   "AL12 -> monitor -> buffers -> table buffer -> shared memory"

1. Size Free  →  "Space Usage in KB" of <free>

"AL12 -> monitor -> table buffer -> overview"

1. Efficiency  →  Used Space / "Space with Valid Objects" (gone in 740+)
2. "Free Directories"  →  "Directory Size" - "Total Objects"

<WIP>

"AL12 -> monitor -> table buffer -> shared memory"

• A detailed breakdown of the shared memory area of the table buffer.
• First view provides the number of blocks and their total size broken down by block/area type.
• If notes 2243084 and 2248618 are applied then Double clicking on any one of those areas provides a histogram-like breakdown of the data blocks in shared memory area.
• Of particular interest is the <free> block type. Note 2253735 explains this type of block and buffer fragmentation. 16KB block initially reserved, if the table does not fill this, it is shrunk, which may leave gaps. Thus, a large amount of dictionary entries without a sufficient buffer size may exasperate buffer fragmentation.

ST10 & AL12

In ST10, sum of "Buffer size" column of all valid tables is close to (slightly higher, but not exactly equal to) the "Space with Valid Objects" metric in AL12 -> "monitor -> buffers -> table buffer -> overview"

The stats given in "AL12 -> monitor -> buffers -> table buffer -> all generic tables/all single-buffered tables" appear to match the stats of ST10, however, AL12 is more granular, listing each area of generic buffering rather than aggregating by table name (i.e. there is no such thing as a "multiple" state in "AL12 -> monitor -> buffers -> table buffer -> all generic tables/all single-buffered tables")

Additional/Misc Notes

• SAP Note 1099937 - Improved diagnosis options for buffer synchronization.
• SAP Note 2253735 - Fragmentation in the table buffer (Includes enhancements to the AL12 > "Monitor -> Buffers -> Table Buffer -> Shared Memory" screen).
• SAP Note 23877 - Performance: Buffering condition tables.
• SAP Note 1011158 - Table buffering on a SAP instance
• SAP Note 2404710 - Table buffer: Memory leak in shared memory

To do:

• Reading DDLOG
• ST10 statistics accounting (especially the unusual ones)
• Secondary Indexes