Cutting SAP Down To (a Manageable) Size
The DBA wouldn’t touch it as there are 83,000 tables in SAP. He was busy with other applications, and he had no time in dealing with a big package that was a mess. During development, there had been no DBA assigned to the project, and now it was time for some database attention to get it to perform in production. My role started the week prior to production and we needed to tune and understand how the DB2 side of SAP would be managed in production.
Another issue with DB2 relates to a competing DBMS that has raised its prices and bought other application packages that compete with SAP. In response, many large organizations have switched to DB2 LUW. While these organizations have decades of DBMS experience, they are somewhat light on the philosophies and techniques to manage DB2.
This article describes our experiences in SAP performance tuning, the challenges we faced, and the tools and techniques we used to get DB2 performance to an acceptable level. It is very safe to say that your challenges will be different, but also hopefully similar enough that this article provides some value.
SAP as an Application Package
SAP® is a huge ERP application with an accompanying BI/BW component. The ERP side handles most business functions, from sales, accounting, and logistics to HR and payroll. It is up to your company to decide which modules to implement. SAP is large and flexible enough to say that every implementation is different.
Everything in IT seems to get tied to SAP in some way. Customization is required, and one or more implementation partner (you can call them consulting firms) will probably have had with your system to match business requirements and desires. Infinite customization is possible within SAP’s framework.
SAP has its own language called ABAP which stands for Advanced Business Application Programming (originally Allgemeiner Berichts-Aufbereitungs-Prozessor, German for "general report creation processor“). ABAP generates SQL and has its own comprehensive data dictionary. Table names are mostly four characters in length and column names are usually five characters. The acronyms are usually German abbreviations, for example, MANDT is the client ID.
Another key role created by SAP is the of the BASIS Administrator. This role is responsible for the daily care and feeding of the SAP environment. Everything can be done by the BASIS Administrator, from application to environment management.
SAP provides its own GUI and tools for just about every aspect of SAP environment from development, performance management, change management including the transport process, database maintenance and to even DB2 upgrades. The GUI is called NetWeaver, and it may be implemented under the SAP Mobile Platform (SMP) and the Solution Manager. The DBA tools are called the DBACockpit, and these are available as a transaction within NetWeaver.
When I first started with SAP, I didn’t know about the GUI. I was just given the IP address along with the instance owner userid and password. I approached SAP as I would any other application: use the DBA tools available, which included the Control Center, later Data Studio, and other free and vendor performance tools installed at the client. Our implementation partner was leaving, and the in-house staff including the Basis Administrator were just taking over.
In many respects, the DBMS is isolated from the SAP environment. Many BASIS administrators live a good many years without any DBMS knowledge. If you were hiring a DBA with only SAP experience, they may or may not have real DB2 experience. For example, Oracle DBAs when they move to DB2, need to understand DB2’s reorg process.
The Size of SAP
The following table shows the number of tables of various sizes in SAP. Of the over 83,000 tables delivered, under 24,000 are used. Definitions and customizations are stored in various tables.
The largest tables often vary between implementations. At one of my main customers, there are 38 tables where compression is being used. After three years in production, the overall size of the SAP database is 1.4 TB.
Here is another important tip on compression: SAP representatives can sell DB2 as part of the SAP sale. The advantage of buying DB2 from SAP is the Storage Optimization feature (aka data compression) is included in the price. This saves substantially on purchasing this important feature.
The result shown in the sizing table above shows that tuning SAP is about the same as many large applications. Look at the activity, and tune the biggest, busiest tables first.
The DBA Role
So, do you need a DBA for SAP? The answer is yes (of course!) The DBA Cockpit provides help for performance, space, backup and recovery, configuration and maintenance activities. The diagnostic tools include explain and the design advisor.
Every now and then, I come across project managers and other people who feel that an application should only be modified by the vendor. With SAP, I point out the functionality in the DBA Cockpit to answer their concerns.
DBAs still have to identify and tune poorly performing SQL, adding indexes as necessary. Almost all SAP delivered queries perform well without modification, so the majority of my tuning efforts are spent on the custom code written in-house.
DB and DBM configuration parameters and performance can also be monitored via the DBA Cockpit. There is both a central planning and DBA calendar for regularly scheduling maintenance or running single utilities. Of course, SAP is using REORGCHK to determine when to REORG, and this is not as useful as looking at table activity to see the actual number of overflow accesses. Within the DBA Cockpit, table activity shows the number of overflow accesses performed.
We recently identified two indexes that were needed to improve the performance of a custom query on two SAP tables. Having identified the query, the index advisor was called. The indexes highlighted in red do not exist and are recommended.
One very useful feature of the DBA Cockpit is the inclusion of comments after the statement on the screen. The location field points to the ABAP module and the line number of the statement. This is extremely valuable for having an effective dialog with development when a problem statement is found.
The monitoring within SAP is snapshot based, so it has some limitations. Additional tools supplement the DBA Cockpit with time based reporting, better analytics and further diagnostics.
When everything is running smoothly, DBA might not be too busy. The automation through the SAP and other toolsets makes daily monitoring easy. Daily routine falls to the Basis Administrator.
Working with the Vendor
The SAP environment is completely visible to SAP support, and they are very helpful in every aspect of the product. As I didn’t have access to the DBA Cockpit at the beginning, the following screen shot shows two indexes that I added outside of SAP. Tables that start with “Z” are user tables, so I was tuning customer modifications. My changes have “MH” as a suffix.
SAP controls everything in its environment, to the point of knowing when you have made index changes outside of the SAP environment. If you added indexes outside of SAP, the Missing Tables and Indexes report will tell you and SAP about it. One of the first (and common) responses from SAP is to get back into compliance by adding back or removing these “renegade” indexes.
The proper way to add indexes is to add them in the development environment, and to use the SAP transport process to move them to other testing and production environments. This allows the changes you make to be in compliance. Most companies have multiple development and test environments within a DB2 instance. Transport can move changes across environments and instances.
SAP has an online community (SAP Community Network, or SCN) for its customers, and it is well worth checking out. The SCN contains many platform specific Tech Notes, and many of these are performance related. This information is quite valuable and should be reviewed regularly.
However, sometimes Tech Notes misinterpret how DB2 is used by customers. For example, I was told by SAP development that all data access in the ERP component is expected to be single row. That isn't true in many customers, for example, we have an in-house modification that returns an average of 300 rows, and we have added indexes for this statement as we did in my earlier example. While SCN is very helpful, I find a considered outside opinion often quite valuable, such as supporting our SAP implementation with ISV performance tools.
Further Conversations with SAP
As we did our tuning, the Basis Administrator asked SAP if we could remove “standard” indexes. A better-worded question might have been if we could remove SAP-delivered secondary indexes on search fields we have not implemented? The full key cardinality of these indexes is very low, with one being a common value.
SAP answered no, that it may cause problems during an upgrade. Thinking about this, how often do you upgrade, about once per year? Should you maintain an index for a once per year event? Of course, we do remove indexes using the SAP transport process.
Another question related to SAP’s use of the MANDT (client number) column first in almost every index. It usually has a cardinality of one in production, maybe higher in test if there are different test sets. One reason might have been early in SAP’s life, possibly multiple clients were supported in a single SAP system, but this is not normal today.
The official answer was that it is working as designed. Further discussion would involve a consulting services agreement with SAP.
To Self-tune or Not?
SAP recommends self-tuning, and they make recommendations to senior management about it. Regardless of what you choose to do, I find that self-tuning is far less important when a single database is in its own instance on a separate LPAR. Why would I ever want self-tuning memory to reduce sizes?
We did increase several values (the usual suspects), and we continue to adjust things upward from time to time. After all, the production ERP machine has 100 GB of RAM.
Bufferpools
SAP uses a very large, single 16K pool for everything. The extent size is 2. We just increased the buffer pool size to 1M pages or 16GB, from 12GB. Be sure to increase DBHEAP at the same time.
I learned to be conservative when increasing memory sizes, as large increases may require deactivate/activate of DB2. When we were at 300,000:
On the other hand, our increase from 750K to 1M buffers went smoothly:
We defined a block-based area, and measured good results. Without a block-based area, asynchronous I/O (table scans) can push synchronous pages (pages read via an index) out of the buffer pool, thereby increasing physical I/O and lowering buffer pool hit ratios. A block-based area allows asynchronous I/O to happen in a separate small area. The block size matches the extent size of two. Vector I/O is asynchronous I/O that occurs in the page area, and we aim for this to be zero.
SAP does relatively little scanning to other systems. In our system, all asynch I/O is in the block area of 5,000 pages:
SAP Delivered Indexes
As with many ERP packages, primary keys and index keys are hierarchical. MANDT (a German abbreviation for client ID) is first in most indexes and queries with a cardinality of 1 in production. Different MANDT values may be used in test and development for various reasons. Some helpful DB2 features are not used consistently, such as clustering, MDC and include columns.
Within the ABAP interface, the primary index is not shown. The names of PK indexes have a ~0 suffix. On the whole, SAP is not really heavily indexed. The average number of indexes is just over 1 per table (for those containing data). The maximum is about 7 indexes on a few tables.
Early on in production, we had a discussion between the DBAs and our developers regarding performance and indexing. They had heard a popular DB2 speaker (we know him as Scott Hayes) talk about “elephants and mosquitoes”, where a small, frequently run query might be a bigger problem than a big, infrequently run one. Beyond this adage, their knowledge fell off.
I found a table where an index was desperately needed. The implementation partner said: “Why? There are already three indexes on that table. ” Confused, I shared my results of “describe indexes for table <>” showing only one index. Somehow, the expected SAP indexes did not make it to our production environment, and this was supported by the SAP Missing Tables and Indexes Report.
The SAP documentation is quite stern:
“Missing primary indexes can lead to inconsistent data in the System. A missing index of any kind can lead to severe performance problems. The above indexes have been defined in the SAP Data Dictionary, but do not exist on the database.
Recommendation: Recreate missing primary indexes immediately on your database. If the above secondary indexes have not been dropped intentionally (for example in cooperation with the SAP Hotline), recreate them as soon as possible.”
Dynamic Miscellaneous Tuning Notes
The following SQL statement gets SQL from the dynamic statement cache along with some I/O activity. Note that the SAP comments are available and point to the module.
db2 "SELECT a.AGENT_ID, a.ROWS_READ, a.ROWS_WRITTEN, ROWS_SELECTED, substr(stmt_text,1,255) text FROM sysibmadm.snapappl a join sysibmadm.snapstmt s on a.agent_id = s.agent_id order by 4 desc"
SAP temporary tables do not appear in the ABAP code but they can be important to tune. Examples are A807 and A914.
These tables can have a long key, with few other columns. In this case, the key supports the ORDER BY. The table definition only contained two other columns. The following index was added to make the query index only:
Almost all of SAP is simple queries, and these are healthy on the whole. Many queries run 100K times ++ in a few seconds of CPU. In terms of tuning, my general rule of thumb for a “bad” query is that it runs “enough”, and uses at least .005 seconds of CPU per execution.
Summary
On the whole, I am impressed with SAP and the ease of administration. SAP does have a methodology and you are well advised to follow it. (Could you survive if you didn’t? I shudder to think about it.)
Most performance problems were found to be due to our modifications and our custom coding and some missing indexes during implementation. Were we lucky to have missed those indexes and add our own? In some ways, yes, as it caused us to evaluate more of our environment.
Once you get to know SAP, it really isn’t all that bad.
About the Author
Martin Hubel has worked both on z/OS since 1985 and on LUW since 1993. He consults and teaches on almost all DB2 topics, is an IBM Champion and IBM Gold Consultant. Martin is a member of the DB2 LUW SAP Technical Leadership Exchange, the DB2 SAP Virtual Users Group, and is a member of the IDUG Volunteer Hall of Fame. He can be reached at martin@mhubel.com.