idle and rsource intensive connections

[SybDBA]

Dear ,

I am in a situation where I need a single procedure using sp_iqconnection(), sp_iqtransaction(), sp_iqcontext() which will generate the RESOURCE INTENSIVE queries and IDLE CONNECTIONS as well.

But I am unable to identify the parameters which i have to take into the consideration for the idle and resource intensive connections.

As I think that the parameters for both the conditions would be opposite to each other.

Please let me know these parameters.

Regards,

Pankaj

[markmumy]

Idle connections are easy.  The resource intensive connections are more difficult.  We don't currently track the CPU, memory, or disk utilization on a per connection basis.  It is tracked, somewhat, and output via an HTML query plan.  But that's not advisable to have on constantly in production.  Also, that info is only generated after election, not during.

You can look at the following via sp_iqconnection:

IQCmdType

The current command executing on the SAP Sybase IQ side, if any. The command type reflects commands defined at the implementation level of the engine. These commands consist of transaction commands, DDL and DML commands for data in the IQ store, internal IQ cursor commands, and special control commands such as OPEN and CLOSE, BACKUP DATABASE, RESTORE DATABASE, and others.

LowestIQCursorState

The IQ cursor state, if any. If multiple cursors exist on the connection, the state that appears is the lowest cursor state of all the cursors; that is, the furthest from completion. Cursor state reflects internal SAP Sybase IQ implementation detail and is subject to change in the future. For this version, cursor states are: NONE, INITIALIZED, PARSED, DESCRIBED, COSTED, PREPARED, EXECUTED, FETCHING, END_OF_DATA, CLOSED and COMPLETED. As suggested by the names, cursor state changes at the end of the operation. A state of PREPARED, for example, indicates that the cursor is executing.

If they are NONE, then the connection is idle.  You can also look at the following:

LastReqTime	The time at which the last request for the specified connection started.
LastIQCmdTime	The time the last IQ command started or completed on the IQ side of the SAP Sybase IQ engine on this connection.
LastIdle	The number of ticks between requests.

Those will give you an idea for how long the connection has been idle.  I caution, though, that just looking at the timers is not enough.  They only tell you the time since the last command was issued.  That time could be spent doing nothing or it could be spent waiting for the command to run.  Hence, the reason to look at the previous two columns.

For user resources, about the closest you could come is looking at the number of threads that a connection has.  It is not perfect, far from it.  But it's about all you have.  I would strongly urge you to open a case and ask for a feature request to track resource consumption, by connection, in real time.  We do this in ASE and HANA, so it stands to reason that we should also do it in IQ.

Mark

[SybDBA]

Hi Mark,

Good Morning...!!

As you suggested in the previous post that it is  not easy to get the resource intensive connections.

But i have made an effort to do it, Please guide me if anything wrong in it.

Here are those two procedures which I mentioned earlier.

for idle connections(sp_iqconnection_idle):

------------------------------------------------------------------------------------------------------------------------------------------

select distinct X.Connhandle,'User Name'=X.userid,'I.P. Address'=X.nodeaddr,X.IQthreads,TempworkspaceGB=X.TempworkspaceKB/1048576.0,'Last Command Time'=X.lastiqcmdtime,'Last Idle'=Datediff(minute,X.lastreqtime,getdate(*)) from sp_iqconnection() as X,sp_iqcontext() as Y where X.connhandle = Y.connhandle and Y.cmdline like '%NO COMMAND%' and

X.LowestIQCursorState not in ('INITIALIZED','FETCHING','PREPARED')

and

Y.cmdline is not null and Y.cmdline <> ' ' and X.userid not in( 'DBA','dbo') and Datediff(minute,X.lastreqtime,getdate(*)) > 30 order by TempworkspaceGB desc,x.IQthreads asc

--------------------------------------------------------------------------------------------------------------------------------------------

for resource intensive connections(sp_conn_det_pk) :

--------------------------------------------------------------------------------

select distinct

X.ConnHandle,

X.Userid,

X.State,

Y.IQthreads,

X.MainTableKBDr,

X.TempTableKBDr,

X.TempWorkSpaceKB,

Y.NodeAddr,

Z.CmdLine

from

sp_iqtransaction() X, sp_iqconnection() Y

, sp_iqcontext() Z

where

X.ConnHandle=Y.ConnHandle

AND

Y.ConnHandle=Z.ConnHandle 

AND

X.TempWorkSpaceKB > 1024       

and

X.State='ACTIVE'

and

Y.IQthreads > 10

and

Z.CmdLine not like 'NO COMMAND'

and

X.Userid not in('DBA','dbo')

order by Y.IQthreads desc

-----------------------------------------------------------------------------------------------------------------

These two procs i have created as per my understanding .

please suggest me changes. if required.

Regards,

Pankaj

[SybDBA]

????