## 3.2.7.2 Examine the Workload
We can now examine the workload in detail including: CPU utilization, table distribution and the plan cache.
The system information you have collected helps to understand the system behavior given a workload of interest. For the illustration given here we run the TPC-H workload including updates using scale factor 100 and 40 concurrent streams. You can use this example to help to understand similar NUMA related problems when examining any customer workload.
For more information on the latest TPC benchmarks (versions two and three) refer to the Transaction Processing Performance Council web site: http://tpc.org/tpch.
The first step is to examine the CPU utilization and thread activity . The SAP HANA Cockpit provides the tile Threads . In the example shown below there are 230 active threads, that is threads scheduled for running on a CPU core, and no blocked threads. As there are only 176 logical cores on the system, the system's CPUs are highly contended.
More information on the thread activity can be found when navigating to the details of the tile as shown below or by examining the view m_SERVICE_THREADS. In this example it is evident that the thread with id 167942 calls many other threads, that is, the operations are heavily parallelized.
Further general information on the system's resource utilization can be gathered from the SAP HANA Cockpit Performance Monitor . It confirms the high CPU utilization close to 99%. The number of active threads and active SQL Executors confirm the large number of concurrently active threads.
In the above diagram the test first loads the input data with relatively low CPU resource consumption - up to the middle of the graph, and the query and update workload is then executed with high CPU resource consumption.
The view m_JOBEXECUTORS can provide some system-wide indication on CPU-related contention while being monitored during the runtime of the workload. The column FREE_WORKER_COUNT indicates if newly arriving work can be handled by free available worker threads. In this workload the FREE_WORKER_COUNT was mostly at zero which is consistent with the high CPU utilization from the performance monitor. High values of SYS_WAITING_JOB_COUNT and JOB_WAITING_JOB_COUNT can be caused by jobs waiting for other jobs to finish, for example due to high CPU utilization, or it could be related to general lock contention. High values in the QUEUED_WAITING_JOB_COUNT can be related to high CPU load resulting in jobs being queued for execution when job workers become available to process these jobs.
To drill down further into this analysis, the following statement can be used to detect NUMA nodes with high CPU utilization by counting the number of active threads. When at the same time other NUMA nodes keep having many fewer active threads, it can be an indication of a skewed distribution of load across the NUMA nodes that should be analyzed in more detail.
```
select numa_node_index, count(0) num_active_jobs from sys.m_service_threads where statement_hash <> '' and is_active = 'TRUE' group by numa_node_index order by numa_node_index;
```
With 44 logical cores per NUMA node in this system the results below indicate that NUMA node 3 has significantly more active threads than the other NUMA nodes. Still, all NUMA nodes seem to be highly utilized with more than 44 active threads per NUMA node. It should be noted that the fully meshed topology of NUMA nodes of this system allows for flexible stealing of jobs from highly utilized NUMA nodes. A highly skewed work distribution can be an indication of NUMA node contention.
The SAP HANA kernel profiler can be used to collect information about which code causes high CPU consumption. It requires expert knowledge of the SAP HANA code. Additionally, the files generated by the kernel profiler contain system-level statistics on the memory allocation of the system.
Complementing the analysis of CPU utilization and thread activity, we also analyze the table distribution across NUMA nodes . In general SAP HANA will try to evenly distribute tables or table partitions across the NUMA nodes in the system. However, multiple hot tables may be allocated to the same NUMA node, and this may result in high contention accessing this data only on this NUMA node. At the same time other NUMA nodes can have low or no load, which might lead to a low overall system usage.
For this analysis we execute the following query in the SQL Console of the SAP HANA Database Explorer to get an overview of the tables and table partitions and how their columns are assigned to NUMA nodes.
```
select table_name, column_name, part_id, string_agg(numa_node_index, ',') as numa_nodes from m_cs_columns where schema_name like '%TPC%'and numa_node_index >= 0 group by table_name, column_name, part_id order by table_name, column_name, part_id;
```
In our example we see that the columns of the largest table, LINEITEM, are all allocated on NUMA node 3.
Furthermore, the following query reports how much memory the columns of all column tables in the system consume per NUMA node. Adding the value for &lt;persistent_memory_size_in_total>= also includes tempfs and non-volatile memory:
```
select numa_node_index, sum(memory_size_in_total+persistent_memory_size_in_total) as memory_consumption_per_numa_node_in_bytes from m_cs_columns group by numa_node_index;
```
The result of this query is shown below where the first column is the NUMA node index and the second column the number of bytes consumed by column store tables on that NUMA node. On NUMA node 3 almost 17GB of memory are allocated for column tables, while on NUMA nodes 0 and 2 only roughly 8.5GB of memory are allocated. Also, on NUMA node 2 only 967MB of memory are allocated, that is much less than for the other NUMA nodes. A negative value for NUMA_NODE_INDEX (for example -1) has a technical interpretation that the target column is not loaded into memory.
The following query includes the table name and gives more insight:
```
select numa_node_index, table_name, sum(memory_size_in_total + persistent_memory_size_in_total) as memory_consumption_per_numa_node_in_bytes from m_cs_columns group by numa_node_index, table_name order by memory_consumption_per_numa_node_in_bytes desc;
```
This result further explains why NUMA node 1 has much less memory allocated than the other NUMA nodes: among the six largest tables only table PART is allocated on that NUMA node. In addition to table data, memory allocation and de-allocation for transient objects can also be a source of contention or skewed memory allocation across NUMA nodes which can be analyzed with advanced profiling tools, for example Intel PCM, Intel VTune, or the SAP HANA kernel profiler.
A final important step of the initial analysis is to check the SQL Plan Cache . In the SAP HANA Cockpit one can navigate to the monitoring of Statements and from there to the analysis of the SQL Plan Cache which shows the content of view m_SQL_PLAN_CACHE. When sorting the content of the SQL plan cache by the total execution time in descending order, one can get the statements with the highest contribution to CPU consumption in the system. It is probable that these statements are the most performance-critical statements because they are executed with very high frequency or every single statement execution is very expensive. The accessed tables
are also available from the SQL plan cache. In this example it is evident that table LINEITEM is used in all of the expensive statements:
It needs to be emphasized, that optimizing the SQL statements is a critical step before further optimizing the NUMA behavior. It may be the case that optimizing the SQL statements resolves all major performance issues. The section Statement Performance Analysis in this guide and the SAP Notes mentioned there provide further information.
## Related Information
Tuning for Better NUMA Behavior [page 103]
```
SAP HANA Cockpit - Threads SAP HANA Cockpit - The Performance Monitor SAP HANA Cockpit - Statements SAP HANA Cockpit - Monitor and Analyze Statements with SQL Plan Cache M_SERVICE_THREADS System View M_JOBEXECUTORS System View M_SQL_PLAN_CACHE System View Kernel Profiler
```