Sap Support

- Sap Support - Dump files for support

Log & Trace file

- Log and trace directories - Investigate backup log content

Sap Hana Global Administration

- Daily Tasks - Start/Stop database - Start/Stop Hana System


- Get Sap hana version details - Get to Log and Trace file directories - Get log backup directories - Get Sap Hana default Port - Get Session Details - Get hana user status - Get dump files diagnosis - Generate dump files diagnosis - Get user store key connection details

Tools : Hana Studio

- Installation - Using hana studio - Using Studio SQL Console

Tools : sapcontrol

- Start hana system - Stop hana System - Useful commands - troubleshoot "fail nieconn refused..."

Tools : hdbsql

- Using hdbsql - hdbsql parameters - Executing SQL - Hiding header welcome message - Hiding column header - Update column delimiter - Formating output result

Tools : hdbuserstore

- Find user store key details - hdbuserstore syntax examples

Tools : hdbrsutil

- hdbrsutil tips & tricks

Tools : Hana Cockpit

- Install hana cockpit 2 - overview - Install hana cockpit 2 complete example - Un-Install hana cockpit 2

Sap Hana Backup & Recovery

- Backup & Restore a tenant database - Backup - Restore/Recover - Checking - Log backup directories - Remove old log backup - Get backup catalog size - Restore database manually - Analyse backup log file content - Troubleshoot backint backup

Sap Hana Security

- Securing sap hana - What to do before password update - Connection & Password - Privileges to unlock a user. - Update password expiration date - Roles and privileges


- Disk space used by log segments - Reclaim space from log segments - Retrieve table index space used on disk

Specific Operations

- Adding physical memory

Sap Hana User Privileges

- Role and privileges - Grant read access to a schema

Stored procedures

- Creating a procedure - Dropping a procedure - Compiling a procedure - Executing a procedure - Working with procedures - Using anonymous block

Sap Hana functions

- DateTime : ADD_YEARS - DateTime : EXTRACT


- Configuring sap hana - Kill long queries automatically


- Sql guide line - How to use SQL -Sql references - Sql tips & tricks for tables

SQL Query

- SQL & Tables - SQL & syntaxes - How to practice SQL


- Physical memory - Memory Investigation Scenarios - Memory analysis - Memory usage details - Allocated Memory - Peak used memory usage details - Peak Memory between 2 dates - Peak Memory for a tenant database - Used memory - Used memory - Tables - Used memory - System Tables - Used memory - Column tables - Used memory - Column tables main storage - Used memory - Column tables Delta storage - Used memory - Row tables - Used memory - Processes & Services - Used memory SQL - Used memory - code & stack - Table storage comparison

Sap Hana Tables

- Managing tables - Various ways to create Tables - Sap Hana Tables - Sap Hana Temporary Tables

Table description & DDL

- Retrieve table description - Table description using table-columns-view - Table ddl with hana studio - Table ddl using 'get_object_definition' procedure - Table ddl using hdbsql


- Various ways to select data


- Various ways to insert data


SQL Programming

- Sql scripting


- Using variables - Declaring & using variables - Variable data types - Local scalar variables - Variables & anonymous-blocks - Query result into a variable - Using a variable containing an SQL syntax

If and Else

- If & else examples - Various ways to use If & else

While Loop

- While loop examples

For Loop

- Using for loop - Breaking out for loop - Skipping statement in for loop - Insert into table with for loop - Using Function with for loop - Using reverse for loop

Shell scripts

- hdbsql & shell scripts

Sap Hana Users

- Managing Users - Create user with hana studio - Create user with sql Script - Create multiple users in one go - Copy a user - Create User with SQL script - Create User via Hana Studio - Update User - Drop User

Create Standard User

- Create standard & personal User

Create Technical User

- Create a technical User - Create backup users - Create administrator users - Create Modeler User - Create Data provisioning User - Create Cockpit User - Create a user to unlock any users

User administration

Unlock User

- Activate a user - Unlock Standard and Technical User


- Connection & Password Security - Reset User connection attempt - Update login until date


- New user password - Update with old password - Reset System user lost password - Update password expiration date - Disable password validity period - Change password on first login - Change password on next login

Investigations & troubleshooting

- Retrieve User details - Check user active or not - Check user status - Check password expiration date - Show Password expiration date - Troubleshoot User

De-Activate/Lock User

- Activate User - De-activate User - Activate System User


- Overview


- User connection - General Connection issues - Connection failed RTE 89006 - Alert 10: passwd authentication


- Tenant already active when starting tenant - Common issues - Bugs - Errors - Performance - Troubleshoot Memory - Troubleshoot Memory issues - Alerts - Scripts and Procedures - Volume Disk Full - Long queries - Alert 65 log backup running too long - Alert 413: cannot reuse password - Alert 414: password to be changed - Alert 431: password expiration - 503 Service not available - Reset System User lost password - Hana System cannot start - Locked users.

Working through sap hana memory analysis

Memory in sap hana is a very large subject. As we know, sap hana is a database which works entirely in memory. Sap hana manages its memory automatically. Monitoring and keeping an eye on sap hana memory space is one thing, understand how the memory is used and dispatched is another. The following chart shows how memory is used and dispatched across available space, but also it shows where memory boundaries can be found.

memory usage map

Working through sap hana memory investigation scenarios

There are many different scenarios possible but let check out 3 different but common memory usage investigations. Where to start with peak memory usage. How to proceed testing a batch processing. How to validate a data import procedure.

memory usage map

Sap hana memory usage explained

Here is a brief description of memory usage. Explanations start from physical memory up to specific sap hana memory usage. More advance explanations, charts and SQL links can be found further down this page.

Host Server Physical Memory:
This is the total physical memory size actually installed on the machine. Free memory slots which might still be available on the server, are not counted as part of the physical memory.

Resident Memory:
This is the actual physical memory being used to run sap hana and the OS server.

Sap Hana Virtual Memory:
This is a size of memory that is dedicated to run sap hana. It is not real memory. It is reserved space from the physical memory for sap hana processes.

Sap Hana Pool Memory:
This is the global sap hana memory that is being allocated. Pool memory is filled with data, processes and queries, code and stack. Free memory is also part of the pool.

Sap Hana Used Memory:
This is the total sap hana memory usage. Database objects such as data table will be part of this memory usage. Queries and working operations come and go from the memory as needed. Those movements increase and shrink used memory size all the time.

Sap Hana Peak Used Memory:
This is the total sap hana memory usage that has reached an unusual high size in the total available memory pool. Peak Used Memory and Allocation limit are to be watch closely. One meeting the other is bad news.

Sap Hana Free Memory:
This is the memory available at the current time. The pool area size grows and shrinks depending on database activities.

3 elements in regards to memory areas.
1/ The physical memory,
2/ The Resident Memory,
3/ The Sap Hana used Memory

memory allocation map

Server physical memory and Sap hana

Physical Memory: This is all memory available on the host server. It represents all memory bars which have been currently installed. It is up to you to check whether more memory bars could be added to the host in order to increase memory space. Keep in mind, at this level, memory has nothing to do with sap hana.

Sap hana and resident memory

Resident Memory: It represents the physical memory that is being used at the os level. All running processes, whether there are sap hana processes or OS program processes, are part of the resident memory.

Sap hana and virtual memory

Virtual Memory: This memory relies on existing physical memory. This specific sap hana RESERVED memory is not entirely allocated straight away. The memory manager requests memory from the OS using this reserved pool allocation, and does it until the memory allocation limit is reached. The total size and the growth depends on sap hana memory space allocation configurations and on sap hana running processes memory requirements. As it is being allocated it becomes part of the resident memory. The memory size goes up and down depending on data growth and on all temporary computational works.

Sap hana SQL management Tools

There are only few sap hana SQL tools on the market. Sap provides 2 types of SQL management tools. Sap hana Studio SQL console is a friendly client interface software which provides the most advanced SQL capability on sap hana databases. Hdbsql is a sap hana os command line sql tool which is mainly used and integrated within unix

Sap hana studio SQL console?

Using hana sql console

Sap hana hdbsql?

using hdbsql

Sap Hana and Used Memory

Used Memory: This is the total memory size that is currently used by sap hana. Within used memory you will find, data such as row and column tables, delta data storage, code and stack, all queries and temporary computational work. Used memory size is not static. It fluctuates up and down depending on temporary activities such as application query requests.

Sap Hana Peak Used Memory Investigation

Peak used memory is perfectly normal in most situations. However, it would be a good idea to check and follow the memory whenever a batch or a query is being run in an exceptional situation, especially if no particular validation has been performed up front.

The following example shows normal peak used memory ( in blue ) and peak memory reaching a zone where alert messages will be triggered. ( in Red ). In the current example more memory can be added via new physical memory and via new sap license. This will stop alert messages and possible problem can be avoided.

Peak used Memory :
- In Blue : OK. Normal memory usage.
- In red : Warning. Abnormal use of memory. Investigations required.

Sap Hana Peak Used Memory reaching deadly memory limit

Peak used memory reaching deadly threshold is very serious as the current database could become unstable and crash. Other databases running on the same SYSTEMDB could also be affected and follow the same fate.

In the current example, there is no way out to prevent a database crash unless the high memory usage reason is being sorted out. It is a critical situation especially because adding new physical memory is not possible. In this example, queries are likely to be the reason of high memory usage.

Sap hana and free memory availability

Free memory will always be available unless nothing is done to prevent memory shortage. Sap hana memory would have been sized initially according to data requirements and data growth estimation. There should be plenty of free space in a sap hana healthy system. However, memory can become suddenly short in the following situations:

A common and regular problem is a peak memory usage which takes most of the free memory in order to run a specific job requirement. It is happening in most cases because queries were not optimized and validations were not performed. Keep in mind, when too many bad queries are executed at the same time, the overall tuning may become impossible. Tuning single query is therefore very important.

Memory shortage could also be shown in an healthy system, where data is being added month after month, year after year and one day memory alerts start to show up. That situation can be as difficult to sort out. If hardware can accommodate more physical memory, there is no real issues. Just getting a new sap license and new physical memory. Otherwise, it become a database migration project from one host to another bigger one. That will not be possible in a short time scale.

Sap hana SQL

Sap hana tools

- - -