- Joined
- Feb 4, 2016
- Messages
- 90
- Likes received
- 7
- Thanks received
- 2
The only other thing I can think of is a none azure specific issue such as a bad execution plan OPTION(RECOMPILE) on the azure function call to the proc or corruption in the buffer.
Edit: Are the database indexes and statistics fresh?
https://docs.microsoft.com/en-us/sq...e-commands/dbcc-dropcleanbuffers-transact-sql
Failing that try turning turing sql server on and off again if you can get a window to do that.
I think it's very likely to be something related to the statistics/dodgy plan.
I think Mudskipper might be onto something with SET ARITHABORT ON. I've read a couple of answers on SO and people say it's sorted their problem out then others have pointed out that it may be a red herring and it's actually a dodgy plan.
I did try setting SET ARITHABORT ON in the stored procedure but no difference. But from what I've read the actual connection from ado.net trumps the stored procedure settings. So I'm going to attempt to change the code in the Azure function to set this on for the connection. Working with live data so will make the change and allow it to run in the morning.
I'm limited on time at the moment so going for the the low hanging fruit. But my feeling is it's actually not related to the function at all and as you have said it's db.
There's also the parameter sniffing stuff. I changed my SPs to assign the passed parameters to local variables and use them in the query. Can't be sure whether it made a difference - my comment said " -- This might help... "
I did try setting SET ARITHABORT ON in the stored procedure but no difference. But from what I've read the actual connection from ado.net trumps the stored procedure settings. So I'm going to attempt to change the code in the Azure function to set this on for the connection. Working with live data so will make the change and allow it to run in the morning.
conn.StateChange += new StateChangeEventHandler(OnStateChange);
conn.Open();
private static void OnStateChange(object sender, StateChangeEventArgs args)
{
if (args.OriginalState == ConnectionState.Closed
&& args.CurrentState == ConnectionState.Open)
{
using (SqlCommand cmd = ((SqlConnection)sender).CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SET ARITHABORT OFF;";
cmd.ExecuteNonQuery();
}
}
}
The Azure function timing reduced itself from 6 mins to 17 seconds without changing anything.
The black art of databases.