1

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

2

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;  
  • 1
    Please see my point #4 above. Resource Governor is not an available option. – Ebilly Aug 2 '18 at 18:35
  • oh dang, you're right. Hopefully this answer will help someone else who runs by so I'll leave it up. Let me think some more about what you can do. – Ali Razeghi Aug 2 '18 at 18:37
0

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://docs.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.

  • Since I've got 72GB of RAM in total on server and have SQL Server's max memory setting at 57,600MB, I would think that R would get access to 20% of 57,600 = 11,520 GB of RAM. However, my R processes never use more that 460MB. Yes, I am consuming data in my R script from SQL Server. But I still never see total server memory utilization over 12% so don't understand why more isn't being used. – Ebilly Aug 3 '18 at 18:15
  • Does R in Standard Edition get 20% of the total memory allocated to SQL Server or 20% of available server memory? – Ebilly Aug 3 '18 at 18:37
  • I’d need to check behaviour on my server but I would guess it gets 20% of the memory allocates to SQL Server, so like you say 11,520GB. However saying that, the screenshots shows your maxing out the CPU - could that be your actual issue? Perhaps batching it up into 2 lots of 6 processes could ease the pressure? – CasualFisher Aug 3 '18 at 19:51
  • Also, do you have access to a server running Developer Edition? That way you could test changes in the resource governor to see if that helps. – CasualFisher Aug 3 '18 at 19:52
  • Yes, I had wondered that too. I tried limiting this to using 5 processes and I still see the same behavior - 100% CPU usage and no R process using > 460 MB. I don't have access to developer edition immediately, but it looks like is certainly going to be needed to test this. – Ebilly Aug 3 '18 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.