Buffering Database Tables in SAP

This SAP article will guide you all about “Buffering Database Tables in SAP”.

In a SAP system, there are many layers of data buffering – IO subsystem, database system, application and program. All buffering is to make data available in memory so request for the same data can be fulfilled via memory. This helps data retrieval performance and underlying business transaction or program performance. In this post, I would talk about SAP table buffering at SAP application level.

1 SAP table buffering introduction

1.1 SAP buffering concept

SAP table buffering is to store data of a table in an application server memory. After data is in memory and no data change, any request for data in the buffered table by any program or business application executed in the server would be fulfilled from the memory of local application server without involvement of database server. ABAP trace in my SAP environment shows that getting data from buffer can be up to about several hundred times faster than getting data from database server.

Figure 1 SAP buffering Overview

SAP buffering pros and cons

Figure 3 SAP Table buffering pros and cons

1.2 SAP buffering types

SAP allows three type of buffering choices – single record buffering, generic records buffering and full table buffering. All those types of buffering are referring to size of “data entry” which would be managed by the SAP buffering mechanism in buffer loading, swap, data change management.

Pros	Further clarification	Cons	Further clarification
Speed up data retrieval process	This can speed up corresponding related program and transaction.	The same data would store on memory of all application server – redundancy.	Memory demand would be higher
Reduce database load	Database server is a Central Instance	Change synchronization delay.	Data retrieved by program can be out of date
Reduce network traffic		Buffer bypassing can happen.	Buffer is not utilized
Reduce process residency time in a SAP work process	Reducing possible contention on work process resources

Figure 4 SAP table buffering types

In following section, I would talk about how to select right buffering type for a table.

1.3 SAP buffer for table buffering

The application server memory used for SAP buffering is called SAP buffer. There are two types of SAP buffer used in table buffering – Generic Key buffer and single record buffer. Generic buffered table and fully buffered table would share the same SAP buffer – Generic Area Buffer. Size of SAP buffer is configured by two parameters: one is to specify size of buffer,the other is to specify number of the directory entries in the table buffering.

	Buffer load during read when data is not in buffer	Buffer change/invalidation	Buffer swap
Single record buffered	Load one record into application buffer at one read. A record would not be in application buffer if it is never read since start of the instance	One record at one time.	Swap would be done at record level.
Generic area buffered	Load all records which share the same partial primary keys. No read means not in buffer	All records which share the same partial primary key of the changed records are invalidated at the same time.	All records shared the same partial keys are either swapped or in memory as a whole.
Full table buffered	Full table is loaded into buffer at 1st read	Whole table are invalidated upon any change on the table.	Whole table would be swapped.

Figure 5 SAP tabe buffer parameters

1.4 SAP buffer synchronization

After a SAP table is buffered, the buffered data can be changed, this data changes need to be reflected in generic area buffer or single record buffer of all SAP servers in the system.

Any change by a sap program/user is always initiated from an application server. On the server where change is initiated, the table/data is marked “changed”/”invalid” (Actual state is “pending” in ST10) as when buffered data is changed (Whether change is committed or not makes no difference based on my testing). When buffer table is marked as “changed”/”invalid” ( “pending” status in ST10 ) in a server, the data request from the server would be fulfilled by database until certain number of read operations on the table occurs from the application server. After that number, buffer would be refreshed and marked “valid”. This mechanism is to reduce load from pre-mature buffer reloading or buffered being refreshed too often un-necessary. For example, a program can update a table hundreds or thousands of time in a seconds, if the system is going to reload the data into buffer for each update, this could bring down the systems.

Now what are footsteps used by other servers for buffer synchronization? When a buffered table is changed, a change entry is logged into SAP table DDLOG. SAP buffer has a mechanism to read DDLOG table “PERIODICALLY” to know what buffered table is changed and from which server, based on that information, it would mark the buffered table as “invalid”/”changed” in all remaining SAP application servers. So there is a delay between data is changed in one server and corresponding data in memory is marked as “invalid”/”changed” in remaining instances of the system. Extent of delay is determined by parameter RDISP/BufRefTime, timing of a data change, volume of buffer table changes and instance/server performance. After a table is in “pending” status, data needed by a program from a server would be from database table. The table would be loaded into buffer again after a certain number of read operations on the server. Please click the post how ST10 buffer state is change for details. So remember when table is in “valid” status, the data would be read from buffer for the server even it is already changed in another server!

Apparently, the delay of buffer synchronization only matters for the SAP system with more than one application server/instance.

What are SAP buffer synchronization parameters? They are:

1. RDISP/BufRefMode – when there is more than SAP application server/instance, this parameter should have value of “Sendon, ExeAuto” – this enables buffer synchronization. Value of “sendoff, exeauto” means no buffer synchronization – applicable for sap system with only one SAP application server/instance.


2. Rdisp/bufRefTime – this specify interval between two adjacent buffer synchronization so buffer can be synchronized periodically. It has a default setting “60″ seconds.

You can use SAP program RSDBBUFF

to know when latest buffer synchronization is done for an instance/server and how far away the server/instance is from next buffer synchronization. However if you would like to know what table buffered entries are changed in the past, SAP program RSTUNE60 can help.

1.5 how to activate SAP table buffering

You might be wondering how to enable buffering for a selected table? Table buffering is a technical setting of a SAP table. SAP transaction SE13 allows you to display or change technical settings of SAP table directly. On the technical setting screen, you just need to switch on the buffering and check the right buffering type as showed in the following screen. You know you need to save the changes – after that, you are done – your table is buffered as per your instruction.

Figure 6 SAP table buffering setting

When you select buffering type – Generic Area buffered, you need to specify how many key fields should be used according to your buffering design. Of course, number if key fields specified should be less than number of fields which the primary index has… otherwise, it is a single records buff.

You can use SAP SE11 transaction to display and change technical settings using menu option of SE11 screen( 2nd screen) as well

1.6 SAP buffering monitoring

SAP transaction ST02 can be used to monitor SAP buffer and memory at instance level to see whether there is enough buffer space. SAP transaction ST10 can be used to monitor access status of both buffered SAP tables and not-buffered SAP tables.

1.7 Table buffering makes a performance difference – an example

Following is result of a ST12 ABAP trace on a business transaction execution. TABLE USR05, TVTA and /VSO/M-VSO_SETUP are buffered configuration tables. VBAK and VBUK are transaction table which has no buffering. Select statements are using full primary keys of those tables.

 

	Buffer size parameter	Further info	Number of entry parameters	Further info
Generic area buffer	zcsa/table_buffer_area	Buffer Memory size	zcsa/db_max_buftab	Maximum number of buffer that can be buffered
Single record buffer	rtbb/buffer_length	Size of single record table buffer	rtbb/max_tables	Max. number of buffered tables

 

Above table shows a significant SQL runtime difference between reading buffered table and non-buffered table when primary keys are used.

2 How do we determine whether a SAP table should be buffered or not?

2.1 What should we consider to buffer a table?

Bye now, you know how SAP table buffering is working now and significant runtime improvement for table access through buffering. So you might wonder how we can reach a decision on whether a SAP table should be buffered or not.

To reach decision, you need to consider following factors/guidelines

Figure 7 SAP table buffering – influencing factors

Following are further prints to help you to understand above points better:

1. Table size – In general we should not buffer a big table since memory is normally limited. We should be extra diligent when you buffer a table which is bigger than 1% of defined buffer space.


2. Data change habit – This refers to when, how often, volume of change related to table. In general, we should not buffer a table which is changed frequently. A table with a change/read ratio > 0.5 should not be buffered normally.


3. Data access habit – This refers to access frequency, access time pattern, access volume pattern (associated selection criteria) and load generated by the SQL. we should not buffer a table which is seldom accessed/queried by SAP programs/transactions. How table is access or access pattern is important as well. Buffer a table with higher database load can be good for database performance. Common wisdom is what is good to database would be good to SAP application. Your option to buy into thisJ. Certain SQL SELECT statement would bypass buffer. If majority of SQL statements being used for a table would bypass buffer, then buffering the table makes no sense.


4. Buffer synchronization delay impact – We should not buffer a table when buffer synchronization delay results in obsoleted data being read by other SAP programs/transactions. This is not a concern for a SAP system with only one application server. If business insert/update data in a quiet window (Data change habit), then this is not a concerns any more.


5. Business performance requirement – If buffering a table can improve a business process performance significantly, then we should consider buffering seriously.


6. Buffer and memory status – If buffer space is not enough and cannot be increased due to physical memory limitation, buffering new table might create more displacements or swaps. If there are heavy swaps in table buffer area, you would make the things worse to buffer additional table, I think you can agree with me on this. It is better to maintain 10% of free buffer space – do not forgot to check free directory as well.


7. Database server and application server load status – this would help us from load distribution point view – where is the best place to keep the load based on specific case. The general wisdom is to remove load from database server to application servers. but if your application server is already overloaded and database server has a lot of idle power, it would not makes sense to buffer a table before application server load can be mitigated. The normal recommendation is that SAP server/instance should maintain at least 20% free CPU power for peak processing – this is especially important to DB server.

Standard SAP table has a predefined setting on table buffering attributes. You should not change the setting for a standard SAP table in general. A SAP tables which stores business transaction data cannot buffered. SAP tables for master data are not buffered. SAP tables for configuration table are normally buffered. However, buffering setting on standard SAP setting can be changed suited to your specific business needs.

Consultants from SAP recommend that table over 5 MB should not buffered. But if this table is frequent accessed, very expensive, very stable, there are enough free buffer space and buffering the table can help program to meet business performance goal, then buffering the table might be a wise choice even it is 10 MB in size. If a table is small and seldom changed, but there is a high chance that delay of synchronization can create data inconsistency and impact accuracy of business result when there is a change, this table cannot be buffered.

Table buffering type is a fine setting mainly dependent on table size, table change habit and access habit. The setting would impact size of data which would be marked “changed”/”Invalid”, If each time, only one record is changed:

• Fully buffered -> all records of the table would be invalidated and reloaded to buffer by the system. One change would result in loading of whole table.


• Generic buffered ->all records which share the same common keys fields would be invalidated and reloaded to buffer by the system. Access other records in the affected group needs to get from database after they are marked “changed” until they are reloaded. Risk of getting obsoleted data if data is accessed after change and before synchronization.


• Single record buffered -> only the change record is invalidated. Other records would be valid in buffer. But single record buffered needs more round trips between database and application server to buffer a table.

Under fully buffered and generic buffered, I would like to highlight that change one record would cause more than one record being reloaded to buffer from database. When table size is big, volume of change and access is high in the same time window, this can create serious system performance issue due to frequent buffer reloading- I have seen this in my work.

If many records are changed each time, “full” or “generic” table buffering might be working better to avoid too many round trips between application server and database server related to buffer synchronization. Number of key fields should be decided based on fields reference in SQL where-clause.

So what tables can be buffered or cannot be buffered – you need to review all those factors and understanding what is your key driving factors based on your business need. There is no hard line here. Bottom line is that you need to know the context surrounding the table buffering – the system, the business application and the table.

2.2 How do we know whether a SAP table should be buffered in SAP development/project?

Here, we only consider “new” local table developed in a SAP development. Designing a new local table involves many areas – like table structure, primary key and index, data retention, data management etc. Among them, it is buffering attribute. If you do not specify buffering attribute explicitly, the table would not be buffered by SAP. Many developers overlook technical settings of SAP table. In the end, it creates performance issue and more effort to correct this later.

Transactional table cannot be buffered normally. We only need to consider local table like mapping, reference and set up tables by collecting needed information related to previous stated table buffering guidelines:

1. Who would populate data? Then when/how frequent is the data inserted to the table? How many entries would be inserted to the table? Who can be a person, a SAP job, a SAP program and an interface.


2. Who would use the data? Then when/how frequent is the data accessed? What selection criteria would be used to access data? How many records would be accessed by each user.


3. Who would modify the data? Then then when/how frequent is the data modified? What are selection criteria for data, how many records would be updated at one time window.


4. How long would data stay in the table? This is data retention period.


5. Business performance requirement. If program performance is critical, spends notable time on this table and there is no way to optimize the access, then buffering might be important.


6. Production server status – buffer/memory status and Database server/application capacity status.

Based on table definition, you know table record size already, then based on above information you collected, you can get a good indication about table size. With change frequency and access frequency, you can get a good indication about change ratio – # of changes/# of reads.

With above information, you can reach decision on buffering. For example:

1. Small table and seldom changed can be buffered if servers status(buffer and CPU) has no issue.


2. Table is not small but it is access a lot – table in general should be buffered if change habit is not a concern.


3. Table is small but change habit is a concern – then this table cannot be buffered generally.


4. Table cannot be buffered if change habit would cause data inconsistency which business cannot afford. If data is changed well before they are being used, then buffer synchronization delay is not concern anymore.

Changes here include data population, data modification and data deletion. If data is changed well before they are being used, then buffer synchronization delay is not a concern anymore.

In SAP project/development, you might need to buffer an existing local table as well, this is mentioned in following section.

2.3 How do we know whether a SAP table should be buffered in SAP production/support?

In SAP development, most information for table buffering is from business requestor who requests the development. In production box, we have different SAP tools to record table related information. We can get a lot of information related to table size, access habit, change habit and system status information.

The best starting point to analyze table buffering is to use SAP transaction ST10 – table call statistics. ST10 analysis can provide a list of table which should be reviewed here further. Click here to know how to run SAP ST10 to do sap table buffering analysis.

If SAP ST10 states that a table can be buffered, then you need to do more investigation to confirm whether it can be buffered.

• Get table size – normally refer to disc space but can be refer to number of entries. SAP transaction DB02 can tell you disc space and history. SAP transaction DB20/DB20ORA and SE15 can show you number of entry in a table. DB20/DB20ora report number of table entries from oracle statistics table while SAP SE16 transaction tell you accurate number of entry in a table by accessing each record of the table.


• Understand how the table is accessed by analyzing table SQL statements Cache via SAP transaction ST04. SQL cache can tell how many seconds the SQL has spent so far, type of SQL operation and total of number of operation. For select/update statement, it can show you the where-clause which you can get clear picture on select-conditions.


• Run ST02 and ST06 – check table buffer status to see whether there is any contention here. Using ST06 to check CPU status on database server and application server as well as main memory to see whether there is potential concern related to buffering.


• You need to talk with business owners to understand remaining details of access habit and change habit as well as possible impact from delay of buffer synchronization.


• You also need to understand the performance requirement – based on current status and time spent on that table, you can know what runtime improvement you would get by buffering the table.

With above information, you can make an informed decision on table buffering. Following the similar process, you can confirm whether you should stop buffering on a table or change buffering type of the tables. Again, ST10 information like number of invalidation and number of database records have several contributors – like se16 transaction would bypass buffer and increased number of rows. It is not safe to make a decision to remove buffering on a table just based on ST10 number of invalidations and number of rows impacted.

3. Further clarification

SAP table buffering does not support secondary index. If a buffered table is big and reading the table is not using primary index, then read a record from such table is consuming more application server CPU and benefit of runtime improvement might not be that significant than read it from database using secondary index. You might need to test to see the improvement before buffer and after buffer to make sure that runtime improvement worth the consumption of CPU and memory in application server side.

SAP application buffering also include number range buffering. Number range buffering is important for application performance. However this is not covered here. I might cover this in future posting.

There are other types of SAP buffer like program buffer etc., those are important to SAP application performance as well. They are configured by SAP basis team. I have covered them in my post – how to run SAP ST02 and use ST02 to do performance analysis on SAP memory and buffer.

We should not confuse SAP buffering with program level data buffering. Data in SAP buffer is available to all SAP programs while program level buffering is only available to a specific program while the program is running normally.

Now, you must have got clear idea about “Buffering Database Tables in SAP”.

Buffering Database Tables in SAP

Table name	Buffer status	Total # of read	Total time(micro seconds)	Time per read(rounded)
TVTA	buffered	58	826	14
USR05	buffered	1,474	26,201	17
VBAK	No	2,342	8,260,148	3,526
VBUK	NO	2,342	6,769,828	2,890
/VSO/M_VSO_SETUP	Buffered	2,342	23,370	10