VERKOOP: 1-800-867-1389
EN
Deze inhoud is niet beschikbaar in uw taal, maar wel in het Engels.

Azure SQL Database Throttling

Updated: June 26, 2014

This topic describes in detail the engine throttling mechanism, the associated error codes, and how to address the errors you might run into.

How Does Engine Throttling Work?

The following table provides information about the engine throttling mechanism, the corresponding error code that is returned, and recommendation on how to address it.

 

Engine Throttling Mechanism Error code returned Recommendation

Engine throttling follows these steps to reduce load and protect system health:

  1. Determines the load reduction required to return the system to a healthy state.

  2. 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.

  3. 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.

  4. 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 can vary. You can figure out the degree and mode of the throttling applied by using the Incident ID and Code values in the error message. Any databases that are throttled remain throttled for at least the duration of one throttling cycle (10 seconds), but throttling may often persist for multiple throttling cycles to return the system to a healthy state.

40501: The service is currently busy. Retry the request after 10 seconds. Incident ID: <ID>. Code: <code>.

noteNote
The Incident ID and Code values can be used to determine which requests are throttled and whether it’s a soft or hard throttle. For more information, see the “Throttling Incident ID” and “Decoding Reason Codes” sections later in this topic.

Back-off and retry request after 10 seconds.

Throttling Incident ID

The throttling incident ID in error 40501 is a GUID value that uniquely identifies a throttling incident.

40501: The service is currently busy. Retry the request after 10 seconds. Incident ID: <ID>. Code: <code>.

If you cannot determine why the throttling occurred or if it persists, and you do not know how to resolve it, contact Microsoft Support and state the incident ID (<ID>) in the error message. Microsoft Support will use this incident ID to retrieve more information related to your throttling incident. The incident ID can be used to get the following information:

  • The starting time of the throttling incident.

  • The type of throttling (soft throttling vs. hard throttling).

  • The resource type (for example, CPU) due to which the throttling incident is hit.

  • What was the user running when this throttling incident happened?

After learning the underlying root cause from Microsoft Customer Support, you can make appropriate changes in your application.

Decoding Reason Codes

The following section describes how to decode the reason codes that are returned by the following engine throttling error code.

40501: The service is currently busy. Retry the request after 10 seconds. Incident ID: <ID>. Code: <code>.

The reason code (<code>) in the error message is a decimal number that contains information about the throttling mode and the exceeded resource type(s):

  • The throttling mode enumerates the rejected statement types.

  • The resource type specifies the exceeded resources. Throttling can happen on multiple resource types concurrently, such as CPU and IO.

Consider the following sample error message as an example, where the reason code is 131075.

40501: The service is currently busy. Retry the request after 10 seconds. Incident ID: {5DE17AB8-A6E34BE5-A2E95BB5D4CC4155}. Code: 131075.

The following diagram demonstrates how to decode the reason codes.

Decoding reason codes

To obtain the throttling mode, apply modulo 4 to the reason code. The modulo operation returns the remainder of one number divided by another. To obtain the throttling type and resource type, divide the reason code by 256 as shown in step 1. Then, convert the quotient of the result to its binary equivalent as shown in steps 2 and 3. The diagram lists all the throttling types and resource types. Compare your throttling type with the resource type bits as shown in the diagram.

The following table provides a list of the throttling modes.

 

Throttling mode code Description Rejected statement types Statements that can still be processed

0

No throttling

None

All

1

Reject Update / Insert

INSERT, UPDATE, CREATE TABLE | INDEX

DELETE, DROP TABLE | INDEX, TRUNCATE

2

Reject All writes

INSERT, UPDATE, DELETE, CREATE, DROP

SELECT

3

Reject All

All

None

To obtain the throttling mode, apply modulo 4 to the reason code. 131075 % 4 = 3. The result 3 means the throttling mode is "Reject All".

To obtain the throttling type and resource type, divide the reason code by 256. Then, convert the quotient of the result to its binary equivalent. 131075 / 256 = 512 (decimal) and 512 (decimal) = 10 00 00 00 00 (binary). This means the database was throttled due to CPU (Resource Type 4) and Hard Throttling (10).

Sample Code to Decode Reason Codes

The following sample code uses the ThrottlingCondition class in the Transient Fault Handling Application block (also known as “Topaz”) in the Enterprise Library Integration Pack for Azure to decode the reason code in the engine throttling error (40501). To download the Transient Fault Handling Application block assemblies and source code to use the ThrottlingCondition class, see Enterprise Library 5.0 Integration Pack for Azure.

The following sample code prompts you to enter the reason code that you get in the engine throttling error (40501), and then displays the throttling mode, throttling type, and resource type for the specified reason code.

using System;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.Data;

namespace ThrottlingDecoder
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("Enter a throttling code to be decoded: ");
            var rawCode = Console.ReadLine();
            int reasonCode;

            if (Int32.TryParse(rawCode, out reasonCode))
            {
                var throttlingCode = ThrottlingCondition.FromReasonCode(reasonCode);

                Console.WriteLine("\nBreakdown for throttling reason code {0}:\n", reasonCode);

                Console.WriteLine("Throttling mode: {0}", throttlingCode.ThrottlingMode);
                Console.WriteLine("Throttled On CPU: {0}", throttlingCode.IsThrottledOnCpu);
                Console.WriteLine("Throttled On DB Size: {0}", throttlingCode.IsThrottledOnDatabaseSize);
                Console.WriteLine("Throttled On DB Reads: {0}", throttlingCode.IsThrottledOnDataRead);
                Console.WriteLine("Throttled On DB Free space: {0}", throttlingCode.IsThrottledOnDataSpace);
                Console.WriteLine("Throttled On Log Free Size: {0}", throttlingCode.IsThrottledOnLogSpace);
                Console.WriteLine("Throttled On Log Writes: {0}", throttlingCode.IsThrottledOnLogWrite);
                Console.WriteLine("Throttled On Worker Threads: {0}", throttlingCode.IsThrottledOnWorkerThreads);
                
                Console.WriteLine("\nThrottled resources:");

                foreach (var res in throttlingCode.ThrottledResources)
                {
                   if (res.Item2 != ThrottlingType.None) Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("Resource type {0} is throttled on {1}", res.Item1, res.Item2);
                    if (res.Item2 != ThrottlingType.None) Console.ResetColor();
                }
            }
            else
            {
                Console.WriteLine("Sorry, but the input you provided does not seem to be a valid number.");
            }
            Console.Read();
        }
    }
}

Best Practice Recommendations

Implement Retry Logic in your Application

The SQL Database TDS Gateway retries connections for about 30 seconds before reporting a failure. If you expect a high volume of application traffic, build retry logic in your application. If a connection fails, do the following:

  • Handle idleness and transient disconnections.

    noteNote
    Install Reliability Update 1 for .NET Framework 4.0 that fixes the issue of SqlClient returning dead connections to the application. With this update, SqlClient checks if the connection in the pool is dead before returning it to the application. If the connection is dead, SqlClient reconnects before returning it to the application. For more information about this issue, see Minimizing Connection Pool errors in SQL Database.

  • Retry to connect to SQL Database in intervals of 10 seconds until the resources are available and your connection is established again. Depending on your application, databases, and network workload, you must increase the delay time as necessary.

  • Change your workload if a connection is terminated again. If a connection is terminated again, look at the error code, find out the real problem, and then try to change your workload. You can implement a queue or a delay mechanism in your client application to reduce your workload.

    Another solution could be to redesign your application and database to remove resource bottlenecks. Ensure that your application do not overload tempdb through excessive DDL or DML operations. In addition, ensure that transactions do not block any resource. When appropriate, consider partitioning your database into multiple databases.

  • Ensure you code can recover from throttling. Users should assume that connections can disconnect at any time in an application. Therefore make a batch request in a single transaction whenever possible. This minimizes the cases where a batch could partially complete and put the database in a state that is unexpected/untested. To do this, you might want to do BEGIN TRANS/COMMIT TRANS around each ad hoc batch, and at the start/end of each stored procedure.

  • Understand how retry logic impacts the correctness of an application (idempotency). When performing updates or inserts, it is important to understand what happens when a connection is disconnected before success is returned. If the transaction is aborted, then retrying the connection is the right step. If the transaction is actually committed then running the transaction again might not be correct. Users performing changes that are non-idempotent may need to modify their logical database schema to be able to detect repeated transaction commits based on the retry logic being utilized to protect against disconnects.

Log Throttling Errors

Use the retry code to log throttling errors to distinguish between transient connection, throttling and hard failure syntax, missing stored procedures, and so on. This becomes particularly useful in troubleshooting scenarios if you are unable to connect to the SQL Database. In this case troubleshooting efforts will focus on logged information; and the ability to effectively troubleshoot will correlate to the quality of the logged information. For more information about implementing logging in SQL Database applications, see Implementing Logging for Azure SQL Database Applications.

See Also

Vindt u dit nuttig?
(1500 tekens resterend)
Bedankt voor uw feedback

Community-inhoud

Toevoegen
Weergeven:
© 2014 Microsoft