Azure SQL Database Timeout Exceptions

I had came across an issue were the Azure database was throwing timeout exceptions like the one below when under heavy load.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was – [Pre-Login] initialization=2; handshake=23; [Login] initialization=0; authentication=0; [Post-Login] complete=1;

 

The project  I was working on involved inserting several thousand rows of data from a single message received from an Azure service bus queue. A WebJob reads the messages of the queue and inserts several hundred rows at a time to reduce the transaction size and time.

I had also used the EAB Transient Fault Handling block https://msdn.microsoft.com/en-us/library/hh680934(v=pandp.50).aspx  which wrapped the code that calls the insert stored procedure to handle any network transients that may occur.

Initially I set the retry logic to 1 second. I was unsure why I was getting timeout exception errors as the retry logic should have taken care of this. It was only until I came across this article about transient errors https://azure.microsoft.com/nb-no/documentation/articles/sql-database-connectivity-issues/

The sentence in article that stood out for me was the one highlighted below:

We recommend that you delay for 5 seconds before your first retry. Retrying after a delay shorter than 5 seconds risks overwhelming the cloud service. For each subsequent retry the delay should grow exponentially, up to a maximum of 60 seconds.

 

Also another issue I had was when inserting data into a database table from within a loop. This was causing the Azure database to throttle and the Database Throughput Unit (DTU) to flat line at 100%. I resolved this issue by forcing a 3 second  delay after each iteration of the loop after reading this article from Microsoft https://azure.microsoft.com/en-us/documentation/articles/sql-database-resource-limits/  This delay caused the DTU to now reside around the 50-70% mark.

 

In conclusion, it seems that the short retry period and the loop I had  for inserting a large dataset caused the timeout exception errors to occur. After making the changes to increase the retry period and adding a delay in the looping function, caused the timeout exceptions to disappear.

Leave a Reply

Your email address will not be published. Required fields are marked *