Scott Klein

Understanding SQL Azure Throttling and Implementing Retry Logic

2012-01-27T13:04:29-05:00:00

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…”

“The service has encountered an error...”

“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!

Enzo Backup for SQL Azure Released!

2011-10-14T16:57:09-04:00:00

Blue Syntax is pleased, and very excited, to announce the general availability of Enzo Backup for SQL Azure. It took a monumental effort to put this product together. The hardest part was designing the backup and restore routines in a way that would not constently trigger the throttling safegaurds in SQL Azure. In addition to the typical retry logic, adaptive loading algorithms that know how to "slow down" the data read/load based on specific error conditions was also incorporated, while keeping its internal operations in parallel.

Microsoft MVP's will have access to this backup utility at no charge. You'll quickly see that the tool is very easy to use and user-friendly, and no question the most complete backup utility available to date for SQL Azure. To get a free license, simply email info@bluesyntax.net.

Take a look and download Enzo Backup at http://www.bluesyntax.net/backup.aspx, which also includes the technical overview of the tool for quick reference.

Honolulu Hawaii Azure Boot Camp Dates

2011-10-07T22:02:08-04:00:00

Just confirmed the Azure Boot Camp dates for Honolulu Hawaii. November 14th and 15th. See http://www.azurebootcamp.com/city/Honolulu.

Registration links are up for Charlotte and Mountain View Azure Boot Camps

2011-09-22T12:31:45-04:00:00

The Registration links for the Charlotte, NC and Mountain View, CA Azure Boot Camps are now up on the Azure Boot Camp site: http://www.azurebootcamp.com/schedule

See you there!

New Azure Boot Camp Dates and Locations

2011-09-19T21:12:24-04:00:00

We are happy to announce two new Azure Boot Camp dates:

Charlotte, NC - October 27th and 28th
Mountain View, CA - November 7th and 8th

These boot camps are a FREE two day deep dive class to get you up to speed on developing for Windows Azure.

Stay tuned for dates in the following locations:

Hawaii, Irvine CA, Denver CO, New York NY, Portland OR, and Seattle WA.

Information and Registration information about all Azure Boot Camps can be found here: http://www.azurebootcamp.com/schedule

Scott

3rd Annual South Florida SQL Saturday - August 13th!

2011-06-27T13:10:27-04:00:00

The 3rd annual South Florida SQL Saturday event will be help August 13th, 2011 at Nova Southeastern University in Davie, FL. This is a change in venue from last year as DeVry is in the middle of a major remodling project.

We has almost 450 last year, and we expect to have over 500 this year with great speakers such as Andy Warren, Herve Roggero, and more! As always, we our goal is to feed you well, so we are shooting for some hot lunch food this year. We are working on that now and will update you as soon as we firm it up.

One of the things we will be trying this year is 90-100 minute sessions on a couple of tracks. We strongly feel that several topics are just getting warmed up in 45 minutes, so we are going to experiment with 90-100 minute sessions on some of the BI topics.

We would love to see you there!

Enzo Backup for SQL Azure Beta Released!

2011-06-27T13:02:33-04:00:00

Blue Syntax is happy to announce the release of their SQL Azure database backup product! Enzo Backup for SQL Azure offers unparalleled backup and restored functionlity and flexibility of a SQL Azure database. You can download the beta release here:

http://www.bluesyntax.net/backup.aspx

With Enzo Backup for SQL Azure, you can:

Create a backup blob, or a backup file from a SQL Azure database
Restore a SQL Azure database from a backup blob, or a backup file
Perform limited backup and restore of SQL Server databases (see details)
Run backups entirely in the cloud using a remote agent
Backup a single schema of a database
Restore specific tables only
Copy backup devices from on-premise to the cloud
Use a command-line utility to perform backup operations
Perform transactionally consistent backups for SQL Azure

Please download it and provide us your feed back!

Phoenix and Las Vegas Azure Boot Camp Dates Finalized

2011-06-14T23:22:49-04:00:00

The dates for the Las Vegas and Phoenix Azure Boot Camps have been finalized.

Las Vegas - August 17 and 18
Phoenix - August 24 and 25
Salt Lake City - September 23 and 24

These boot camps are a FREE two day deep dive class to get you up to speed on developing for Windows Azure.

Information and Registration information about all Azure Boot Camps can be found here: http://www.azurebootcamp.com/schedule

Scott

Azure Boot Camps on the West Coast

2011-05-30T20:21:40-04:00:00

Blue Syntax has been working with Microsoft and the Central Region DPE's to help train at many of the Azure Boot Camps throughout the Central Region, including Dallas, St. Louis, Nashville, and Indianapolis. Blue Syntax is proud to announce that they are holding Azure Boot Camps on the West Coast starting in June. The cities and dates are as follows:

San Francisco - June 13 and 14
Phoenix - TBD
Las Vegas - TBD
Salt Lake City - September 23 and 24
Irvine - TBD

Phoenix, Las Vegas, and Irvine dates will be confirmed within the next week. Once I have those dates I will post here.

Information and Registration information about all Azure Boot Camps can be found here: http://www.azurebootcamp.com/schedule

Scott

2011 SQL Cruises Announced!

2010-11-04T18:49:29-04:00:00

Brent Ozar and Tim Ford announced today they have two SQL Cruises planned for 2011. If you don't know what a SQL Cruise is, you are missing out. Hop on over to their SQL Cruise site for information, but I'll briefly tell you that Brent and Tim have found themselves a niche. Brent and Tim simply reserved some space on a cruise ship, and for a low price for the cruise and a VERY low price for quality training, you are enjoying quality time with fellow SQL folks as well as relaxing as you travel to great destinations! How can you beat cruising to great places while learning SQL at the same time, from two stalwart SQL Server gurus?!?!

For 2011 they have two cruises planned:

A 5-day cruise leaving March 6th out of Miami to Grand Cayman and Cozumel. This cruise is geared toward entry-level and beginner SQL Server professionals who have 1-3 years of experience working with SQL Server.

A 7-day cruise leaving May 29th out of Seattle to Alaska. This cruise is aimed at the moderate and advanced SQL Server Professionals, with Buck Woody as a guest speaker!