Sql-server – How to grant more memory to run R processes in SQL Server 2016 R Services

memoryr-servicessql serversql-server-2016

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.

enter image description here

Best Answer

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;
Related Question