SAP HANA Optimization

Panelists: Christian Baessler and Jonathan Haun from Syskoplan Reply
Date: Thursday, September 20
Sponsor: SAP HANA Administration Bootcamp

Read the chat transcript below to get your SAP HANA administration questions answered by experts in the field, Christian Baessler and Jonathan Haun from Syskoplan Reply. Whether you are planning out your SAP HANA system architecture, determining the best deployment scenario for your needs, or optimizing your installation, you can get practical advice based on lessons from Christian and Jonathan’s multiple deployments.

Bridget Kotelly: Hello, and welcome to today’s live Q&A on SAP HANA optimization. We’re looking forward to a lively discussion. Thanks to everyone who has submitted questions in advance.

We’re joined for today’s event by Christian Baessler and Jonathan Haun from Syskoplan Reply owa. Christian and Jonathan are frequent speakers at SAPinsider events and will be presenting on the upcoming SAP HANA Administration Bootcamps taking place October 17-18 in Prague and November 27-28 in Orlando. To learn more, click here.

Christian and Jonathan, thanks so much for taking the time out of your busy schedules to be here.

Jonathan Haun: Thank you for hosting us today.

Bridget Kotelly: To start things off, can you highlight some of the key differences between administering SAP HANA and administrating traditional databases.

Christian Baessler: You can look at SAP HANA administration from two perspectives: For the beginners who want just to migrate (for example, an SAP ECC system from a conventional database to SAP HANA), it is still a database. SAP HANA uses similar concepts to what you know from using databases such as Oracle, SQL, or DB2 apply, of course, often with different terminology.

For the advanced scenarios, however, SAP HANA has much more in it that can change the way you do things. The main difference is the changed focus from disk management to memory management.

Jonathan Haun: One major difference is that SAP HANA is 100% in memory. You have to keep an eye on the overall memory use to keep your SAP HANA system running and stable.

Comment From Mene: What are the main possibilities for improving the performance of SAP HANA? I recently found SAP Note 2399996 (Automatic SAP HANACleaner) and integrated this script into SAP HANA, and it seems to work well. Is there any other automatic SAP HANA performance optimizer available at the moment?

Jonathan Haun: The Python script in SAP HANACleaner is a great tool to help you manage routine tasks. Some of the cleanup items it helps with need to be completed more often than others (for example, pruning the backup catalog and the log segment backup files). I also like to return log segment space to the operating system (OS) and defragment the data files daily. Overall, this tool helps to reduce a lot of the normal system-generated data that many database administrators (DBAs) forget to clean up periodically. Because it is a Python script, you can also schedule it using Linux cron.

Comment From Oguzhan: Hello, how can I reduce memory use on development systems?

Jonathan Haun: The overall memory pool (or heap) is composed of a lot of items, including Code Stack, System Tables, Row Tables, Column Tables, and Temporary Computation space. If your Row Tables or Column Tables are using most of the memory, you can archive data to reduce the memory use. If Temporary Computation and other cache allocators are using most of the random-access memory (RAM), you can contact support and request help adjusting some of these settings. You also have to consider custom-developed SQL and SAP HANA models. Depending on their design, memory use can be very high during execution. In these cases, you need to optimize the SQL or model to reduce memory consumption.

Comment From bsp: What tools are available for SAP HANA partitioning and transmutation (P&T) performance monitoring and tuning improvement?

Jonathan Haun: 

The SAP HANA cockpit contains many GUI tools to help you monitor and analyze system resource use at a very detailed level. Those same tools use SQL against the built-in system views, and you can use SQL to perform the same analysis. You can analyze SAP HANA models and SQL queries using the plan visualization tool built into SAP HANA studio. With this tool you can see which area in your code is consuming the most CPU and memory resources.

Christian Baessler: For advanced scenarios, SAP HANA has a lot of built-in capabilities that potentially can change the way you design and realize solutions. The probably most obvious ones are the reporting capabilities in SAP HANA.  The mere performance improvements of SAP HANA can be very significant, allowing you in some cases to run reports directly in SAP ERP Central Component (SAP ECC). In the past SAP ECC without SAP HANA required analytic tools just for performance reasons.

SAP HANA also has a lot of built-in analytic extract, transform, and load (ETL) tools that allow you to model or aggregate data for reporting purposes in the database  itself (however, available resources need to be considered, and it may not make sense to do this on the same database that your SAP ECC system is running on).

That’s where SAP HANA gets very exciting and different.

Comment From Lalit Sharma: While we are setting up an SAP HANA landscape, do we need to have the same schema name for the database across the Dev, QA, and PRD environments? If not, then how do we handle security role promotion in the environment?

Jonathan Haun: Using the same schema names makes it much easier to promote repository roles from DEV, QA, and  PRD because the design-time roles reference the runtime schema name, requiring the schema to exist before the design-time object can be activated. If this is not the case in your environment, create one or more empty schemas in each environment so that the roles will be activated. This step requires that you also create multiple individual repository roles for each schema. You can then selectively grant one of these roles depending on the environment.

Christian Baessler: Using the same schema name for all systems (of the same system type — for example, the same schema name for all SAP ECC systems and another name for all SAP BusinessObjects Business Intelligence suite-based systems), I would definitely recommend that. It makes your life so much easier during system refreshes.

Note that this concept can be also applied in a multi-tenant environment (which is the standard way of doing it in SAP HANA 2.0).

Comment From Marco: How can you monitor your system’s current performance, and how can you fine-tune it?

Jonathan Haun: I gave details about monitoring your system’s performance when I responded to the question from bsp earlier. Message me if you would like more details.

Comment From public sector cust: For hardware, what are the pros and cons of the appliance model versus the SAP HANA tailored datacenter integration (TDI) model? When I look on the web for advice on the appliance versus TDI models, I find mostly older opinions from when TDI was brand new. According to the content on these sites, with TDI you get lower acquisition costs, but have to integrate the servers and storage from different vendors yourself. I’m asking this question because our hardware partner who sells both models says the appliance model is best, while one of the Linux distribution vendors told me that the appliance model would be going away and TDI was the future. I would like to narrow the question to the case when the hardware partner makes both servers and storage so that I can get either an appliance with the implementation consulting baked into the price or get TDI servers and storage with consulting from the hardware partner. Either way, I would like to have one hardware partner involved so that the differences are smaller. So how do I decide?

Jonathan Haun: In most cases, the TDI model allows you to integrate SAP HANA into hardware you already own and know how to administer. The cost savings are based, in part, on this model.

TDI also supports the virtualization model that can also help you save on the cost of high availability and disaster recovery. Maintenance can easier when you use VMware vSphere vMotion as well.

Using the entry-level systems running on the Intel E5 processor are also another way to save money. While they are not as powerful as more advanced systems, for smaller scale-up systems (less than 1 GB), they can save you considerable money. Under TDI rules, you can consider deploying a solution on these types of systems.

Comment From Jacob: Could you name one of the most important maintenance tasks in SAP HANA?

Jonathan Haun: I’ll name backup of the data and logs, followed by maintenance on the backup catalog and log segment backup files. If you forget to do this cleanup, your log segment backups can consume the storage. Once they are full, the log segments themselves begin to fill up the /hana/log partition. Once both are full, your database will stop functioning.

Comment From customer: Do we need to create user accounts for everyone within the SAP HANA database?

Jonathan Haun: This depends on the solution and access you are expected to give. For example, if you are implementing sidecar reporting, you likely need to create an account for each user who queries the data. This step is required when you need to set up data security on a per-user basis. However, if you are running SAP S/4HANA 1610, you can force users to access the system via the application server (via a Remote Function Call [RFC]). In this case, you don’t need accounts for each end user within the database. Regardless of what the solution is, you will likely need users for the Basis, security, and development teams. Combined with audit policies, individual user accounts help provide better accountability.

Comment From Maurice: As a team manager and for myself, what critical skills should we be sure to have or learn so that we can be the master of our SAP HANA environment?

Christian Baessler: For moving to SAP HANA, you need to:

• Become familiar with the SAP HANA tools
• Become familiar or very familiar with Linux (Red Hat, SUSE), as this is the only supported OS for SAP HANA (this may not be an issue in your environment).
• Develop SAP HANA skills — regardless of whether you consider the TDI approach or appliance model (where usually the hardware vendor is bringing or installing everything ready to use to your datacenter: Maintenance of SAP HANA is on you or the customer’s site.
• Understand general SAP HANA concepts, processes, and architecture so that you can troubleshoot if something does not work and understand where you need to look for information.
• Know what you need to do if you run an SAP system (for example, SAP ECC) on SAP HANA

It also helps to understand specific SAP HANA SQL so that you get a better idea of what else SAP HANA provides, as SAP will take advantage more and more of that functionality within its standard solutions.

I’d recommend that you attend one of the SAP HANA Administration Bootcamps — either October 17-18 in Prague or November 27-28 in Orlando.

Comment From Chen: Have you experienced SAP HANA server crashes, and if so, could you comment on the common causes and steps necessary to prevent this, besides the obvious step of upgrading to a newer SAP HANA version or patch?

Jonathan Haun: If you run out of memory, the system can crash. You might also have a bug and need to look at upgrading or patching SAP HANA.

Comment: I faced an issue today in restoring my system. I entered a backup prefix as FULL_date_time, but the same prefix is not taking during the restoration. Could you offer any guidance?

Jonathan Haun: If you can locate the files on the file system or within the backup catalog, you should be able to determine the prefix that was used.

Comment From Sunil G: After migrating to SAP Business Suite on SAP HANA, a client is not seeing enough speed and optimization in processes, specifically electronic data integration (EDI) order load, which still takes three to five seconds per order load. When this client receives more than 10,000 orders daily, the process is not fast. Do you have any insights on how to troubleshoot this problem?

Christian Baessler: I would need a little more information. Is the performance faster, slower, or roughly the same as in the old environment (I would expect the performance to be the same or  maybe somehow faster).

You start with the overall performance trace within the SAP system. If you can determine that the database is slowing down the process, then it’s time to dig in that direction. But my first guess, based on my experience, is that in EDI scenarios, it’s usually not the database, or if so, the database is only indirectly causing a performance issue when, for example, a lot of custom code has been implemented such as for sales order processing.

Comment From Chen: What is the best way to refresh and export or import user security in the SAP HANA database?

Jonathan Haun: If you use repository roles, you can export or import the delivery unit. However, you will need to script the granting of these roles and save the script to quickly re-implement the security. There is no way to export users and their security out of the box.

Comment From Marlo: What are your recommendations for running multi-tenant database containers in an SAP landscape?

Jonathan Haun: Starting with SAP HANA 2.0 support package stack 01, you have to run multi-tenant.

Comment: What is the toolset to monitor SAP HANA? Should I use the SAP HANA cockpit, SAP HANA studio, or SAP Solution Manager? What else can I use? Which tool offers the best alerting?

Christian Baessler: I think you mentioned the main tools coming from SAP. For alerting,
SAP HANA has alerting capabilities (via email). SAP Solution Manager Technical Monitoring is a good solution as well.

I would determine your overall strategy to decide which way to go. SAP HANA is good for monitoring SAP HANA itself, but it does not monitor the other levels (mainly within an SAP system). If you are monitoring SAP objects (for example, short dumps), heartbeat on the SAPGUI and others are relevant. You definitely want to consider SAP Solution Manager or one of the third-party tools to support the SAP system.

Comment From Mauricio: When SAP HANA is starting after an issue and is taking a lot of time, how can we check or monitor the crash recovery progress? How can I receive some sign to check that everything is going all right?

Jonathan Haun: In some cases SAP HANA has to replay transactions from the log in addition to reloading tables into memory. You will see this in the indexserver trace file.

Comment From Carlos: How can I export data from SAP HANA in an Oracle-compatible format, excluding comma-separated values (CSV) files? Currently, we send Oracle export dump files, but we don’t have any idea on how to do it with SAP HANA in the future. The client database is Oracle.

Jonathan Haun: You will need to use SAP Data Services to extract data from SAP HANA and load it into Oracle.

Comment From Maurice: We see more and more applications tailored for use on SAP HANA. I know it may be a case-by-case question, but generally do you need to create an SAP HANA user for each person accessing the database?

Jonathan Haun: See my response to the comment from customer above.

Comment From public sector cust: Specifically for multi-tenant in an SAP landscape, on the production SAP HANA server or appliance, if we have two independent production ERP systems, PR1 and PR2, should we have their databases be tenants in the same PRD SAP HANA environment, or instead have MCOS with a PR1 HANA tenant and PR2 HANA tenant. Both systems have the same maintenance window and the same Basis team.

Christian Baessler: Well, I can only express my preference: If these are two different systems, there is a reason for it. It’s likely that they have eventually different maintenance cycles (upgrades, patches, and so on). To stay flexible I would separate them as much as possible.
I would definitely not run them in one tenant or schema as it will be very hard to control resources between the two environments. I would almost even tend to not have them in the same database and different tenants (applies for prod servers), but that’s my preference and depends partially on the available budget.

Jonathan Haun: For more information, read this blog: Choosing the Right HANA Database Architecture.

Comment From public sector cust: For test databases for SAP Business Suite on an SAP ERP system, can storage snapshots (provided by a storage array vendor) be used to set the entire SAP HANA database back to a previous point in time, including the log volume, with no database recovery needed? I’m asking this question because we have been doing this with Oracle for years: The developer, integrator, or analyst reaches some milestone in configuring the test system, we shut down the SAP and Oracle systems, and have the storage array take a snapshot of just the file systems containing all of the database elements (data files, online redo logs, control files). We then restart the Oracle and SAP systems, and testing continues. If testing needs to be redone, we stop the SAP and Oracle systems, have the storage array revert the file systems, then start Oracle (no database recovery; the Oracle database is unaware) and the SAP systems. In just a few minutes, they have their system back to how it was. Developers like this capability. I am not asking about using storage array snapshots for the production SAP HANA database, just for the test, which we have the luxury of stopping to take a snapshot. We keep multiple snapshots of the test Oracle database for months during a project. What I’ve read about storage array snapshots and SAP HANA says not to snapshot the log volume because we want to roll forward using logs, but this is production advice, so we would want to ignore this advice as it would be for a test SAP HANA database. I’ve also read information from NetApp that keeping array snapshots of SAP HANA for very long is challenging because of the greater rate of change due to the index model.

Jonathan Haun: This is a hard question to answer completely here. But storage snapshots are a point in time. You would need to use a combination of snapshot restore and log segment restore to restore to a specific point in time. Otherwise, your restore of the snapshot would be limited to when it was taken.

Comment From Rob: For enabling data volume encryption what is the performance impact?

Jonathan Haun: It adds a very small amount of overhead in my experience. It is mostly increased CPU but it is very small.

Jonathan Haun: If you have any follow-up questions, please email me at jonathan.haun@enowa.com or Twitter @jdh2n.

Christian Baessler: Thanks for participating. If you have further questions, message Jonathan (see his email above) or me at Christian.Baessler@enowa.com

Comment From Carlos: Can you perform regular database administrator tasks without a system user? Is it recommended? If we create a specific user for database administrator tasks, should that user be created on the SYSTEM database or the tenant database?

Jonathan Haun: Yes, you can create users with almost the same exact privileges. There are only 55-60 privileges that only the SYSTEM has, but they are not something that is used often.

Bridget Kotelly: Christian and Jonathan, thanks so much for answering all of the questions today. I look forward to seeing you both at the bootcamps in Prague and Orlando.

I also thank everyone who attended. To learn more about this topic and get hands-on experience, join us for the SAP HANA Administration Bootcamp. These bootcamps are being held on October 17-18 in Prague and November 27-28 in Orlando. Find out more and register here,