We installed R services in SQL 2016 SP2 and we are getting issues intermittently, can you please help us?
Hi SQL Lovers!
2 Weeks ago I was working in a very interesting case in SQL 2016. I received an email from one of my customers saying that they were having intermitent issues within their app that was executing some SP_Execute_External_script SP calls to the database.
SQL Server 2016 SP 2 CU 4 Enterprise Standalone + R services
- Issue from SQL Side:
We performed some normal checks between SQL and OS Side (Permissions, event viewer logs, R logs etc etc) But we didn’t find any event or error that can point to our issue. From R log we just found this:
StaleDirectoryCleaner starting directory deletions…
StaleDirectoryCleaner completed directory deletions with 1
SQLSatellite_LauncherCleanUp, dllmain.cpp, 381
WaitAll(2, 5000) completed with 258
cleanup wait failed with 258 and error 0
We also restarted the launchpad service but with no luck….. The biggest challenge was that sometimes the service responded fine and sometimes it showed the issue that I paste above (And this for me was absolutelly new…). From SQL Side we executed an extended events session with all R services counters but nothing appeared… From sys.dm_Exec_session_Wait_stats we just observed that the session was waiting for SATELLITE_SERVICE_SETUP wait which points that SQL Was waiting an answer from the R service itself. After that, since it was not a sql issue we decided to open a case with Microsoft. At the begining, we executed some traces in Proc mon and process explorer and we found that the BXLServer service sometimes started and sometimes it was blocked by something (The firewall was down)… So then we pointed to the antivirus and filtered drivers. We uninstall it but it didn’t fix our issue.
After our engineer reached the product team, they sent as the solution. There is a currently R bug within SQL 2016 (Fixed in SQL 2017). The issue is that R service will query the OS asking if HTTPS is enabled or not to get the latest repository version. Based on that call it will use https:// site…., and if it doesn’t respond, it sets to http:// site instead. In order to fix this we just modify the profile file which is located in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\etc\Rprofile.site . We changed this:
httpsSupported <- RevoUtils:::httpsSupported()
httpsSupported <- F
Then, we restarted the launchpad service and that fixed our issue!! Here you have two ways, or you modify the launchapd config file (important thing…. If you modify this file and apply a CU or SP that file will be replaced and you will have to modify it again) or you migrate directly to SQL 2017.
Hope this helps!