SQL 2019 Parallel R Script calling rxExec Hangs

%3CLINGO-SUB%20id%3D%22lingo-sub-1123567%22%20slang%3D%22en-US%22%3ESQL%202019%20Parallel%20R%20Script%20calling%20rxExec%20Hangs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1123567%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20calling%20the%20Attached%20R%20Script%20utilizing%20the%20rxExec%20function%20on%20SQL%20Server%202019%20RTM%2C%20the%20script%20hangs%20and%20does%20not%20complete%20execution.%26nbsp%3B%20The%20same%20script%20succeeds%20on%20SQL%20Server%202016%20CU11.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20breaking%20change%20to%20rxExec%20for%20SQL%20Server%202019%3F%26nbsp%3B%20If%20so%20what%20is%20the%20preferred%20way%20to%20work%20around%20this%20issue.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E--Server%201%201%20Socket%206%20cores%20SQL%20Server%202019%20Process%20Hangs%3CBR%20%2F%3E--Server%202%204%20Sockets%204%20cores%20%24numCoresToUse%20%3D%204%20SQL%20Server%202016%20Execution%20Time%2000%3A33%3C%2FP%3E%3CP%3EDECLARE%20%40rscript%20nvarchar(max)%3CBR%20%2F%3ESET%20%40rscript%20%3D%20N'%3CBR%20%2F%3EplayDice%20%26lt%3B-%20function()%3CBR%20%2F%3E%7B%3CBR%20%2F%3ESys.sleep(1)%3CBR%20%2F%3Eresult%20%26lt%3B-%20NULL%3CBR%20%2F%3Epoint%20%26lt%3B-%20NULL%3CBR%20%2F%3Ecount%20%26lt%3B-%201%3CBR%20%2F%3Ewhile%20(is.null(result))%3CBR%20%2F%3E%7B%3CBR%20%2F%3Eroll%20%26lt%3B-%20sum(sample(6%2C%202%2C%20replace%3DTRUE))%3CBR%20%2F%3Eif%20(is.null(point))%3CBR%20%2F%3E%7B%3CBR%20%2F%3Epoint%20%26lt%3B-%20roll%3CBR%20%2F%3E%7D%3CBR%20%2F%3Eif%20(count%20%3D%3D%201%20%26amp%3B%26amp%3B%20(roll%20%3D%3D%207%20%7C%7C%20roll%20%3D%3D%2011))%3CBR%20%2F%3E%7B%3CBR%20%2F%3Eresult%20%26lt%3B-%20%22Win%22%3CBR%20%2F%3E%7D%3CBR%20%2F%3Eelse%20if%20(count%20%3D%3D%201%20%26amp%3B%26amp%3B%20(roll%20%3D%3D%202%20%7C%7C%20roll%20%3D%3D%203%20%7C%7C%20roll%20%3D%3D%2012))%3CBR%20%2F%3E%7B%3CBR%20%2F%3Eresult%20%26lt%3B-%20%22Loss%22%3CBR%20%2F%3E%7D%3CBR%20%2F%3Eelse%20if%20(count%20%26gt%3B%201%20%26amp%3B%26amp%3B%20roll%20%3D%3D%207%20)%3CBR%20%2F%3E%7B%3CBR%20%2F%3Eresult%20%26lt%3B-%20%22Loss%22%3CBR%20%2F%3E%7D%3CBR%20%2F%3Eelse%20if%20(count%20%26gt%3B%201%20%26amp%3B%26amp%3B%20point%20%3D%3D%20roll)%3CBR%20%2F%3E%7B%3CBR%20%2F%3Eresult%20%26lt%3B-%20%22Win%22%3CBR%20%2F%3E%7D%3CBR%20%2F%3Eelse%3CBR%20%2F%3E%7B%3CBR%20%2F%3Ecount%20%26lt%3B-%20count%20%2B%201%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3CBR%20%2F%3Eresult%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3ErxSetComputeContext(RxLocalParallel())%3C%2FP%3E%3CP%3Eprint(rxOptions())%3CBR%20%2F%3Ez%20%26lt%3B-%20rxExec(playDice%2C%20timesToRun%3D100)%3C%2FP%3E%3CP%3E%23library(doParallel)%3CBR%20%2F%3E%23cl%20%26lt%3B-%20makeCluster(detectCores())%3CBR%20%2F%3E%23print(detectCores())%3CBR%20%2F%3E%23registerDoParallel(cl)%3C%2FP%3E%3CP%3E%23z%20%26lt%3B-%20foreach(i%3D1%3A100)%20%25dopar%25%20playDice()%3C%2FP%3E%3CP%3E%23stopCluster(cl)%3C%2FP%3E%3CP%3EResultDf%20%26lt%3B-%20data.frame(matrix(unlist(z)%2C%20nrow%3Dlength(z)%2C%20byrow%3DT))%3CBR%20%2F%3E'%3CBR%20%2F%3EEXECUTE%20sp_execute_external_script%3CBR%20%2F%3E%40language%20%3D%20N'R'%3CBR%20%2F%3E%2C%40script%20%3D%20%40rscript%3CBR%20%2F%3E%2C%40parallel%20%3D%200%3CBR%20%2F%3E%2C%40output_data_1_name%20%3D%20N'ResultDf'%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor

When calling the Attached R Script utilizing the rxExec function on SQL Server 2019 RTM, the script hangs and does not complete execution.  The same script succeeds on SQL Server 2016 CU11.

 

Is there a breaking change to rxExec for SQL Server 2019?  If so what is the preferred way to work around this issue.


--Server 1 1 Socket 6 cores SQL Server 2019 Process Hangs
--Server 2 4 Sockets 4 cores $numCoresToUse = 4 SQL Server 2016 Execution Time 00:33

DECLARE @rscript nvarchar(max)
SET @rscript = N'
playDice <- function()
{
Sys.sleep(1)
result <- NULL
point <- NULL
count <- 1
while (is.null(result))
{
roll <- sum(sample(6, 2, replace=TRUE))
if (is.null(point))
{
point <- roll
}
if (count == 1 && (roll == 7 || roll == 11))
{
result <- "Win"
}
else if (count == 1 && (roll == 2 || roll == 3 || roll == 12))
{
result <- "Loss"
}
else if (count > 1 && roll == 7 )
{
result <- "Loss"
}
else if (count > 1 && point == roll)
{
result <- "Win"
}
else
{
count <- count + 1
}
}
result
}

rxSetComputeContext(RxLocalParallel())

print(rxOptions())
z <- rxExec(playDice, timesToRun=100)

#library(doParallel)
#cl <- makeCluster(detectCores())
#print(detectCores())
#registerDoParallel(cl)

#z <- foreach(i=1:100) %dopar% playDice()

#stopCluster(cl)

ResultDf <- data.frame(matrix(unlist(z), nrow=length(z), byrow=T))
'
EXECUTE sp_execute_external_script
@language = N'R'
,@script = @rscript
,@parallel = 0
,@output_data_1_name = N'ResultDf'



 

0 Replies