How to measure used/required amount of IQ_SYSTEM_TEMP of query ?

[former_member191072]

Hello ,

I'm using IQ16SPS08 , I want to know about how to measure(or know) used/required amount of IQ_SYSTEM_TEMP of query .

When I executed one of heavy query (many nested select statements) on my dev-IQ , IQ returned ”Insufficient temp" error . Then I added extra amount for temp  (maybe too big), It worked . But I want to know how many use temp this query for release disk amount .

Regards,

JT

[tayeb_hadjou · Accepted solution]

Hi Jim,

To see temp space estimated and used is in Root node in the query plans.

If the query was executed with option Query_Plan_After_Run enabled (Default in IQ 16), you will be able to see both Estimated and Actual. Eg.

Est. Temp Space Used (Mb) 5534.6

Act. Temp Space Used (Mb) 5719.6

If Query_Plan_After_Run is disabled, then you would see only estimated value.

For active connection, you can see the used temp space in system stored procedure sp_iqconnection result. Calclate the corresponding TempTableSpaceKB+TempWorkSpaceKB value.

Examples.

1- To see the temp space used for a specific connection. Filter result by Connection ID or username.

select ConnHandle,  IQconnID, Name , IQCmdType,LastIQCmdTime, ConnCreateTime, NodeAddr, (TempTableSpaceKB+TempWorkSpaceKB) as TempSpaceUsed from sp_iqconnection()

where IQconnID=<value>.

2- To get the 5 most tempspace consumers from CURRENT connections :

select Top 5 ConnHandle,  IQconnID, Name , IQCmdType,LastIQCmdTime, ConnCreateTime, NodeAddr, (TempTableSpaceKB+TempWorkSpaceKB) as TempSpaceUsed from sp_iqconnection()

order by TempSpaceUsed desc

Note that it possible to limit temp space per connection, if you prefer keep the server wide availability even though rejects large consumers (exceeding the limt).

To do so, set Max_Temp_Space_Per_Connection to the limit.

MAX_TEMP_SPACE_PER_CONNECTION Option

Hope this helps.

Regards,

Tayeb.