<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/resource-governor-and-external-resource-pool-for-microsoft-r-services/"></div></div><div><a data-url="https://www.r-bloggers.com/resource-governor-and-external-resource-pool-for-microsoft-r-services/" data-counturl="https://www.r-bloggers.com/resource-governor-and-external-resource-pool-for-microsoft-r-services/" data-count="horizontal" data-text="Resource governor and external resource pool for Microsoft R Services" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2016/08/18/resource-governor-and-external-resource-pool-for-microsoft-r-services/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Setting external resource pool for execution of R commands using sp_execute_external_script has proven extremely useful, especially in cases where you have other workers present, when you don’t want to overdo on data analysis and get to much resources from others (especially when running data analysis in production environment) or when you know that your data analysis will require maximum CPU and memory for a specific period. In such cases using and defining <a target="_blank" href="https://msdn.microsoft.com/en-us/library/mt694065.aspx#">external resource pool</a> is not only useful but highly recommended.</p>
<p><a target="_blank" href="https://msdn.microsoft.com/en-us/library/bb933866.aspx">Resource governor</a> is a feature that enables you to manage SQL Server workload and system resource consumption their limits. Limits can be configures for any workload in terms of CPU, Memory and I/O consumption. Where you have many different workloads on the same SQL Server, resource Governor helps allocate requested resources.</p>
<p>By default, two pools are defined – default and internal for system resources, and only default for system external resources.</p>
<p><img alt="2016-08-18 22_19_51-SQLQuery4.sql - SICN-KASTRUN.RevoTestDB (SPAR_si01017988 (54))_ - Microsoft SQL" class="wp-image-2045" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-22_19_51-sqlquery4-sql-sicn-kastrun-revotestdb-spar_si01017988-54_-microsoft-sql.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Resource governor has <span>resource pools</span>, <span>workload groups</span> that are groups of similar load within same <span>classification</span> criteria. When defining external resource pool we will be defining the limitations for CPU and/or memory consumption for external resources. And R Services (using external procedure sp_execute_external_script) falls under external resources. According to MSDN, with external resource pool for R services will govern “<code>rterm.exe</code>, <code>BxlServer.exe</code>, and other processes spawned by them (quoted: <a target="_blank" href="https://msdn.microsoft.com/en-us/library/mt694065.aspx#">link</a>)”</p>
<p>First of all, you need to have resource governor enabled:</p>
<pre>-- Enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO</pre>
<p>Once you have this, let us set the query for test so we can measure and see how the resource governor will behave.</p>
<p>I will be using <a target="_blank" href="https://msdn.microsoft.com/en-us/library/mt637368.aspx">RevoScaleR</a> sample data, that come with each R Service installation of SQL Server 2016. This sample data should be available on following location:</p>
<p>C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\SampleData</p>
<p><img alt="2016-08-18 22_41_08-SampleData" class="wp-image-2077" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-22_41_08-sampledata.png?resize=456%2C196&ssl=1" data-recalc-dims="1"></p>
<p>And I will be using <em>AirlineDemoSmall.csv  – </em>CSV dataset, roughly 14MB size. Please note, that path to the SampleData might differ, based on your SQLServer 2016 installation folder.</p>
<p>In your database create and import data to your SQL Server table:</p>
<pre>CREATE TABLE AirlineDemoSmall(
     ArrDelay varchar(100) NOT NULL
    ,CRSDepTime float NOT NULL
    ,[DayOfWeek] varchar(12) NOT NULL  
)
GO

-- this file should be at your location! so no need to download it
BULK INSERT AirlineDemoSmall
FROM 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\
R_SERVICES\library\RevoScaleR\SampleData\AirlineDemoSmall.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2 -- Skip header
)</pre>
<h5>*Please note; both statements (CREATE TABLE and BULK INSERT) are part of RevoScaleR ImportAirlineDB.sql file. I am just reusing a section of this code. Whole file will be added to Github.</h5>
<p>Once you have your data imported, we will run a simple linear regression using RevoScale library.</p>
<pre>EXECUTE  sp_execute_external_script
                 @language = N'R'
                ,@script = N'
            library(RevoScaleR)
            f &lt;- formula(as.numeric(ArrDelay) ~ as.numeric(DayOfWeek) + CRSDepTime)
            s &lt;- system.time(mod &lt;- rxLinMod(formula = f, data = AirLine))
            OutputDataSet &lt;-  data.frame(system_time = s[3]);'
                ,@input_data_1 = N'SELECT * FROM AirlineDemoSmall'
                ,@input_data_1_name = N'AirLine'
-- WITH RESULT SETS UNDEFINED
WITH RESULT SETS 
            ((
                 Elapsed_time FLOAT
            ));</pre>
<p> </p>
<p>So I am only exporting R system time function and only Total elapsed time (hence 3 attribute from vector result of system.time function). Usual representation of this function is following format: user time, system time and elapsed time.</p>
<p><img alt="2016-08-18 22_51_34-RStudio" class="wp-image-2101" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-22_51_34-rstudio.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>And will only take elapsed time as a measure and comparison between resource pool enabled and disabled. Results from our control environment (before setting the resource pool):</p>
<p><img alt="2016-08-18 22_53_45-SQLQuery4.sql - SICN-KASTRUN.RevoTestDB (SPAR_si01017988 (54))_ - Microsoft SQL" class="wp-image-2106" src="https://i2.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-22_53_45-sqlquery4-sql-sicn-kastrun-revotestdb-spar_si01017988-54_-microsoft-sql.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>and computation time is 0.058 of a second where as elapsed time  – value returned from our sp_execute_external_script – is 0,63 of a second.</p>
<p><img alt="2016-08-18 22_58_47-SQLQuery4.sql - SICN-KASTRUN.RevoTestDB (SPAR_si01017988 (54))_ - Microsoft SQL" class="wp-image-2116" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-22_58_47-sqlquery4-sql-sicn-kastrun-revotestdb-spar_si01017988-54_-microsoft-sql.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Now we will create a new pool and limit the CPU and memory available for such workloads:</p>
<pre>-- 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;</pre>
<p> </p>
<p>New pool – called: RService_Resource_Pool is created  with new values set.</p>
<p><img alt="2016-08-18 23_09_23-Resource_governor_Microsoft_R_services.sql - SICN-KASTRUN.RevoTestDB (SPAR_si010" class="wp-image-2130" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-23_09_23-resource_governor_microsoft_r_services-sql-sicn-kastrun-revotestdb-spar_si010.png?resize=456%2C46&ssl=1" data-recalc-dims="1"></p>
<p>And obligatory reconfiguration:</p>
<pre>ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO</pre>
<p>This configuration can also be found using this system configuration table:</p>
<pre>-- Check configuration
SELECT * FROM sys.resource_governor_external_resource_pools</pre>
<p><img alt="2016-08-18 23_36_54-Resource_governor_Microsoft_R_services.sql - SICN-KASTRUN.RevoTestDB (SPAR_si010" class="wp-image-2146" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-23_36_54-resource_governor_microsoft_r_services-sql-sicn-kastrun-revotestdb-spar_si010.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Please note that only <a target="_blank" href="https://msdn.microsoft.com/en-us/library/mt591988.aspx">20 workers</a> are dedicated to be used for R external processes.</p>
<p><img alt="Capture" class="wp-image-2150" src="https://i2.wp.com/tomaztsql.files.wordpress.com/2016/08/capture2.png?resize=456%2C247&ssl=1" data-recalc-dims="1"></p>
<p>With each run of RevoScaleR library functions an increment will appear for DMV statistics on external script execution. Also, only RevoScaleR functions are counted here. Any other function (to my knowledge) is not counted here (as of SQL Server 2016 version).</p>
<pre>SELECT * FROM sys.dm_external_script_execution_stats</pre>
<p><img alt="2016-08-18 23_34_01-Resource_governor_Microsoft_R_services.sql - SICN-KASTRUN.RevoTestDB (SPAR_si010" class="wp-image-2140" src="https://i2.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-18-23_34_01-resource_governor_microsoft_r_services-sql-sicn-kastrun-revotestdb-spar_si010.png?resize=312%2C472&ssl=1" data-recalc-dims="1"></p>
<p>Once you have this, we need to set the Classification function as well:</p>
<pre>CREATE WORKLOAD GROUP R_workgroup WITH (importance = medium) USING &quot;default&quot;, 
EXTERNAL &quot;RService_Resource_Pool&quot;;  

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

USE master  
GO  
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</pre>
<p> </p>
<p>On <a target="_blank" href="https://msdn.microsoft.com/en-us/library/mt703708.aspx">MSDN</a> you will find a post on setting up resource governor for R Services and due to security reasons, “<em>However, direct management of the Launchpad service by using Resource Governor is not supported.</em>” So, this practically means that you can also create a login with one of twenty R User Names (MSSQLServer01 … MSSQLServer20) in group of SQLRUserGroup in order for Resource Governor to work.</p>
<p>So, much coding needed at this point. I will be using User MSSQLSERVER01 and create all the necessary things: logins, permissions, etc.After setting up MSSQLSERVER01 windows user and using EXECUTE AS LOGIN command prior to running sp_execute_external_script.</p>
<p>Running the command:</p>
<p>SELECT * FROM sys.resource_governor_workload_groups;</p>
<p>you will now  see all the workload groups.</p>
<p>And now, finally we can rerun the same procedure:</p>
<pre>-- We will run same query

EXECUTE  sp_execute_external_script
                 @language = N'R'
                ,@script = N'
   library(RevoScaleR)
   f &lt;- formula(as.numeric(ArrDelay) ~ as.numeric(DayOfWeek) + CRSDepTime)
    s &lt;- system.time(mod &lt;- rxLinMod(formula = f, data = AirLine))
                            OutputDataSet &lt;-  data.frame(system_time = s[3]);'
                ,@input_data_1 = N'SELECT * FROM AirlineDemoSmall'
                ,@input_data_1_name = N'AirLine'
-- WITH RESULT SETS UNDEFINED
WITH RESULT SETS 
            ((
                 Elapsed_time FLOAT
            ));</pre>
<p> </p>
<p>And CPU consumption will stay within the limits:</p>
<p><img alt="2016-08-19 00_40_57-Task Manager" class="wp-image-2176" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-19-00_40_57-task-manager.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>and the elapsed time will be higher due to lower CPU available.</p>
<p><img alt="2016-08-19 00_41_54-Resource_governor_Microsoft_R_services.sql - SICN-KASTRUN.RevoTestDB (SPAR_si010" class="wp-image-2180" src="https://i2.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-19-00_41_54-resource_governor_microsoft_r_services-sql-sicn-kastrun-revotestdb-spar_si010.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Happy R-Tsqling!<img height="16" alt=":-)" width="16" class="wp-smiley" src="https://s1.wp.com/wp-content/mu-plugins/wpcom-smileys/simple-smile.svg" draggable="false"></p>
<p>Code Is available at <a target="_blank" href="https://github.com/tomaztk/Resource_Governor_external_resource_pool_Microsoft_R_Services">Github</a>.</p>
<p> </p>
<p><span>NOTE!</span></p>
<p><span>CPU consumption will rise after the execution of external execution command due to the worker MSSQLSERVER01 cleaning the session files and log files!</span></p>
<p><img alt="2016-08-19 00_44_26-MSSQLSERVER01" class="wp-image-2186" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/08/2016-08-19-00_44_26-mssqlserver01.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Since log and session files are part of Launchpad service, Resource Governor will not be able to limit the CPU, Memory of I/O consumption.</p>
<p> </p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/1913/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/1913/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/resource-governor-and-external-resource-pool-for-microsoft-r-services/"></div></div><div><a data-url="https://www.r-bloggers.com/resource-governor-and-external-resource-pool-for-microsoft-r-services/" data-counturl="https://www.r-bloggers.com/resource-governor-and-external-resource-pool-for-microsoft-r-services/" data-count="vertical" data-text="Resource governor and external resource pool for Microsoft R Services" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2016/08/18/resource-governor-and-external-resource-pool-for-microsoft-r-services/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/uU3BncCpmic"></a></div>