Prevent Machine Learning Services (R) on SQL Server taking up high levels of CPU

Question

I have had Machine Learning Service installed on SQL Server to be able to run R scripts (called from Stored Procedures).

There are now several of these Stored Procedures as part of a script to populate date into Power BI. Because we run these stored procedures from Power BI Service refresh, it appears that they are running in parallel and consuming large amounts of CPU whilst they run. This is slowing down the server for other (more critical) processes. In Task Manager there are often as many as 10 instances of RTerm.exe running at the same time when the refresh is invoked.

When a single one of these stored procedures is run, sometimes one instance, but often multiple instances of RTerm.exe start running on the server.

Is there a way to limit ML Services / R so that it has access to only a proportion of the CPUs or resources available and we can ensure that it does not consume more resources than a prescribed maximum? Are there any other strategies that people have deployed to resolve this issue?

Answer 1

The server manages a pool of RTerm.exe instances in order to handle multiple requests, and the default is to increase that pool by 5 extra instances at a time when more are needed (leading to 6 spinning up at once when executing the first external script).

This can be changed by setting a value for PROCESS_POOL_SQLSATELLITE_GROWTH in rlauncher.config in the MSSQL/Binn folder and then restarting the SQL Server launchpad service in SQL Server Configuration Manager.

For my case, we had scripts failing due to memory allocation issues. Turned out those 5 extra instances were eating up most of the default memory budget (20% of physical memory) by themselves, not leaving enough for relatively simple scripts to complete. Setting PROCESS_POOL_SQLSATELLITE_GROWTH=1 has resolved the issue.

Found this information from this source: https://nielsberglund.com/2017/04/23/microsoft-sql-server-r-services---internals-iv/