How to grant more memory to run R processes in SQL Server 2016 R Services

Question

I'm running a fresh install of SQL Server 2016 Standard Edition (13.0.5026.0) with R Services on Windows Server 2016 Standard. With all sorts of happiness I went to run an R script that completes within 15 mins on my lame desktop machine (8 GB RAM, 1 Intel proc @3.4 GHz) and found that it takes over two hours to run on my carefully configured server.

I'm wondering what else I need to do to get my R processes using more memory? My script spawns 12 R processes, which sit there using up to 460MB each even though there is plenty available and I've told them to use more. On my desktop in R Studio this same script happily grabs all available memory.

Here's the relevant details on my setup:

1. 72 GB RAM

2. 2 Intel Xeon procs @ 2.0GHz with 6 cores each

3. SQL Server max memory set to 32 GB (I've also had it at 57.6GB, no difference noted)

4. Though I don't think it matters since I don't have Enterprise Edition w/Resource Governor, I've set the internal and external pools to use 40 and 60 max memory percent, respectively.

5. I have my MAX DOP set at 8 per MS's recommendations, but I've also tried disabling parallel processing by setting this to 1 as I've heard that under certain conditions R's parallel processing can complete with SQL server's. No change in memory usage by R was seen, but certainly my CPU usage decreased significantly.

6. I've also added a line to the RLauncher config file (E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher) to use MEMORY_LIMIT_PERCENT=60.

7. I've confirmed that the SQL Server Service and SQL Server Launchpad services have the correct user rights in the local security policy to replace a process level token, manage memory, etc.

8. Yes, I can successfully run basic R scripts.

9. Yes, I've restarted the server after making my configuration changes.

10. I've ensured that the server is running in high performance mode and not power saving mode in both the BIOS and in Windows.

11. Ensured no errors in the event logs that point to an issue.

12. Ensured that the worker accounts MSSQLSERVER01-20 all have permissions to log on locally and are part of the SQLRUserGroup.

Answer 1

You'll want to use the resource governor. Here is a good blog.

Enable Resource Governor (do some research on this):

ALTER RESOURCE GOVERNOR RECONFIGURE;  

This will tell you how much % of your memory R can use.

SELECT * FROM sys.resource_governor_external_resource_pools

Sample query to change values from the link:

-- Default value
ALTER EXTERNAL RESOURCE POOL [default] 
WITH (AFFINITY CPU = AUTO)
GO

CREATE EXTERNAL RESOURCE POOL RService_Resource_Pool  
WITH (  
     MAX_CPU_PERCENT = 10  
    ,MAX_MEMORY_PERCENT = 5
);  

ALTER RESOURCE POOL [default] WITH (max_memory_percent = 60, max_cpu_percent=90);  
ALTER EXTERNAL RESOURCE POOL [default] WITH (max_memory_percent = 40, max_cpu_percent=10);  
ALTER RESOURCE GOVERNOR reconfigure;

Don't forget to run reconfigure again:

ALTER RESOURCE GOVERNOR RECONFIGURE;  

Answer 2

If you do not have Enterprise Edition and want SQL Server to allocate more memory for your R scripts then your only option would be to max out the physical RAM and that way SQL Server can allocate ~20% of that to R services. https://learn.microsoft.com/en-us/sql/advanced-analytics/r/sql-server-configuration-r-services?view=sql-server-2017

Are you consuming data in the R script from SQL Server? If so that could also be creating contention on memory allocation hence causing it to fail on the Server but work fine on your desktop.