I have an Azure worker role that inserts a batch of records into a table. Yesterday, it took at most 5 minutes to insert the records, but today it has been taking up to a couple of hours. I suspect that the process is being throttled, but I don't get any exceptions. Does SQL Azure always return an error if you are being throttled, or is there another way to detect if you are being throttled?
Asked
Active
Viewed 4,229 times
2 Answers
4
In case of CPU throttling SQL Database will not throw an error but will slowdown the operation. At this time there is no mechanism to determine whether this form of throttling is taking place other than possibly looking at the query stats telling that the work is taking place slowly (if your CPU time is lower than usual). Check this link for details about this behavior: performance and elasticity guid (look for "Performance Thresholds Monitored by Engine Throttling").
Herve Roggero
- 5,149
- 1
- 17
- 11
-
But for non-CPU throttling, you will generally get an exception with an error code indicating the throttling type. – Brian Reischl Jul 11 '12 at 19:12
-
Yes, you are correct. All of that is widely documented in the link I provided. The question was if SQL Azure "always" return an error code when being throttled. The answer is no. Not always. And for the second part of the question, if there is another way to detect throttling, the answer is also no. Not in the case of CPU throttling. – Herve Roggero Jul 12 '12 at 12:28
4
One of the newer capabilities is the ability to monitor the number of outstanding requests a SQL Azure database has. You can do this with this query:
select count(*) from sys.dm_exec_requests
As you will see in this documentation reaching the limit of worker threads is a key reason for being throttled. Also documented here is that as you approach 180 worker threads you can expect to be throttled.
This is one of the things used in the Cotega monitoring service for SQL Azure to detect issues. [Disclaimer: I work on this service]
Cotega
- 339
- 1
- 2
- 8