Azure function, slooooow.

[woohoo]

I'm not sure if we have an Azure expert on here but here goes...

I have an Azure Function, just runs a stored proc that doesnt return any data, see below. It uses a cron timer to schedule the job to run at a set time each day.

The proc just does an update and the function is pretty simple.

var result = cmd.ExecuteNonQuery();

It's now taking 6 mins to run. Running the proc from SSMS it takes a few seconds with the exact same parameters. Nothing else is going on with the database server.

Any ideas or approaches?

[NickFitz]

I'm conjecturing from my experience of AWS Lambda functions, which are basically the same thing, that the time is being spent getting the thing started up. Presumably the underlying instance that runs the function is not being kept around long enough to be reused, so it has to load everything from scratch each time. If it's a small instance, and it's initialising the whole of Windows and then the .NET framework and/or a bunch of other stuff, that might account for the slowness.

Here's an article I read yesterday about AWS Lambda by a chap who works there, explaining how that works: https://medium.com/@PaulDJohnston/wh...s-4f547a6e87c3

Note the stuff about the difference between a cold start and a warm start, and the throwaway comment that "The optimised execution environment is kept around for a while to be invoked". I assume Azure Functions work in a very similar fashion, and if the time between executions is longer than "a while" then the whole thing has to do a cold start every time.

You could try scheduling it four minutes earlier…

[madame SasGuru]

What NF says - its only triggered once a day so will be doing it from a cold start.

[woohoo]

Thanks both.

It could well be that it's a cold start that is causing part of the delay. I've read a few things on github and stackoverflow about people warming up the function. But the saving in time does not match the results I'm seeing.

I've had issues with the function failing because of the sql connection timing out. I've increased the timeout so now I can see the function is taking around 6 minutes to complete. But it's the timeout of the running stored procedure that indicates it not just a slow start.

I've improved the performance of the proc and it takes less than 10 seconds. So I'm finding it hard to reconcile the difference in time.
Really all the function is doing is calling a proc, I'm just doing an update so no real data to return or processing in the function.

[BlueSharp]

Thanks both.

It could well be that it's a cold start that is causing part of the delay. I've read a few things on github and stackoverflow about people warming up the function. But the saving in time does not match the results I'm seeing.

I've had issues with the function failing because of the sql connection timing out. I've increased the timeout so now I can see the function is taking around 6 minutes to complete. But it's the timeout of the running stored procedure that indicates it not just a slow start.

I've improved the performance of the proc and it takes less than 10 seconds. So I'm finding it hard to reconcile the difference in time.
Really all the function is doing is calling a proc, I'm just doing an update so no real data to return or processing in the function.

6 minutes seems to be a long time to do a warm up. With azure functions they will auto scale out, is the function scaling out and causing a locking issue? i.e. multiple issues being ran at the same time. If it's only ran once a day it might be worth looking at an azure logic app.

[woohoo]

6 minutes seems to be a long time to do a warm up. With azure functions they will auto scale out, is the function scaling out and causing a locking issue? i.e. multiple issues being ran at the same time. If it's only ran once a day it might be worth looking at an azure logic app.

The scale out is interesting. I'm having trouble finding much about scaling when the function is triggered by a timer. I can't seem to find this information in the Portal (in might be in front of me but I can't see it).

What I can find about scaling is relevant to handling lots of requests or when the cpu hits max. But I can't see calling a stored proc as something that is CPU intensive for the function. It's the db doing the work.


Thanks for another avenue to look down.

[BlueSharp]

The scale out is interesting. I'm having trouble finding much about scaling when the function is triggered by a timer. I can't seem to find this information in the Portal (in might be in front of me but I can't see it).

What I can find about scaling is relevant to handling lots of requests or when the cpu hits max. But I can't see calling a stored proc as something that is CPU intensive for the function. It's the db doing the work.


Thanks for another avenue to look down.

The auto scaling we use is for message queue subscribers, the timer job we have is on the traditional "always on plan" as it runs for over ten minutes. You can see the number of instances span up in the portal. Click on your azure function and click on monitor. Also worth sticking in some trace writer events and writing the logs to blob storage when debugging these things.

[woohoo]

The auto scaling we use is for message queue subscribers, the timer job we have is on the traditional "always on plan" as it runs for over ten minutes. You can see the number of instances span up in the portal. Click on your azure function and click on monitor. Also worth sticking in some trace writer events and writing the logs to blob storage when debugging these things.

So, there is no mention of instances in monitor so I assume there is only one instance spinning up.
The logs I have are not duplicated or anything so again I assume there is only one instance.

The logging I have shows that opening the connection is near instant and all of the time is spent executing the stored procedure. So I have logging before and after the ExecuteNonQuery and it's this that is taking all the time.

When I get a bit of time I'm going to run the function from visual studio and see how it compares. Unfortunately, the client does not have a test system so I have to be careful.

[mudskipper]

Thanks both.

It could well be that it's a cold start that is causing part of the delay. I've read a few things on github and stackoverflow about people warming up the function. But the saving in time does not match the results I'm seeing.

I've had issues with the function failing because of the sql connection timing out. I've increased the timeout so now I can see the function is taking around 6 minutes to complete. But it's the timeout of the running stored procedure that indicates it not just a slow start.

I've improved the performance of the proc and it takes less than 10 seconds. So I'm finding it hard to reconcile the difference in time.
Really all the function is doing is calling a proc, I'm just doing an update so no real data to return or processing in the function.

Have you looked at the query plans?

I had a similar issue calling a stored proc from .Net - turns out the .Net framework always runs with ARITHABORT off, whereas in SSMS it's on by default. Sticking SET ARITHABORT ON at the top of my SP fixed the issue.

Dunno if Azure functions is the same, but worth a try perhaps.

[woohoo]

Have you looked at the query plans?

I had a similar issue calling a stored proc from .Net - turns out the .Net framework always runs with ARITHABORT off, whereas in SSMS it's on by default. Sticking SET ARITHABORT ON at the top of my SP fixed the issue.

Dunno if Azure functions is the same, but worth a try perhaps.

That's given me an option to look at. Just reading about it on stackoverflow now, though some argument about whether it does make a difference or it's because of dodgy cached plans.

Nice one.