on 08-20-2015 11:27 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.