Analyzing higher CPU usage in HANA

[rajarajeswari_kaliyaperum]

Seeing higher CPU usage in HANA ?

When ever CPU usage is high in HANA the main reason could be one of the below.

1.A table has been created as row store by mistake rather than column store and HANA is suffering because of that

2.Too many threads are running in the affected node and it has almost depleted most of the resources

So how to analyze in such cases.

Though SAP is already asking us to capture kernel profiler trace during such issues, i also would try to find out which node is affected,which user is using maximum number of threads, which connection is consuming more threads,how many threads are used in all the nodes and so on. Below are the queries that i suggest to troubleshoot such issues.

a.How to find the distinct number of active connection for each node in a scale out host.

WITH CTE AS (

select distinct CONNECTION_ID,USER_NAME from M_SERVICE_THREADS where THREAD_STATE !='Inactive' and SERVICE_NAME='indexserver' and HOST='<ServerName>' group by CONNECTION_ID,USER_NAME order by USER_NAME

)

SELECT distinct user_name,count(*) as ActiveNumberofCONNECTIONID FROM CTE group by USER_NAME order by ActiveNumberofCONNECTIONID desc

b.How to find the list if users using active threads in HANA

select distinct USER_NAME,count(*) as NumberOfThreads from M_SERVICE_THREADS where THREAD_STATE !='Inactive' and SERVICE_NAME='indexserver' group by uSER_NAME  order by NumberOfThreads desc

To find specific to one host:

select distinct USER_NAME,count(*) as n from M_SERVICE_THREADS where HOST='<host name scale out>' and THREAD_STATE !='Inactive' and SERVICE_NAME='indexserver' group by uSER_NAME  order by n desc

c.How to find the Number of active threads per host

select HOST,count(*) as NumberOfThreadActive from M_SERVICE_THREADS where THREAD_STATE !='Inactive' and SERVICE_NAME='indexserver' group by HOST;

 

 

d.How to find the number of threads associated with each of the connection ID in HANA DB.

select distinct CONNECTION_ID,count(*) as NumberOfThreadsAndSubthreads from M_SERVICE_THREADS where THREAD_STATE !='Inactive' and SERVICE_NAME='indexserver' group by CONNECTION_ID  order by NumberOfThreadsAndSubthreads desc;

 

e.How to find the connection ID and associated owner of that connection id.

select distinct CONNECTION_ID,USER_NAME from M_SERVICE_THREADS where THREAD_STATE !='Inactive' and SERVICE_NAME='indexserver' group by CONNECTION_ID,USER_NAME

f.How to find the list of threads associated with all the application connected to HANA DB.

select distinct Application_name, count(*) from M_SERVICE_THREADS where THREAD_STATE !='Inactive' and SERVICE_NAME='indexserver' group by Application_name

 

Thanks for reading!
Follow for more such posts!
Like and leave a comment or suggestion if any!