How to limit DTU per job/task for SQL on Azure

Question

Hello I am wondering if there is a way to set a limit on a per job or task basis that a job or task on a SQL DB that is hosted on Azure.

So, something like....

• Weekly backup is limited to consuming 10% of available DTU

Currently we have a few manual jobs and stored procedures that will consume 100% of available DTU's when run. And I have a sneaking suspicion that it would consume as many DTU's as we give it...such as in an elastic pool.

Answer 1

Good day @Jason Johnson ,

Your question is very confusing and I cannot decided what service you speak about. In one side according to your description and the fact that you speak about DTU and SQL in Azure, it seems like you speak about Azure SQL Database. Please remember that there are multiple services in the Azure based on SQL Server. In the other side you speak about backup the database but Azure SQL Database does not support to execute BACKUP DATABASE command (it is not in public preview but not supported).

So I am confusing what service you speak about. I am guessing that your question is about Azure SQL Database, and you mentioned backup just as example for the sake of the discussion.

Note: in the future or if my guess is wrong, then please clarify what type of service you speak about using the exact name of the service.

So... assuming you speak about the Azure SQL Database:

"limit DTU per job/task" is a very common request, which I saw in the forum multiple times. Unfortunately it is not directly supported.

So what next? What can you do?

1) before the task you can move the database to higher pricing tier and take it back lower after the task. This is the official way to use the service for long running tasks.

2) You can limit the number of the CPU which take part in the execution of a query using the hing MAXDOP

This can be done in the database level:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1  

Or in the query level:

SELECT C1,C2 FROM TBL OPTION (MAXDOP 1);  

But you should remember that Azure Database DTU is a combine value of multiple parameters including I/O, CPU, and Memory. Using the DTU pricing model you cannot control one of these separately. The DTU might be a result of other parameters and not only CPU.