SAP CRM 7.0 generates a high volume of data during transaction processing of sales, marketing, and service activities. High volumes of table growth can have a serious effect on the performance and availability of your SAP CRM system. In fact, if database growth is not properly controlled, it can lead to a blockage of all dialog work processes, system downtime, and a consequent loss of sales and revenue.

You can employ a number of data or table management strategies to effectively manage and control database growth — including data prevention, data deletion, data archiving, and data reorganization. Although these data management approaches differ conceptually, they all strive to ensure that data does not grow unnecessarily large in order to optimize disk space usage and enhance your system performance.

Data Management Strategies

Data prevention allows you to control data growth by deactivating the update of specific types of data (particularly if this data is not needed). Data prevention can be achieved using configuration settings for the SAP CRM business requirements and the business processes of an entity. Data deletion ensures the permanent removal of data that you do not need for business transaction processing and reporting from the system. Data archiving is another option, especially if you still want to have access to the data after deletion. Data archiving decongests a large volume of data by deleting it and then storing it outside of the database in a format that enables easy retrieval and analysis when it is eventually needed. Data reorganization increases system performance by freeing up space in fragmented tables.

The benefits of leveraging data management strategies based on best practices include improved system performance, reduced database table access time, reduced cost of database management and hardware resources, faster system response time, increased data availability, data reusability, reduced system downtime, and ensured compliance.

Some tables that need to be properly managed for data growth and processes that can help with this include CRM Middleware tables, pricing conditions for enterprise business transactions, outgoing Remote Function Call (RFC) tables, external list management, the index table for CRM business transactions, and table CRMD_SCHEDLIN for schedule lines of CRM business transaction items.

CRM Middleware Tables

In SAP CRM, Business Document (BDoc) messages serve as a repository for data that makes up a process, such as a transaction or application message. The Middleware Trace allows you to store the information that is written into CRM Middleware during processing in the trace tables.

It is common to experience a high data growth rate on tables for BDocs in CRM Middleware, which can lead to system disk space and performance bottlenecks. The performance of the system is affected during BDoc processing because it slows down read and write access to the tables used for this operation. Trace tables can also grow to the extent that they negatively affect the SAP CRM system. The affected tables include tables for managing BDoc messages (tables SMW3_BDOC, SMW3_BDOC1, SMW3_BDOC2, SMW3_BDOC4, SMW3_BDOC5, SMW3_BDOC6, SMW3_BDOC7, and SMW3_BDOCQ) and CRM Middleware trace table SMWT_TRC. You should delete the entries in these tables at regular intervals (e.g., every one to seven days) to prevent their growth from affecting system performance.

The trace functionality of CRM Middleware allows you to activate tracing, deactivate tracing, and set a trace level. To set the trace level, use transaction SMWTAD or follow menu path Architecture and Technology > Middleware > Monitoring > Message Flow > Set up Middleware Trace. The trace level options include:

• Level 0 — Error: Only serious errors such as full table space should be reported
  
  
• Level 1 — Warning: Only situations that can produce an error are reported, such as when a high watermark is reached
  
  
• Level 2 — Service Flow: All services that are processed in the CRM Middleware are reported
  
  
• Level 3 — Detail Level 1: Additional information is reported about executed programs
  
  
• Level 4 — Detail Level 2: Information about specific program variables

High volume of data growth of the affected tables listed above can be attributed to any of the following causes:

• The trace level is higher than 1
  
  
• The program SMO6_REORG2 used for table reorganization is not executed regularly
  
  
• The background job MW_REORG does not run

Trace environments allow you to run a trace with different trace levels. Available trace environments include standard, delta data synchronization, message flow, replication, generation, initial load, test, and internal trace. To prevent performance issues that result from the high growth rate of SAP CRM Middleware trace tables, it is a best practice to activate traces with a default value of level 1 for all trace areas except for the generation environments. For this environment, a default value of trace level 4 is recommended and you should keep this level as is.

You should also check that the program SMO6_REORG2 is defined and scheduled as a job that is executed regularly via transaction SM37. Also, check that the corresponding background job MW_REORG executes successfully without error.

In the event that no corresponding background job is scheduled to run regularly at defined intervals, schedule program SMO6_REORG2 to run regularly in all active clients because the program is client-specific. This can be performed via transaction SM36. When you define parameters for creating and scheduling the background job MW_REORG, you can use the variant SAP&_MW_REORG to ensure that trace and log data more than seven days old is deleted automatically. To modify the period within which trace and log data is deleted, create a new variant in transaction SE38 for program SMO6_REORG2.

Program SMO6_REORG2 can also be used to reorganize BDoc messages that are reprocessed by the system after encountering errors, as well as to reorganize some CRM Middleware statistical data, which includes message flow statistics and the statistics on data exchange between mobile clients and the SAP CRM server.

Pricing Conditions for an Enterprise Business Transaction

Table PRCD_COND is a document condition table used to store pricing conditions of SAP CRM enterprise business transactions. These tables can grow very large within a short time frame, especially when you have hundreds of pricing conditions per item. Other tables that might be affected by the growth of table PRCD_COND are PRCD_HEAD (header) and PRCD_ITEM (item). By adopting appropriate configuration settings for pricing procedures, you can greatly reduce the number of entries written to tables PRCD_COND, PRCD_HEAD, and PRCD_ITEM. A simple pricing procedure with only the required condition types should be configured in the system as much as possible. This can be done under customization by following menu path Customer Relationship Management > Basic Functions > Pricing > Define Settings for Pricing > Create Pricing Procedures.

Table PRCD_COND is created as a transparent table in SAP CRM. When large documents are created, the table can grow quickly. This can lead to a performance bottleneck because many relational database systems have thresholds for the physical size of database tables. Therefore, it is a best practice to convert table PRCD_COND to a cluster table to take advantage of the compression logic benefit. A cluster allows the memory consumption of the table to be reduced by a factor of five to 10. This increases performance because the number and frequency of accesses to the hard disk is drastically reduced.

At this juncture, it is important to note that the conversion of table PRCD_COND to clusters is a modification and as such cannot be performed in the SAP CRM production system of the SAP system landscape. For more information on this, you can refer to SAP Note 580455. Furthermore, the process is time consuming and can even take several days to complete depending on the size of table PRCD_COND. It is a best practice to carry out this conversion before the table gets too large. During the conversion process of table PRCD_COND to a cluster, the table is locked, no processes can have access to the table, and the processes that use pricing conditions are stopped. The steps below can guide you in converting table PRCD_COND to clusters:

1. Check the size of the table cluster PRCD_CLUST in the development system to ensure that the table cluster PRCD_CLUST is large enough to accommodate all the entries in table PRCD_COND.
   
   
2. Transport the blank table cluster definition into the target system. This is very important because data can also be lost if the table definition is not available during the conversion process.
   
   
3. Check the size category of the table cluster in the target system.
   
   
4. Trigger the conversion in the development system.
   
   
5. Transport the definition of table PRCD_COND to the target system as a cluster table.

Outgoing RFC Tables

RFC tables can also acquire a large number of entries. Examples of RFCs are transactional RFC (tRFC) and queued RFC (qRFC). tRFC is an asynchronous communication technology that executes the called function module in the RFC server only once. tRFC is useful in situations in which functions are executed as a logical unit of work (LUW). Queued RFC is an extension of transactional RFC and it ensures that multiple LUWs are processed according to the order defined by the application. The table ARFCSDATA acts as a repository for tRFCs and qRFCs called in the sending system. The ARFCSSTATE stores the status of the sent RFCs.

Under normal circumstances, when the RFC has been successfully executed in the target system, the table entries relating to the call are deleted. However, it is possible for an exception or error to occur during the call. In such a situation, a reset of all database operations started by the previous call is carried out and a corresponding error message is logged in table ARFCSSTATE. The implication therefore is that outbound tRFC and qRFC tables, especially ARFCSDATA and ARFCSSTATE, will have large number of entries which can lead to poor performance of the SAP CRM system.

As stated earlier, entries in these tables are automatically deleted after the RFCs have been executed successfully, thereby controlling the growth of the tables. However, data is not deleted when RFCs cannot be processed due to an error and or during asynchronous processing.

You can use transaction SE16 to check the number of entries in table ARFCSSTATE. Table ARFCSSTATE with empty entries for the ARFCRETURN field shows tRFCs that have not been processed or that have the status of “error.” Entries in the ARFCSSTATE table that do not have an empty ARFCRETURN field indicate qRFCs that have not been processed or that have the status “error.” Transactions SM58 and SMQ1 allow you to analyze, process, and delete unsuccessful tRFC and qRFC calls respectively. However, before error entries are deleted, it is important that you review and troubleshoot the error with the system administrator to avoid a reoccurrence. Furthermore, it is a best practice to reorganize tables ARFCSSTATE and ARFCSDATA after successfully deleting the entries.

NOSEND is one of the statuses of outbound qRFCs that is determined by the way LUWs are processed. By virtue of this status, LUWs of this queue are never sent, but they are received by the retrieval application. SAP uses these queues for internal purposes. When an LUW is read by an application, the status does not change. It is only deleted from the queue when the application confirms the receipt. It is a best practice not to change the status for any reason and that you activate the queue via transaction SMQ1.

Furthermore, in an SAP ERP-SAP CRM environment, it is possible for the system to run OPEN-FI event 00501015 (function module OUTBOUND_CALL_00501015) unintentionally even if delivery-based SAP CRM billing is not needed. This can also cause the system to deteriorate in performance as a result of the update (with irrelevant entries) of the ARFCRDATA table in the SAP CRM system. This unnecessary table growth can be prevented by deactivating OPEN-FI event 00501015 in the SAP ERP system. Also, you can use transaction code SMQ1 to delete unprocessed entries for outbound queue R3AD_LEDELIVx (x=0-9).

External List Management

External list management is a functionality of SAP CRM that allows you to effectively manage large volumes of uploaded data. Table CRMD_MKTLIST_C serves as the repository for uploaded files. After data mapping, the information in the file is stored in a number of related tables, including CRMD_MKTLIST_ACT, CRMD_MKTLIST_ADR, CRMD_MKTLIST_ATR, CRMD_MKTLIST_BCI, and CRMD_MKTLIST_CEN, among others.

Table CRMD_MKTLIST_E is used to store error messages for each data entry while the link between data entry of a list, the business partner, and activity is stored in table CRMD_MKTLIST_I. Furthermore, tables BUT000, BUT020, BUT021, BUT051, ADR2, ADR3, ADR6, and ADRC are susceptible to high data growth especially when business partners are created within external list management. Tables CRMD_ORDERADM_H, CRMD_ACTIVITY_H, CRMD_LEAD_H and CRMD_PARTNER are vulnerable to growth when business transactions are created within external list management.

These database tables can quickly grow very large depending on a number of factors, which include:

• The number of lists uploaded
  
  
• The size of fields uploaded as a list
  
  
• The time frame before deletion of the list
  
  
• Whether the list is deleted or not

The database monitor in the Computing Center Management System (CCMS) can be used to check the total size of the tables and the available free space. To guard against system performance bottlenecks that can result from low disk space, it is a best practice to reduce the content and size of the aforementioned tables via data deletion as follows:

• Delete the temporary data of a list or delete the list completely for tables CRMD_MKTLIST_ACT, CRMD_MKTLIST_ADR, CRMD_MKTLIST_ATR, CRMD_MKTLIST_BCI, CRMD_MKTLIST_C, and CRMD_MKTLIST_CEN. Others are CRMD_MKTLIST_CLR, CRMD_MKTLIST_LEA, CRMD_MKTLIST_ORG, CRMD_MKTLIST_PER, and CRMD_MKTLIST_SUV.
  
  
• Delete the list of the following tables CRMD_MKTLIST_I, and CRMD_MKTLIST_E
  
  
• Delete the business partners for BUT000, BUT020, BUT021, BUT051, ADR2, ADR3, ADR6, and ADRC

Index for CRM Business Transactions

CRMD_ORDER_INDEX is a centralized index table that allows you to search for SAP CRM business transactions. This table is vulnerable to high table growth because it consists of an entry for every item-partner-combination or header-partner-combination of a business transaction. Secondary indexes can be created as additional indexes to hasten and optimize search functions for business transactions and to avoid slow search operations when business transactions are searched in the SAP CRM system. A best practice approach to keep the size of secondary indexes as small as possible is to ensure that only fields that are needed are included as search criteria when secondary indexes are created. When SAP CRM business transactions are archived, entries in the central index table CRMD_ORDER_INDEX are also archived and deleted, thereby freeing up space and consequently enhancing system performance.

Schedule Lines of CRM Business Transaction Items

Table CRMD_SCHEDLIN is used to store the schedule lines of SAP CRM business transactions. The data growth rate of CRMD_SCHEDLIN differs from similar tables because of the way schedule lines are designed and structured in various SAP ERP systems. While the schedule line structure in SAP CRM is designed to store one general appointment and quantity field, the schedule line structure in SAP ERP is designed for large numbers of different appointments and quantity fields. As a result, the schedule line structure in an SAP CRM system is usually thinner than schedule line structures in an SAP ERP system. Dedicated quantities and appointments are displayed via the schedule line type and only one schedule line is produced per quantity type. Appointments that arise from scheduling are kept in the SAP CRM order as individual schedule lines. To effectively manage the growth of CRMD_SCHEDLIN and prevent performance bottlenecks, you should perform archiving. When SAP CRM business transactions are archived, the contents of table CRMD_SCHEDLIN are archived and deleted.

Kehinde Eseyin

Kehinde Eseyin is a security architect. He holds a bachelor’s degree in computer science. He has about 12 years of IT security, governance framework, IS risk, and compliance experience gained by working in numerous global organizations. Over the years, he has demonstrated competencies in security design, information assurance, cyber security, data privacy, threat and vulnerability management, penetration testing, business architecture, project management, IT audit, IS controls framework, and identity and access management.
 

You may contact the author at eseyinok@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.