cancel
Showing results for 
Search instead for 
Did you mean: 

How to log "Approximate CPU time used" foreach connection

stefano_bolli
Explorer
0 Kudos

Hi all,

     I have a set of custom procedures and events to log some informations (connId, login timestamp,...) about user connections and disconnections.

This custom informations are useful to me to identify possible performance issues during specific timeframes.

My idea is, once a user is logging out, to collect the connection properties: ConnectedTime and ApproximateCPUTime using the following query.


select value from sa_conn_properties(@connId) where PropName = 'ApproximateCPUTime' --value is in sec

select value from sa_conn_properties(@connId) where PropName = 'ConnectedTime' --value is in sec

I have the following event triggered on "User Disconnection" where I try to collect the properties of interest.


CREATE EVENT "dba"."ev_MON_Logout" TYPE "Disconnect"

HANDLER

begin

            declare @user varchar(30)

            declare @connid integer

            declare @approx_cpu_time numeric(10)

            declare @conn_time numeric(10)

            select @connid=event_parameter('ConnectionID')

            select @user=event_parameter('User')

            select @approx_cpu_time=convert(numeric(10), value) from sa_conn_properties(@connid) where PropName = 'ApproximateCPUTime' -- in sec

            select @conn_time=convert(numeric(10), value)/1000 from sa_conn_properties(@connid) where PropName = 'ConnectedTime' -- in sec

          -- ...after collection they are stored in my own monitoring tables

end;

The problem is that I tried the above solution, but I have always the properties value equals to null.

Can be the problem the fact that when the event is triggered, the corresponding connection is already closed, so that only the @connid  and @user are returned and not the other properties?

How can I get ConnectedTime and ApproximateCPUTime automatically for each connection?

During my tests I tried also to use "sa_eng_properties" instead of "sa_conn_properties" in order to obtain an engine estimation at that moment instead of the connection one, but I obtain a strange result.


select * from sa_eng_properties() where PropName = 'ApproximateCPUTime'

PropNum,PropName,PropDescription,Value

385,ApproximateCPUTime,Approximate CPU time used,-6551729.6684032

Why do I obtain always the above value? ...and what does it mean?

Best regards,

Stefano

Accepted Solutions (0)

Answers (2)

Answers (2)

tayeb_hadjou
Advisor
Advisor
0 Kudos

Hi Stefano,

You can create an Event based on system stored procedure sp_iqconnection. It measures #threads of a connection in a given time. Threads are tightly dependant on cpus :

The column IQThreads contains the number of IQ threads currently assigned to the connection. This column can help you determine which connections are using the most cpu resources.

Note that a connection threads usage is limited by MAX_IQ_THREADS_PER_CONNECTION.

Regards,

Tayeb.

markmumy
Advisor
Advisor
0 Kudos

I am not so sure that these parameters apply to the IQ portion of the engine.  These are SQL Anywhere counters handled by the SA engine.  The connection time won't be an issue as all connections go through the SA engine.  The approximate cpu time is only viable on Linux and Windows.  Additionally, I don't believe that IQ is updating these counters so what you see may solely be for work in the SA engine.

Mark

stefano_bolli
Explorer
0 Kudos

Hi Mark,

        I understand your doubt, so I check again in SAP IQ 16.08 "reference building block" doc.

About "ApproximateCPUTime" properties there isn't any remarks about the fact that IQ doesn't update this counter....

4.8.3.1 List of connection properties

Property name = ApproximateCPUTime

Description = Returns an estimate of the amount of CPU time accumulated by a given connection, in seconds. The value returned may differ from the actual value by as much as 50%, although typical variations are in the 5-10% range. On multi-processor computers, each CPU (or hyperthread or core) accumulates time, so the sum of accumulated times for all connections may be greater than the elapsed time. This property is supported on Windows and Linux

However "sa_conn_properties" procedure is a catalog stored procedure, so it could be that it

refers only to time spent by catalog part of the engine (SQL Anywhere one).

I am working on a Windows box...do you have any idea about how to measure and automatically log the CPU time used by IQ connections?

Best regards,

Stefano