SQL SERVER – Introduction to SQL Azure Database Throttling

In standalone versions of SQL Server, if your SQL Server receives more concurrent requests than it can service simultaneously, it will queue the requests for later processing (subject to certain limits—generally available memory on the box).  For example, if you have the max worker thread configuration option set to 100 and there are already 100 queries submitted to the server than any new requests are simply queued until one of the worker threads completes and can pick up the request.  Except in fatal circumstances where DBA intervention is required this will allow all of the requests to eventually complete (or potentially timeout, if the client specified this setting). Let us understand about SQL Azure Database Throttling.

SQL Azure breaks from this longstanding model and instead throttles (i.e., rejects or cancels, rather than queues) requests when the server becomes too busy. The throttling is done in order to prevent cascading failures that could occur when the server becomes too busy, thus ensuring some basic service level for all users regardless of the load on the system.  Note that the SQL Azure throttling does not use and should not be confused with the SQL Server Resource Governor feature.

The simple fact that a user’s request is throttled is considered by design and by itself is not considered a problem with the service or service availability. Properly designed applications written for SQL Azure will have error handlers to trap the throttling errors and retry the operation at a later time (at least greater than the SQL Azure throttling interval, which is configured for 10 seconds). If another user on the same backend server causes you to be throttled continuously over a sustained period of 5 minutes or longer (where you can perform no work) then this will qualify as a service availability issue.

Now that we understood how this throttling works, I was wondering if I can use some of the DMVs to understand if there are IO waits for example. I opened a transaction and went about inserting few 100’s of rows in a batch. I wanted to see if there are throttling limits that are hitting me:

-- Read IO Stalls
SELECT @@VERSION
GO
SELECT type, SUM(io_stall_read_ms) AS io_stall_read,
SUM(io_stall_write_ms) AS io_stall_write,
SUM(num_of_reads) AS num_of_reads,
SUM(num_of_writes) AS num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs
JOIN sys.database_files df
ON fs.file_id = df.file_id
WHERE database_id = DB_ID() GROUP BY df.type

As you can see I am running on the latest version and to validate, my reads and writes are getting throttled and hence the stall values in them are on a higher side. I took a basic small database for this experiment to get high stall. If you provision a higher Premier version, the limits might never hit for such small experiments.

Do let me know how many are working with Azure, SQL databases and are using the same for your applications. What are those typical applications that you have been migrating to the cloud database? Let me know via comments as I would like to learn from your experience too.

Reference: Pinal Dave (https://blog.sqlauthority.com)