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)
A truly amazing blog on SQL related topics. I also like SQL Interview QnA. Thank you.
Looks like another reason to avoid Azure entirely! I’ve never been happier with a cloud service provider as I am with AWS. Microsoft was late to the cloud and has been playing catch up ever since.
Very true. They are late but speed for them to catch up is quicker than I expected.
This article misses some of the key behaviours of Azure SQL Database in terms of throttling. Each Service Tier and performance level in Azure SQL Database has different allowances in terms of IO (both read rate and log write rate), different max memory/buffer pool sizes and different CPU time allocations. When a query or the overall workload hits these limits, queries block. Timeouts are the likely result – and in my experience it is more likely that timeouts will occur before new connections are refused. This is especially true on Basic and Standard Tiers, where just one query can very easily consume all of the available IO/CPU resource.
The sys.dm_db_resource_stats DMV provides a clear view of how much of the available IO, memory, CPU, etc is being consumed (as a percentage of the available resource in your particular service tier and performance level).
For details see:
Who is actually using Azure SQL for something beyond a simple-few-users system?