Resource Governor

Resource Governor is a very welcome feature of R Services (in-database), as it enables the Govern workload against the server with a simple dataset that is available with the accompanying code-database RevoTestDB, it needs to be restored first:

USE [master]
RESTORE DATABASE [RevoTestDB] FROM  DISK = N'C:\SQLServer2017MLServicesR\CH03\RevoTestDB.bak';
GO  

After the restore, we will see the loads against the database and how to govern resources:

USE RevoTestDB;
GO

-- TEST query
EXECUTE  sp_execute_external_script
                 @language = N'R'
                 ,@script = N'
            library(RevoScaleR)
f <- formula(as.numeric(ArrDelay) ~ as.numeric(DayOfWeek) + CRSDepTime)
s <- system.time(mod <- rxLinMod(formula = f, data = AirLine))
            OutputDataSet <-  data.frame(system_time = s[3]);'
                ,@input_data_1 = N'SELECT * FROM AirlineDemoSmall'
                ,@input_data_1_name = N'AirLine'
WITH RESULT SETS ((Elapsed_time FLOAT));

With this test query on my computer, the whole running time was 21 seconds and with elapsed time returned from R Engine, of 1.43 seconds.

Setting up the Resource Governer to tackle picks and spikes. To have execution operations running faster when needed or when running a higher amount of data, we need to configure the external resource pool and the resource pool to grant the resources:

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

ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

In the last step, a classification function must be created and reconfigured:

CREATE FUNCTION RG_Class_function()
RETURNS sysname
WITH schemabinding
AS  
BEGIN  
    IF program_name() in ('Microsoft R Host', 'RStudio') RETURN 'R_workgroup';  
    RETURN 'default'
    END;  
GO  

ALTER RESOURCE GOVERNOR WITH  (classifier_function = dbo.RG_Class_function);  
ALTER RESOURCE GOVERNOR reconfigure;  
GO

After that, I can run the same query again:

-- TEST 2 - performance normal; with governor enabled
EXECUTE  sp_execute_external_script
                 @language = N'R'
,@script = N'
library(RevoScaleR)
f <- formula(as.numeric(ArrDelay) ~ as.numeric(DayOfWeek) + CRSDepTime)
s <- system.time(mod <- rxLinMod(formula = f, data = AirLine))
OutputDataSet<-  data.frame(system_time = s[3]);'
,@input_data_1 = N'SELECT * FROM AirlineDemoSmall'
,@input_data_1_name = N'AirLine'
WITH RESULT SETS ((Elapsed_time FLOAT));  

In the end, the comparison in performance is obvious. In the case of my test environment, because I dedicated more CPU and RAM resources to executing R code, I get a running time of three seconds with an R elapsed time of 0.63 seconds. Results on your client might be different, but the change from the default resource governor settings to new settings should be very obvious.