How to check which query is consuming high temp cache on Sybase IQ

[former_member755915]

Hello All,

We need to know which query is consuming how much of temp cache on sybase IQ. Is there any command or store procedure to get the information?

[raymond_lackey]

Might point you in the right direction...

View Active Connections / Connection details

select a.ConnHandle, a.IQconnID, a.Userid, a.Name, a.ConnCreateTime, a.LastReqTime, a.ReqType, a.LastIQCmdTime, a.IQCmdType, a.TempTableSpaceKB, a.TempWorkSpaceKB, a.IQCursors, a.IQthreads, CONNECTION_PROPERTY('NodeAddress', a.ConnHandle) as NodeAddress, b.CmdLine, b.ConnOrCursor, a.INCConnSuspended, c.ConnHandle from dbo.sp_iqconnection() a INNER JOIN dbo.sp_iqcontext() b ON a.ConnHandle = b.ConnHandle LEFT OUTER JOIN dbo.sp_iqmpxsuspendedconninfo() c on a.ConnHandle = c.ConnHandle order by ConnCreateTime

To view Cache Size / Cache Reads / Cache Statistics > Temporary cache

select stat_name, stat_value, now() from dbo.sp_iqstatistics() where stat_name = 'TempCacheCurrentSize' or stat_name = 'TempCacheFinds' or stat_name = 'TempCacheHits' or stat_name = 'TempCachePagesPinned' or stat_name = 'TempCachePagesPinnedPercentage' or stat_name = 'TempCachePagesInUsePercentage' or stat_name = 'TempCachePagesDirtyPercentage' order by stat_name

[former_member755915]

Hi Raymond,

Thank you.

Actually we are facing "insufficient buffer for sort" issue from quiet long time. We tried all the methods to mitigate it but no luck. We need to find out the bad queries which application team are firing onto the database. We have 600 Gb of RAM on the server for 30 TB database, still we are facing the issue.

we have identified couple of queries which are consuming 200 to 300 GB of temp cache, but we are falling to prove only those queries are consuming it.