Introduction
Update: Peak Memory and CPU for Task Executions
Since wave 13 combining expensive statement information (CPU, Peak Memory) with task log information is delivered as a standard functionality. The monitoring view TASK_LOGS_V_EXT has additional columns for memory and CPU consumption. They are copied over from the expensive statement table and persisted into the task log table. Note that you have to re-import the content table to expose the columns in data modelling. This has the helpful side effect, that we now have a history that goes along with the task log table and is independent from the expensive statement view. Please check out details from the product documentation here. |
Data Warehouse Cloud allows you to configure the thresholds for capturing expensive statements (help page).
These are the delivered default settings:
 |
This is a very helpful functionality for every admin – unfortunately something important is missing:
- How do I identify that the statement is related to SAP Data Warehouse Cloud?
- How do I map the captured statements to the related activity in SAP Data Warehouse Cloud?
Without this information you can only see that something expensive happened – but you can’t change it as the initiating process stays unknown. Or other way round: you have defined a task in DWC and would like to check if it is “expensive”.
Luckily we can at least find and map the expensive statements for some SAP Data Warehouse Cloud tasks. This blog post describes how to do it for the data integration features view persistence and remote table persistence.
Technical Background
Every HANA task comes with a set of session variables. If the SAP Data Warehouse Cloud task framework is the initiator, the variable '$$DWC_PROCEDURE_RUN$$' is added.
With that information we can immediately select all expensive statements that are related to direct or scheduled execution. The following statement would return all entries that are related to the task framework:
SELECT *
FROM M_EXPENSIVE_STATEMENTS
WHERE "SESSION_VARIABLES"
LIKE '%$$DWC_PROCEDURE_RUN$$%'Mapping to Task Information
The value of the variable is a Task log id. This is an identifier for each task execution and hence can be used to lookup all attributes of a task like space id, name of the schedule, runtime, etc.
The view TASK_LOGS_V_EXT contains this mapping and is accessible from a space tagged as monitoring space. Detailed information about this view can be found on this SAP Help Portal.
Here is a screenshot of the view definition and the main columns:
The remaining task is now to combine the filter on the expensive statement table with the lookup on the task log view. This is described in the next chapter.
SQL View - Filter and Combine
The SQL view exposes a subset of the task attributes together with a subset of the expensive statement view:
- SPACE_ID: space name of the task definition
- APPLICATION_ID: identifies the task type – e.g. view persistence
- ACTIVITY: g. persist, remove, …
- OBJECT_ID: the id of the schedule
Note: Cleaning the task logs (Configuration -> Task Logs -> Clean Up Older Than) will also delete content of TASK_LOGS_V_EXT and hence might affect the lookup.
Please create an SQL view with the name TASK_EXP_STATM_V_H_01 in your monitoring space (see also setting up a Monitoring Space in SAP Data Warehouse Cloud on the SAP Help Portal).
/*
help page for M_EXPENSIVE_STATEMENTS:
https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20af736e751910148162e2ab19...
help page to configure threshhold for expensive statements in DWC:
https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/9cd0691c44a74f2aa47b52f615f...
This view searches for entries in the M_EXPENSIVE_STATEMENTS that are related to DWC DataIntegration.
This clasification is based on an internal property of the SESSION_VARIABLES -> $$DWC_PROCEDURE_RUN$$
*/
SELECT
"DB_USER",
"SCHEMA_NAME",
"APP_USER",
"EXP_ST_START_TIME",
"DURATION_MICROSEC",
"OBJECT_NAME",
"OPERATION",
"RECORDS",
"STATEMENT_STRING",
"PARAMETERS",
"ERROR_CODE",
"ERROR_TEXT",
"MEMORY_SIZE",
"REUSED_MEMORY_SIZE",
"CPU_TIME",
"STATEMENT_START_TIME",
"APPLICATION_SOURCE",
"APPLICATION_NAME",
"NETWORK_MESSAGE_ID",
"WORKLOAD_CLASS_NAME",
"PRIORITY",
"STATEMENT_THREAD_LIMIT",
"STATEMENT_MEMORY_LIMIT",
"SESSION_VARIABLES",
"TABLE_TYPES",
"TASK_LOGS_V_EXT"."TASK_LOG_ID" TASK_LOG_ID,
"TASK_LOGS_V_EXT"."SPACE_ID" SPACE_ID,
"TASK_LOGS_V_EXT"."APPLICATION_ID" APPLICATION_ID,
"TASK_LOGS_V_EXT"."ACTIVITY" ACTIVITY,
"TASK_LOGS_V_EXT"."OBJECT_ID" OBJECT_ID
FROM (
SELECT
"DB_USER",
"SCHEMA_NAME",
"APP_USER",
"START_TIME" EXP_ST_START_TIME,
"DURATION_MICROSEC",
"OBJECT_NAME",
"OPERATION",
"RECORDS",
"STATEMENT_STRING",
"PARAMETERS",
"ERROR_CODE",
"ERROR_TEXT",
"MEMORY_SIZE",
"REUSED_MEMORY_SIZE",
"CPU_TIME",
"STATEMENT_START_TIME",
"APPLICATION_SOURCE",
"APPLICATION_NAME",
"NETWORK_MESSAGE_ID",
"WORKLOAD_CLASS_NAME",
"PRIORITY",
"STATEMENT_THREAD_LIMIT",
"STATEMENT_MEMORY_LIMIT",
"SESSION_VARIABLES",
"TABLE_TYPES",
-- JSON_VALUE has issues with $$ in the property name
substr_before(substr_after(SESSION_VARIABLES, '"$$DWC_PROCEDURE_RUN$$": "'), '"') TASK_LOG_ID
FROM "M_EXPENSIVE_STATEMENTS"
-- if the session is initiated by the task framework, it contains the property TASK_EXECUTION_ID
WHERE "SESSION_VARIABLES" LIKE '%$$DWC_PROCEDURE_RUN$$%'
) EXP_STATEMENTS_TASK_RELATED
LEFT JOIN "TASK_LOGS_V_EXT"
ON "TASK_LOGS_V_EXT"."TASK_LOG_ID" = EXP_STATEMENTS_TASK_RELATED."TASK_LOG_ID" SQL View - For Reporting
This is a sample view for reporting purposes. It exposes the duration, memory and CPU time as measure and as dimension. You might wonder why the figures are added as dimension in addition – the reason is that I like to enable a filtering (e.g. show all entries with a peak memory greater than 10 GB).
Step 1: Create SQL in the monitor enabled view
SELECT "DB_USER",
"SCHEMA_NAME",
"APP_USER",
"EXP_ST_START_TIME",
"DURATION_MICROSEC" / 1000 DURATION_MS,
"DURATION_MICROSEC" / 1000 / 1000 DURATION_SEC,
"OBJECT_NAME",
"OPERATION",
"RECORDS",
"STATEMENT_STRING",
"PARAMETERS",
"ERROR_CODE",
"ERROR_TEXT",
1 ROW_COUNT,
to_int(round("MEMORY_SIZE" / 1024 / 1024)) MEMORY_SIZE_MB,
to_int(round("MEMORY_SIZE" / 1024 / 1024 / 1024)) MEMORY_SIZE_GB,
"CPU_TIME" / 1000 CPU_TIME_MS,
"CPU_TIME" / 1000 / 1000 CPU_TIME_SEC,
"STATEMENT_START_TIME",
"APPLICATION_NAME",
"WORKLOAD_CLASS_NAME",
"STATEMENT_THREAD_LIMIT",
"STATEMENT_MEMORY_LIMIT",
"TASK_LOG_ID",
"SPACE_ID",
"APPLICATION_ID",
"ACTIVITY",
"OBJECT_ID"
FROM "TASK_EXP_STATM_V_H_01"Step 2: Select type Analytical Dataset
Step 3: Set the following dimensions as the measures:
- DURATION_MS
- MEMORY_SIZE_MB
- CPU_TIME_MS
- ROW_COUNT
The related dimensions DURATION_SECS, MEMORY_SIZE_GB and CPU_TIME_SEC should be kept as normal dimension/attributes.
Reporting Scenarios
List all Tasks With Peak Memory Greater than 10 GB
This is pretty straight forward. We have the dimension MEMORY_SIZE_GB that contains the peak memory in GB. Just add a filter greater than 10 by the following steps:
Navigate to the filter section in SAC (after you have inserted a table using the DWC view you just created) and select the Peak Memory (GB) (Range) from the dropdown:
In the following dialog, select greater than with a value 10.
Conclusion
Combine the memory and runtime figures from expensive statements with the modeling artefacts of DWC provide a great help in monitoring and understanding the impact of the tasks in terms of system resources. Especially during first productive usage it is highly recommended to evaluate the impact and to take measure to reduce or distribute the load to avoid overload situations.
I’d be happy to hear your thoughts, ideas and comments on this monitoring topic. Let me know in the comments!
- SAP Managed Tags:
