Area

Details Step When Reference
General Performance

General SAP HANA Checks: Ensure system is running in

healthy state and critical alerts are picked up.


Recommend to run: post installation, revision update,

recovery, weekly, monthly or quarterly, etc.

run scripts HANA_Configuration_MiniChecks* and examine the result.

Post Installation and

Maintenance

1999993 – How-To: Interpreting SAP HANA Mini Check Results


1969700 – SQL statement collection for SAP HANA


General Performance

SAP HANA Parameter Checks: Ensure relevant

parameters are set correctly


Recommend to run: post installation, after every parameter

changed, weekly, monthly or quarterly, etc.

run scripts HANA_Configuration_Parameters_Rev* and examine the result.

Post Installation and

Maintenance

2186744 – FAQ: SAP HANA Parameters


1969700 – SQL statement collection for SAP HANA


Workload Management

Memory tracing & limit:

i) Monitor memory consumption of SQL statements

ii) Protect SAP HANA system from excessive memory

usage due to uncontrolled queries/database request



i) global.ini -> [resource_tracking]

-> enable_tracking = on

-> memory_tracking = on

ii) global.ini -> [memorymanager]

-> statement_memory_limit = <GB>

*Note 2222250: In general it is useful to start with a rather high memory limit. You can e.g. take 30 % of the first 500 GB of the global allocation limit and 15 % of the remaining memory as a starting point:

Global allocation limit statement_memory_limit
250 GB 75 GB
500 GB 150 GB
1000 GB 225 GB
3000 GB 525 GB

*check and consult with functional/ business user to adapt to critical business queries on later stage

-> statement_memory_limit_threshold = <%>

*Optional: statement_memory_limit parameter is taken into account only when total memory usage reaches above threshold. No statements have to be canceled if total memory is below the threshold. This allows expensive statements that consume more than the allowed statement memory limit to finish successfully during periods when a system runs under no load (for example, during the night).


Post Installation and

Maintenance (adjust value depends on system’s workload)

1999997 – FAQ: SAP HANA Memory


2222250 – FAQ: SAP HANA Workload Management

Workload Management

CPU measurement:

i) Activation of CPU specific data collection and show CPU time

required for processing the statement.

Allow CPU time analysis and see what’s happening in more

detail in Studio -> Performance -> Thread:

*Note 2100040: activation of the CPU time measurement can be

expensive in terms of system CPU consumption and performance, so you should pay attention for these side-effects and deactivate the time measurement if required.


i) global.ini -> [resource_tracking]

-> enable_tracking = on

-> cpu_time_measurement_mode = on [SPS09 and above] | fast [SPS08 and below]


ii) global.ini -> [resource_tracking]

-> service_thread_sampling_monitor_thread_detail_enabled = true


*THREAD DETAIL, THREAD METHOD not populated per default in M_SERVICE_THREAD_SAMPLES view and only written when parameter above is set to true.


Once set to true, THREAD DETAIL and THREAD METHOD will populated

when doing performance analysis with script HANA_Threads_ThreadSamples_*

Example of THREAD DETAIL and THREAD METHOD:

Thread Type

Thread Method

Thread Detail

JobWorker

calc

PlanExecutor calc

plan<id>@<host>:<port>/pop<id> (<action>)

MergedogMerger

merging

<x> of <y> table(s):

<table>

SqlExecutor

BatchExecute

(batch size:<size>) <sql_statement>


Post Installation or

activate when needed

2100040 – FAQ: SAP HANA CPU



2114710 – FAQ: SAP HANA Threads and Thread Samples

Workload Management

Workload Management – Activate expensive statements trace.

Recommend to activate this trace on a permanent basis due to the

significant value added and small performance overhead

(with reasonable thresold)

Extremely useful for SQL optimization and performance analysis.

i) activate expensive statements trace:

-> global.ini -> [expensive_statement] -> enable

ii) available criteria and indicators that mark a SQL statement as expensive:

Runtime: global.ini -> [expensive_statement] -> threshold_duration

CPU Consumption: global.ini -> [expensive_statement] -> threshold_cpu

prerequisite: enable below

global.ini -> [resource_tracking]

-> enable_tracking

-> cpu_time_measurement_mode

Memory Consumption: global.ini -> [expensive_statement] -> threshold_memory

prerequisite: enable below

global.ini -> [resource_tracking]

-> enable_tracking

-> memory_tracking

*Set to an accpetable thresold value and higher limit to avoid overhead and increase unnessary system resource consumption.

Recommended and suggested starting value (based on average to large system workload)

threshold_cpu = 300 000 000 us (5 mins)

threshold_duration = 900 000 000 us (15 mins)

threshold_memory = 2 147 483 648 byte (2GB)

*adapt the setting above by monitor the number of expensive statement generated in M_EXPENSIVE_STATEMENTS.


Post Installation

Maintenance (adjust value depends on system’s workload)

2180165 – FAQ: SAP HANA Expensive Statements Trace


2000002 – FAQ: SAP HANA SQL Optimization


2114710 – FAQ: SAP HANA Threads and Thread Samples

Workload Management

ESS – Collector status

Ensure relevant history collections are activated to ease performance

analysis, troubleshooting and investigation, extremely helpful with

complete resource information, usage provided when

execute perfomance analysis scripts, example HANA_IO_KeyFigures_Details_History & HANA_Threads_ThreadSamples_FilterAndAggregation and etc.

UPDATE “_SYS_STATISTICS”.”STATISTICS_SCHEDULE” SET STATUS = ‘Idle’ WHERE STATUS = ‘Inactive’ AND ID NOT IN (41, 5008, 5024, 5025, 5033, 5035, 5047)

If you find disabled actions due to timeout, you can enable them with the following command:

UPDATE “_SYS_STATISTICS”.”STATISTICS_SCHEDULE” SET STATUS = ‘Idle’ WHERE STATUS = ‘Disabled’ AND STATUSREASON = ‘timeout’


Post Installation 2147247 – FAQ: SAP HANA Statistics Server
Workload Management

ESS – Collector Retention period

With the embedded statistics server the retention time for most of the

histories is already 42 days. Recommend to set the retention to more

than a month for month-end perfomance analysis and comparison.


Howeer, you can adapt the retention of each collector based on your

business needs.


UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET RETENTION_DAYS_CURRENT = 42 WHERE ID NOT IN ( 5008, 5024, 5025, 5026, 5033, 5035, 5047 ) Post Installation 2147247 – FAQ: SAP HANA Statistics Server
System Availability

Increase xsengine log segment

The size of a logsegment of the XSEngine is set to 8MB by default. In

certain circumstances (e.g. full log backup volume) this might be too

small as the number of logsegments might grow too fast and reach

the limit of 10240 logsegments, thus, causing system standstill.


Hence, it might make sense to increase the size to 64MB.


ALTER SYSTEM ALTER CONFIGURATION (‘xsengine.ini’, ‘SYSTEM’) SET (‘persistence’, ‘log_segment_size_mb’) = ’64’ WITH RECONFIGURE; Post Installation 2062080 – SAP HANA DB: Increasing the size of logsegments of XSEngine
General Performance

Increase SQL Plan Cache

Performance problem arise when plan cache is set to too  small due

to frequent recompilations of queries.

Current default plan cache size is 2GB. Would recommend to set to

3.2GB after fresh installation and adjust accordingly based on

system’s characteristic or workload and by monitor the

SQL plan cache usage.

if plan cache is turn off:

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’,’system’) SET(‘sql’, ‘plan_cache_enabled’) =’True’ WITH RECONFIGURE

Increase plan cache value:

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’,’system’) SET(‘sql’, ‘plan_cache_size’) = ‘<DESIRED_SIZE_IN_BYTES>’ WITH RECONFIGURE


Post Installation

and

Maintenance (adjust value depends on system’s workload)

2040002 – Size recommendation for the HANA SQL Plan Cache
Network Performance

Analyze Round Trip Time (RTT) between server and client applications. Script “HANA_Network_Clients” is useful in this case. Client performance data are only collected if parameter sql_client_network_io is enabled. By default, the value is false.

i) activate collection of network client performanc data

indexserver.ini –> [sql_client_network_io] –> enabled = true

Post Installation 2081065 – Troubleshooting SAP HANA Network

Normally, we will set and configure above as post HANA DB installation tasks. Those are optional yet important post tasks to ensure HANA system is running in optinum and healthy state, as well as provide insightful data to ease performance analysis, troubleshooting and optimization.

— Important —

Please ensure parameter set is supported by installed HANA revision. Most of the parameters above are only valid to SPS09 and above.


Any valuable feedback from time to time to make this more informative and complete are welcomed and appreciated.


Thanks,

Nicholas Chang


edited 9th March 2016: network client data performance collection parameter

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply