Using Resource Governor with SQL Server 2016 R Services

SQL Server 2016 includes new advanced analitcs features like R script execution, these features are very useful, however, because those features may use a lot of memory and a lot and a lot of processor it could be the case that all the resources are used by the R Scripts execution.

To control the amount of resources that are allocated to the execution of R scripts you can use the feature known as resource governor. The following T-SQL gets the current configuration of the sql server for the execution and control of resources and resources.

- Check the amount of resources that are assigned by default 
SELECT   *   DESDE   sys. resource_governor_resource_pools   WHERE name =  Default   

The following statute verifies what resources are allocated to the pool external resources. Remember that the R scripts are executed as external scripts so we must use a dedicated pool for that execution

SELECT   *   DESDE   sys. resource_governor_external_resource_pools   
WHERE name =   Default   

The following T-SQL modifies the allocation of resources so that the maximum utilization is 70% of the total memory that the server has. In this way if we want to limit the amount of memory that is assigned we can do it the T-SQL shown below.

ALTER RESOURCE POOL "default" WITH (max_memory_percent =   70); 

If you want to limit the amount of memory that is used by the external scripting then we must modify it by using External Resource Pool. The following command modifies the allocation of resources so that the amount of memory assigned to the external scrtips does not exceed 50%.

ALTER RESOURCE POOL EXTERNAL "default" WITH (max_memory_percent =   50); 

Once we have made those changes, we must execute the following command for these changes to take effect.

ALTER RESOURCE GOVERNOR reconfigure; 

We also have the option to create a special configuration for the assignment of external scripting resources. For example the following command set a maximum of memory use of 30% for external scripts. We also have the option to create a group where we establish the use of resources that we establish by creating a workload group. 

CREATE EXTERNAL RESOURCE POOL WITH pool_scripts_externos (max_memory_percent =   30); 

CREATE WORKLOAD GROUP WITH grupo_scripts_externos (Importance = medium) 
USING "default" EXTERNAL "pool_scripts_externos"; 

ALTER RESOURCE GOVERNOR WITH (classifier_function = NULL);    
ALTER RESOURCE GOVERNOR reconfigure;    

Next we are going to create a classification function, that function will be used to identify the programs that execute R code in this way we can filter those requests that come from the applications such like R Studio we assign the corresponding resources.

USE master 
GO 
CREATE   FUNCTION   aplicaciones_R_Externas () 
RETURNS sysname 
WITH schemabinding 
ACE    
BEGIN    
IF program_name () in ( 'RStudio') RETURN 'grupo_scripts_externos'; 
RETURN 'default'    
END; 
GO

Once the function has been created we have to reconfigure the resource group and assign the classification function that will be used to identify the calls that are being made to the SQL Server instance and will be assigned the groups of Resources corresponding to external applications running R languages. The command to apply the changes is shown below: 

ALTER RESOURCE GOVERNOR WITH (classifier_function =   dbo.aplicaciones_R_Externas); 
ALTER RESOURCE GOVERNOR reconfigure; 
go 

If we want to verify that the changes have been applied correctly we have to execute the following commands: 

SELECT   *   DESDE   sys. resource_governor_workload_groups; 

SELECT   *   DESDE   sys. resource_governor_external_resource_pools; 

In summary in this article we show you how we can control the amount of resources that is allocated to the execution of external scripts and we can control the amount of memory also the amount of semen that will use this type of execution of external scripts.