[IQUG] IQUG Digest, Vol 59, Issue 16

Baker, Chris c.baker at sap.com
Wed Nov 15 14:00:36 MST 2017

The other option is to reduce IQ_SYSTEM_TEMP usage by checking the index advisor to see if you are missing HG indexes that perhaps should be added, and thereby avoid sorting and temp space usage.

Sorting may not be the whole issue, however.  Temp space is also used to materialize the result set fully, before returning the results to the client.  Try setting the option force_no_scroll_cursors = 'on' to avoid this happening, if possible (some applications still open the cursor as scrollable - e.g. MicroStrategy, so don't like this, so the default is 'off').  You can set the option globally as:
	set option public.force_no_scroll_coursors = 'on';
or on a per-user basis or temporary.

Also check that any join columns, etc, have the same datatypes to avoid implicit conversions.  If your FP indexes have also rolled over to flat-FP, then the sort may take more space in temp as well, depending on the query plan.

Your increase in temp usage due to the increased connections may point to insufficient HG indexing.  You should avoid changing the 'sort_pinnable_cache_percent' as all that will do is reduce performance further.  Look at your indexing first.

How much memory and cores are you running right now?  Are you even at the suggested minimum (which I think is now about 8-12 GB/core for IQ 16- Mark may correct me here) RAM requirements?

The other thing you may want to think of is to reduce the number of concurrent users (-gm) allowed to connect, especially if resources are in short supply due to increased usage, or 


Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033 | M +1 647-224-2033 | TF +1 866-716-8860
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2
c.baker at sap.com | www.sap.com
Conference tel: 1-866-312-7353,,9648565377#

-----Original Message-----
From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Steve Shen
Sent: Wednesday, November 15, 2017 2:37 PM
To: iqug at iqug.org
Subject: Re: [IQUG] IQUG Digest, Vol 59, Issue 16

Recently my users ran into frequent issues on " Insufficient buffers for 'Sort'" because more concurrent connections were using the production IQ instance at the peak time.

There are two solutions:
1. To increase the value of "-iqtc": It's not easy to find extra RAM!
2. To reduce the value of " Sort_Pinnable_Cache_Percent" from the default 20% to a lower percentage: The SAP/Sybase technical document strongly discouraged us from changing the value on "Sort_Pinnable_Cache_Percent".

I am wondering whether any of you had ever lowered the default values on " Sort_Pinnable_Cache_Percent" and resolved the issues for good.

If yes, what percentage did you lower to?  Please let me know.  Thank you.


Steve Shen

t: (646) 827-2102
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
IQUG mailing list
IQUG at iqug.org

More information about the IQUG mailing list