My good friend and mentor in many ways Govind Kanshi did write about his learning of working on Cloud for ISV’s and he calls out a number of caveats. One of the hidden gem is the article is the concept of throttling.
We keep talking about this to our customers and from time to time I start getting requests how to solve some of the error messages developers are getting as part of new deployment to cloud. Recently saw an error message from a customer:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
They said they were working on a batch process and getting this error from time-to-time. The general tendency was to ask, “what have you done in your environments to solve this?” The common answer I get is, adding a connection string property: Connection Timeout=300 or some high number.
Ahhh !!! Do you get it? Is there a problem? All these are great solutions when you are working with SQL Server On-premise and makes a lot of sense. If you are working on Azure SQLDB (any cloud vendor), then you need to understand the behaviour of throttling :).
Everything on the cloud has limits and there are few restrictions one needs to adhere. Cloud dynamics is all about sharing resources, not having run-away of resources by one connections and monetizing using scale sharable model of resources.
So customer always asks, “what should I do now?” What numbers should I be aware off? In this particular case, I generally say to customer:
- Try to make transactions smaller and do it in batches instead of a big ONE transaction for the whole batch.
- There are limits to how long a transaction can be open like ~30mins. So this can be avoided if we do the above.
- If you are doing a lot of TempDB interactions or DMLs within the same transaction boundary, there are limits to ~2GB for the size of the transaction one can have. Doing the Pt 1, we can avoid that too.
- Irrespective of all the above, try to build a retry logic in your application / DAL layer to handle this behaviour.
Please read the WIKI page to understand more about the limits, they are worth a note. Also learn about how the SQL Engine applies the throttling in the Azure world.
The basics don’t change irrespective of where you work in cloud. It is important to understand them and be prepared in our code upfront. Have you built retry logics in your DAL anytime, share us your experience.