Big data companies often reach out to us for help with their SAP database performance issues. Even organizations using modern applications built for scalability and supported by three-tier client/server architectures designed to reduce database strain still experience challenges.
When a database is pushed to its limits and becomes the weakest link in the IT environment, the key is to manage data volume and growth. One of the most effective strategies is data archiving, which can be approached in three ways:
- Expand systems
- Eliminate old data by deleting it
- Eliminate old data by archiving it
Data Archiving
Unnecessarily expanding your systems can be costly, and deleting the data is frequently impractical due to the need for read access to individual data objects. Therefore, the solution is to relocate the data in such a way that it remains accessible when needed. Data archiving is less risky than haphazardly deleting your unused or infrequently accessed data, and a smarter long-term storage solution than simply expanding your systems.
Data archiving shrinks storage requirements by removing unused or infrequently accessed data from the database and archiving it to more cost-efficient media. When performed routinely, data archiving plays a significant role in controlling the growth of SAP data, resulting in a decreased total cost of ownership (TCO).
In addition to these benefits, companies frequently deploy data archiving to remedy system performance issues, mistakenly signing off on archiving projects under the assumption that improved performance is a given. However, archiving for improved system performance without a proper strategy in place will likely lead to disappointing results at best. It is not uncommon for a data archiving project that results in the significant reduction of data in the system to keep performance levels the same as they were prior to archiving. How can an SAP data archiving project enhance system performance? 
What Type of Data are you Targeting?
It is safe to say that there are options when it comes to data archiving:
1. Unused or infrequently accessed data is safe to target for archiving in coordination with SAP’s archiving programs, which will archive only business-complete data. Remember to identify your retention period for complete business data in the database, based on your company’s reporting requirements.
2. Data that is needed for a specific task is read from the database.
3. Old data should not be migrated to the application.
This ensures that data blocks containing old data will not require accessibility in daily operations and will not affect the I/O rate. Both table and index blocks are vulnerable to a mixture of new and old data. Old data shouldn’t be migrated to the application; therefore, it has no effect on system performance.
When archiving for performance, one of the most important factors to consider in selecting which data to archive first, is index layout. This is due to the fact that every system creates indexes upon data insertion, and the index type used to access data plays a role in the I/O rate.
Identifying Mixed Data in Table and Index Blocks
Table fragmentation can significantly impact the performance gains you expect from data archiving. To truly optimize system performance, it’s essential to identify data blocks that contain both old and new records. Unused data blocks do nothing but consume valuable database space.
While it may be challenging, these “mixed” data blocks can be detected. If new data is being inserted into table blocks that aren’t entirely empty, you’ve likely encountered mixed data. It’s also important to note that if your company has never performed archiving or deletion, table block fragmentation is unlikely.
Mixed data can also appear in index blocks when the index isn’t sorted chronologically. In these cases, the position of each entry—and the data block it ends up in—depends on the sort order of the indexed fields.
Selecting Initial Data to Archive
Index data can be sorted in two ways:
- Chronologically, the data is not limited to dates and time sequences. In this instance, chronological can also refer to an organizational method referencing document numbers, such as a sequential method that increases or decreases. It’s improbable to find old and new data mixed in chronological indexes since they typically are not located near each other.
- Non-chronologically sorted indexes, where sorting methods focus on factors unrelated to time, such as randomly generated identification numbers, or other types of ID’s, which use alternative logic other than, or in addition to time.
To achieve the best performance gains from your SAP data archiving initiative, identify SQL statements that result in high I/O activity within your database. If those statements rely on a non-chronological index, archiving is the most effective way to reduce I/O and improve overall system performance.
Keep in mind that improvements in system performance depend significantly on the ratio of chronologically sorted indexes to non-chronologically sorted ones. It’s also important to consider how frequently specific data is accessed. Naturally, both of these factors can vary widely from one table to another and from system to system.
As previously noted, system performance is most impacted by identifying and archiving tables with the highest I/O rates that use non-chronologically sorted indexes. Therefore, if you intend to improve SAP system performance, begin your project with the tables that show the highest I/O rate, rather than the common approach of archiving the largest system tables first.
While data archiving can play a significant role in improving SAP system performance, it is imperative to understand how to identify and target data for archiving in order to realize performance goals. If you are considering embarking on an archiving project for performance-enhancing results, it is recommended that you review your indexes, particularly those that are non-chronologically sorted in order to set attainable expectations. Many properly maintained SAP systems have unidentified quantities of data that fit company parameters for transfer to long-term storage solutions. Archiving such data can have a palpable impact on overall performance. You may be surprised by what opportunities you uncover.
Explore how U.S. Utilities was able to save over $1Million by implementing data archiving strategy in preparation for SAP RISE journey.
