Sql-server – Removing sp_execute_external_script Start-Up Lag

pythonsql serversql-server-2017

On a freshly-restarted instance of SQL Server, the first time Python is executed using sp_execute_external_script, there's a lag before results are returned. After the initial Python script execution, future script executions executed shortly thereafter return much faster. However, if several minutes elapse before another script execution is attempted, the lag reappears.

I'm guessing that the lag is due to some aspect of the external script environment being started. The delay can't be due to query execution time as it is present even when the script invocation does not involve data access.

EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'print("hello")';

If I'm correct that the lag has to do with script environment start up, is there an option to configure that environment to be pre-started when the SQL Server instance starts so that the lag is removed?

Best Answer

As of writing, there isn't a feature (that I've ever heard of) and I imagine anything in this area would just be implemented as a performance fix as the external scripting feature matures.

Two options in the meantime would be:

  1. Run a dummy script at startup.
  2. Quit rebooting your server! If this is a testing environment or one being spun up dynamically, speed probably isn't a priority. If it is production, you you might need to ask yourself why you're rebooting with any amount of regularity.
Related Question