Sybase IQ Performance

[former_member592597]

Hi! I'm having performance problems in my environment. I ran the query and got the access plan. I would like to know if the "Est. Temp. Used Space (Mb)" and "Act Temp. Used Space (Mb)" is a problem.

See below:

Est. Temp Space Used (Mb)77443593808.5

Act. Temp Space Used (Mb)63977.6

sp_iqstatus

' Temporary IQ Buffers:''146917, 18432Mb'

' Main IQ Blocks Used:''139799454 of 189939104, 73%=1066Gb, Max Block#: 216653583'

' Temporary IQ Blocks Used:''534178 of 15688661, 3%=4173Mb, Max Block#: 13577053'

' Main Reserved Blocks Available:''25600 of 25600, 100%=200Mb'

' Temporary Reserved Blocks Available:''39315 of 39315, 100%=307Mb'

' IQ Dynamic Memory:''Current: 37386mb, Max: 37897mb'

' Main IQ Buffers:''Used: 146912, Locked: 91'

' Temporary IQ Buffers:''Used: 92853, Locked: 994'

Tks.

[former_member592597]

Hi mark!

Any idea that can help me?

Tks

[former_member592597]

Hi Mark!

Thanks for the feedback.

This query returns less than a thousand records.

I am unable to attach the access plan. "This attachment is not allowed because the file type is invalid."

I uploaded the file to my google drive. Follow the link:

https://drive.google.com/drive/folders/136CNvwIJiG4uBpBfGvjxvqgfILNhWGZk?usp=sharing

Tks

[markmumy]

"performance problems" -- That's quite vague. Is it an issue with this query? All queries? How many cores are on the system? How much RAM? What does the IQ storage layout look like? Have you followed the IQ Sizing Guide for proper hardware sizing?

The difference in numbers, generally, makes sense. The estimated value comes from our statistics. If join columns, for instance, don't have indexes, we have to guess on how many result rows there will be. This is used to compute the temp space needed. If there are no statistics in the form of an HG or n-bit indexes, then the guess can be drastically wrong.

What should be more concerning is the actual temp space used. That's roughly 64GB of space. Does this query process or return a lot of data? Then this is normal.

Having the full post execution HTML query plan is the only way to know for sure how optimized, or not, the query is.

Can you share that?

Mark

[SybDBA]

this looks similar to the interview question being asked in general mostly in SAP ASE:

User is reporting slowness, how do you identify and resolve ? 🙂

And I'm like in the ocean of thoughts, on each point the person in-front will defend, network is doing good, I/Os doing good, we recently updated stats following reorg, etc.. still user reporting slowness... 😞

Then there is an ultimate answer I could say:

Slowness in the user's mind....lol.. 😄

Thanks

Pankaj