Symptom
- Run jobs from SAP Data Services Designer and see huge performance impacts.
- Same job triggered in both QA and DEV system but performance issue captured only in QA
- Collected HANA SQLtrace for both systems (as per note 2031647 - How to enable SQL Trace in SAP HANA Studio) and result looks like:
> DEV - eds_landing_<hostname>_30603_000.py
...
2021-10-06 18:01:31.173510 UPDATE begin
2021-10-06 18:01:31.175321 UPDATE finish duration: 1811
2021-10-06 18:01:31.177249 INSERT begin latency: 1928
2021-10-06 18:01:31.177915 INSERT finish duration: 666
2021-10-06 18:01:31.179860 UPDATE begin latency : 1945
2021-10-06 18:01:31.181723 UPDATE finish duration: 1863
2021-10-06 18:01:31.183645 INSERT begin latency : 1922
...
Avg latency: 1,920 (microsecond)
> QA - eds_landing_<hostname>_30440_000.py
...
2021-10-14 17:54:18.218845 UPDATE begin
2021-10-14 17:54:18.222540 UPDATE finish duration: 3695
2021-10-14 17:54:23.595376 INSERT begin latency: 5 second 372836 (?!)
2021-10-14 17:54:23.598180 INSERT finish duration: 2804
2021-10-14 17:54:23.914077 UPDATE begin latency: 315897
2021-10-14 17:54:23.918985 UPDATE finish duration: 4908
2021-10-14 17:54:24.242236 INSERT begin latency: 323251
2021-10-14 17:54:24.244343 INSERT finish duration: 2107
...
Avg latency: 350,000 (microsecond)
- Huge latency difference between UPDATE and INSERT statement executions
- Checked NIPING test (with hostname with fully qualified)from app server to QA and app server to DEV but no issue found
- Since QA is scale-out HANA system, checked distribution mode (client_distribution_mode) on HANA server and client connection property DISTRIBUTION on HANA client. Both are set as 'statement' which is correct based on
"Settings on client and server side are negotiated in the following way" from the note
2200772 - FAQ: SAP HANA Statement Routing and Client Distribution Mode
2079372 - SAP HANA - Statement Routing
Read more...
Environment
- Environment upgraded to
Azure
SAP BI IPS 4.2 SP7 Patch 6
SAP DS 4.2 SP 13 Patch 4
SAP HANA 2.0 SP05 (scale-out)
SLES 12 SP4
- Environment upgraded from
HEC
SAP BI IPS 4.2 SP7 Patch 6
SAP DS 4.2 SP 13 Patch 1
SAP HANA 2.0 SP03 (scale-up)
SLES 12 SP3
Product
Keywords
SAP, Data Services, HANA, scale out, performance, hostname, , KBA , HAN-DB , SAP HANA Database , EIM-DS-EXE , Job Execution , Problem
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.