In previous blogs, we’ve looked at the potential for integrating the statistical language R into soon-to-be-released SQL Server 2016. Developers and analysts are excited about the new possibilities – the response of administrators is considerably more restrained. Administrators are a conservative lot – it’s part of their job description. When learning of a new feature they must ask “Might this have adverse effects on my system? Might R compete with SQL Server for critical resources like memory?”
We need to take a look at how we can configure R so that we can enjoy the new features while carefully controlling any impact on the normal functioning of an SQL Server system. To be precise, we will configure the settings not for R directly, but for the SQL Server R launcher service. We will therefore not look in the folder where R is located, but in the \binn folder of SQL Server itself.
In that folder we see the RLauncher.dll library and its corresponding config file. Of course, it is always wise to make a copy of a config file before modifying it to have a known good copy to revert to if things go awry.
There is a detailed list of the potential configuration file settings that can be used as a reference. We are interested in MEMORY_LIMIT_PERCENT. If no value is set for MEMORY_LIMIT_PERCENT, R is allowed to take up to 20% of the total physical memory on the system. This is the default.
Altering this default setting requires only adding the new value to the config file.
The memory limit applies to the sum of all R sessions running concurrently, not to a single R sessions. In practice, even 20% of total memory may prove too small for some data mining tasks. If the memory allocated to R cannot be increased then R may have to offload tasks to R compute contexts running on other machines.
Since R is an external resource rather than a part of SQL Server itself, the resource governor as it was in previous versions of SQL Server would be of no value. In SQL Server 2016, a new resource pool type, the external pool, has been added. The external pool applies to R processes running in SQL Server’s space and whatever additional external scripting services Microsoft may add in the future. Administrators can create external resource pools in addition to the default to control more precisely how R resources are allocated to workload groups defined in the resource governor.
The default value allowing a maximum of 20% of physical RAM for R will almost certainly have to be reconfigured. For systems processing larger analytic tasks, the 20% value is likely to be too small. For other systems, giving up 20% of memory to R may put an unacceptable strain on SQL Server’s primary tasks. If there is not enough memory to service all requests at peak time, the resource governor can be used to control how memory available to R is divided among different task requests.