As some of you may have noticed, Windows Azure SQL Database is quite good at protecting itself. If you try to insert too many rows in a short period of time, it will simply close your connection and cause your transaction to roll back.
I was trying to insert close to 700 000 items into a table and was systematically getting cut off.
Windows Azure SQL Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Windows Azure SQL Database customers, your connection to the service may be closed due to the following conditions:
- Excessive resource usage
- Connections that have been idle for 30 minutes or longer
- Failover because of server failures
In my case, I was probably violating the first of the three reasons mentioned above. I tried to figure out how to go around this issue and after toying around with this problem for some time I came up with using a Table Value as a parameter for a Stored Procedure. I quickly found an example and came up with the following solution.
More information can be found in Windows Azure SQL Database Performance and Elasticity Guide
The degree to which a subscriber’s connectivity is blocked ranges from blocking inserts and updates only, to blocking all writes, to blocking all reads and writes. The time span for which throttling occurs is referred to as the Throttling Cycle and the duration of a Throttling Cycle is referred to as the Throttling Sleep Interval which is 10 seconds by default.
Throttling severity falls into one of two categories, Soft Throttling for “mildly exceeded” types and Hard Throttling for “significantly exceeded” types. Because significantly exceeded types pose a greater risk to overall system health, they are handled more aggressively than mildly exceeded types. Engine Throttling follows these steps to reduce load and protect system health:
- Determines the load reduction required to return the system to a healthy state.
- Marks subscriber databases that are consuming excessive resources as throttling candidates. If Engine Throttling is occurring due to a mildly exceeded type then certain databases may be exempt from consideration as throttling candidates. If Engine Throttling is due to a significantly exceeded type then all subscriber databases can be candidates for throttling with the exception of subscriber databases that have not received any load in the Throttling Cycle immediately preceding the current Throttling Cycle.
- Calculates how many candidate databases must be throttled to return the system to a healthy state by evaluating the historical resource usage patterns of the candidate databases.
- Throttles the calculated number of candidate databases until system load is returned to the desired level. Depending on whether throttling is Hard Throttling or Soft Throttling, the degree of throttling applied or the throttling mode, as described in Understanding Windows Azure SQL Database Reason Codes can vary. Any databases that are throttled remain throttled for at least the duration of one throttling cycle but throttling may often persist for multiple throttling cycles to return the system to a healthy state.
Keep in mind that you are sharing the following Database Server configuration.
As of this writing, each SQL Database computer is equipped with 32 GB RAM, 8 CPU cores and 12 hard drives. To ensure flexibility, each SQL Database computer can host multiple subscribers at a time.