topic Re: Failure to create Nested Loop Join memorylimit 2048MB in SAS Viya

Failure to create Nested Loop Join memorylimit 2048MB

thedcoii — Tue, 11 Jul 2023 19:40:04 GMT

In our latest update one of our decisions is maxing out our memory limit and it looks related to sql we have in the process. Should we update this 'working memorylimit' or is there another way to analyze how many resources we are using on the system and optimize our process?

Thanks

ERROR App.TableServices.DS2.Runtime.Log Foursquare_Autodecision1_24 execute - [HY000] Error failed to create Nested Loop Join thread, system resources have been exceeded. The working memorylimit is 2048MB. (0x81bfc201).
2023-07-11T11:54:31,640 [00000008] ERROR App.TableServices.DS2.Runtime.Log Foursquare_Autodecision1_24 execute - Line 175: EXECUTE failed for SQL statement:

Re: Failure to create Nested Loop Join memorylimit 2048MB

Reeza — Tue, 11 Jul 2023 20:46:12 GMT

2GB seems like a low limit, how big are the datasets you're working with?

Re: Failure to create Nested Loop Join memorylimit 2048MB

thedcoii — Tue, 11 Jul 2023 20:54:03 GMT

doesn't seem large but after a lot of versions with a lot of calls maybe we are breaching this. I've read that 2GB maybe a bit small but I have been looking around and have not found how to increase this limit when sas starts. Do you know where this configuration setting is @Reeza ?

Re: Failure to create Nested Loop Join memorylimit 2048MB

Patrick — Tue, 11 Jul 2023 21:24:43 GMT

2 GB sounds like the installation default (at least it is for SAS 9.4). There is normally a limit set for named users so they can't take down the environment with a single rogue process.

Increasing the max memsize limit for users is possible and often done. I believe how/where exactly to change it will depend on your Viya version.

There is a similar discussion with a link to the SAS docu here. You would need to find something similar in the docu for your exact Viya version.