I have seen a number of questions lately regarding how SQL Azure handles throttling and how to determine why the throttling occurred. Sometimes those questions are followed by another question asking how to handle throttling conditions in their applications.
Troubleshooting SQL Azure Throttling
GREAT NEWS! There actually is a way to find out if you are throttled and why, and the results of the throttling. The key is to look at the error message coming back to you. You’ll typically see an error message such as:
“The server is currently busy…”
Or
“The service has encountered an error...”
Or
“The service is experiencing a problem…”
There a few more, but the key is to look at the END of these messages because there will be a Code at the end. For example:
“The service is currently busy. Retry the request after 10 seconds. Code %d”.
The code is a decimal value, and is the vital piece of information to tracking down the throttling issue. For example, 131075. Don’t confuse this code with the error code. You’ll actually see two codes in the error message; the actual error code, and the reason code. It is the reason code we are after; the code that follows the error message.
So, before we dive into this reason code number, it is also important to understand the throttling modes, and throttling types. The types explain why you are getting throttled, and the modes explain how you are being throttled. Thus, it is this decimal code value at the end of the message that specifies the mode and type of throttling.
The two tables below show the different throttling types and modes. Throttling types, the reason you are being throttled, will fall into either a soft throttling category or a hard throttling category. This is because substantially exceeded types pose a much greater risk to the system and thus are handled more aggressively. Throttling modes range from no throttling at all to completely rejecting all read and writes.
Throttling Types
| Throttling type | Soft Throttling limit exceeded | Hard Throttling limit exceeded |
| Temporary disk space problem occurred | 0x01 | 0x02 |
| Temporary log space problem occurred | 0x04 | 0x08 |
| High-volume transaction/write/update activity exists | 0x10 | 0x20 |
| High-volume database input/output (I/O) activity exists | 0x40 | 0x80 |
| High-volume CPU activity exists | 0x100 | 0x200 |
| Database quota exceeded | 0x400 | 0x800 |
| Too many concurrent requests occurred | 0x4000 | 0x8000 |
Throttling Modes
| Throttling mode | Description | Types of statements disallowed | Types of statements allowed |
| 0x00 | AllowAll - No throttling, all queries permitted. | No statements disallowed | All statements allowed |
| 0x01 | RejectUpsert - Updates and Inserts will fail. | INSERT, UPDATE, CREATE TABLE | INDEX | DELETE, DROP TABLE | INDEX, TRUNCATE |
| 0x02 | RejectAllWrites - All writes (including deletes) will fail. | INSERT, UPDATE, DELETE, CREATE, DROP | SELECT |
| 0x03 | RejectAll - All reads and writes will fail. | All statements disallowed | No statements allowed |
With the error code in hand, open up Windows Calculator and from the View menu, select Programmer. We need the calculator to run in programmer mode because we are programmers. Not really, but because we need several keys that the Programmer mode supplies.
With the Calculator in Programmer mode, make sure the Dec and Dword options are selected on the left side of the calculator. Next, enter the code from the error message, in this case 131075. Now we’re getting to the good part. Once you have entered the reason code, change the notation from Dec to Hex. The reason code will now show 20003.
This number is the mode and type. How? The last two digits (03) are the throttling mode. The remaining digits, in this case the first three (200), are the throttling. We can then take these two numbers and look up in the two tables and determine that the throttling mode (03) is Reject All and the throttling type is High-volume CPU activity exists.
Thus, in this example the throttling occurred because too much CPU activity was taking place and therefor it was determined that Hard Throttling needed to be imposed and thus all reads are writes will fail.
Transient Fault Handling Application Block
The next step in our quest for improved performance is to implement retry logic into the application. Building something like this on your own could take months, so it sure is a good thing that it has been included in the Microsoft Enterprise Library. Now it is almost plug-n-play.
The retry logic built into the Enterprise library is called the Transient Fault Handling Application Block providing a set of reusable components for adding retry logic into your Windows Azure application. You can use these components against SQL Azure, Windows Azure Storage, and the Service Bus and Caching Service.
It is easy to add to your applications via Nuget. With your application open, type and run the following command in the Package Manager Console (also highlighted in the image below).
Install-Package EnterpriseLibrary.WindowsAzure.TransientFaultHandling
The Nuget package installs the Enterprise Library and adds all the necessary references to the project.
I’m going to highlight three simple examples on how to implement retry logic into your application. However, before using the components, you need to add a few directives:
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
using Microsoft.Practices.TransientFaultHandling;
using System.Data.EntityClient;
using System.Data.SqlClient;
This first example uses the ReliableSqlConnection class, which looks very similar to the SqlConnection class of ADO.NET, but provides a set of value-add methods. These methods ensure that connections could be reliable established and commands reliably executed against a SQL Azure database.
In the code below, the ReliableSqlConnection is used to establish a reliable connection and execute a query against that connection.
using (var cnn = new ReliableSqlConnection(connString))
{
cnn.Open();
using (var cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM HumanResources.Employee";
using (var rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
//
}
}
}
}
The previous example is pretty plain, and doesn’t really illustrate the flexibility of the retry components. This is because we haven’t defined a retry policy. Sure, we used the ReliableSqlConnection class as shown in the previous example, and as such will provide the built-in default policy as to how many times the retry will occur, and so on. But the real power comes from defining a custom policy, via the RetryPolicy class.
The RetryPolicy class allows for creating different policies based on our needs. The RetryPolicy class contains several constructors that accepts input pertaining to the retry count, retry interval timespan, and delta backoff.
This next code example below illustrates creating a retry policy using the RetryPolicy class, specifying the retry attempts and the fixed time between retries. This policy is then applied to the ReliablSqlConnection as both a policy to the connection as well as the policy to the command.
RetryPolicy myretrypolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3, TimeSpan.FromSeconds(30));
using (ReliableSqlConnection cnn = new ReliableSqlConnection(connString, myretrypolicy, myretrypolicy))
{
try
{
cnn.Open();
using (var cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM HumanResources.Employee";
using (var rdr = cnn.ExecuteCommand<IDataReader>(cmd))
{
//
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
The awesome thing is that retry logic just doesn’t exist for ADO.NET APIs, but the Entity Framework as well. The implementation of the retry policy model in the Transient Fault Handling Application Block makes it easy to wrap any user code into a the scope of a retry, and for the Entity Framework this is accomplished via the ExecuteAction and ExecuteAction<T> methods of the RetryPolicy class.
using (NorthwindEntities dc = new NorthwindEntities())
{
RetryPolicy myPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3);
Employee e1 = myPolicy.ExecuteAction<Employee>(() =>
(from x in dc.Employees
where x.LastName == "King"
select x).First());
}
Now, there may be times when using the ReliableSqlConnection classes just aren’t feasible. Some developers are comfortable with the existing SqlConnection classes of ADO.NET and don’t want to 1) worry about replacing their existing database logic code with new code, or 2) may not trust a newer plug-in component. And this makes sense, as the ReliableSqlConnection classes are targeted more for new development than existing applications.
However, the question then becomes how to implement retry logic in existing applications. The answer is an easy one as shown in the example below. The ADO.NET SqlConnection class comes with an OpenWithRetry method which takes the same retry policy. We simply define a retry policy and pass that to the OpenWithRetry method. Equally as important, the SqlCommand object has several retry methods that provide the ability to pass a retry policy as well, including ExecuteReaderWithRetry, ExecuteNonQueryWithRetry, ExecuteScalarWithRetry, and ExecuteXmlReaderWithRetry. The example below defines a retry policy and uses that as a parameter to both the OpenWithRetry on the connection as well on the ExecuteReaderWithRetry.
RetryPolicy myretrypolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3, TimeSpan.FromSeconds(30));
using (SqlConnection cnn = new SqlConnection(connString))
{
cnn.OpenWithRetry(myretrypolicy);
using (var cmd =
cnn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM
HumanResources.Employee";
using (var rdr =
cmd.ExecuteReaderWithRetry(myretrypolicy))
{
if (rdr.Read())
{
//
}
}
}
}
You have several options, but the point is that you don’t need to build retry logic yourself. The Transient Fault Handling Application Block makes it very easy to implement retry logic into new or existing applications. Plus, with your trusty calculator (in Programmer mode) you can now determine why throttling might be happening and handles those situations gracefully.
Happy coding!