4

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?

Improve this question
1
  • As a solution/work-around been found for this issue? I am experiencing the same issue using the Python feature.
    – Edmund
    Commented Sep 5, 2019 at 18:52

1 Answer 1

Reset to default
1

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.
Improve this answer
3
  • Thanks, @LowlyDBA. What makes this challenging is that the start-up lag reappears if a script isn't executed within a certain amount of time (not very long: the timeout is several minutes or less). It looks like the script execution environment hibernates/shuts down after so much inactivity. Unfortunately, a dummy script at startup doesn't address the idle timeout hibernation/shutdown.
    – Ben Gribaudo
    Commented Oct 23, 2017 at 20:09
  • That's a very different problem - can you edit that info into the original question? As well as maybe some info as to how you're using python scripts and how often they are being run.
    – LowlyDBA - John M
    Commented Oct 23, 2017 at 20:14
  • Sorry about the confusion! I edited the question to reflect the broader problem scope between when you posted your (now deleted) comment and your answer.
    – Ben Gribaudo
    Commented Oct 23, 2017 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.