azure sql limit dtu resources per operation

3
votes

Sadly last week azure transfer one database from web tier to s1 tier. I manually increase the tier to s2. worked hard to change some stuff in the system so the dtu wont reach 100%.

Now i have new situation - i get background stuff that run and doing stuff in the db like delete etc. the problem is that the background stuff consume 100 percent dtu and my website start getting errors.

my question is: is there a way to tell the sql per query/operation to consume max of X dtu? for example i want to create an index and again when i do the operation my dtu raise to 100 and it stayed there allot of time - guess its a big index to build - so again im stuck and i cancel the query because i dont want my end users to suffer lags.

all those issue didnt exists in the web tier and everything worked smoothly.

1
azureazure-sql-database

1 Answers

1
votes

That's a very nice suggestion,unfortunately limiting a particular query or operation to consume limited DTU is not possible ..may be in future versions they might bring resource governor like tools

Closest thing i can think of limiting DTU for a query is set to 

Option (MAXDOP 1)

Query may go in Parallel and consume more resources for each thread ,so limiting MAXDOP will help in limiting DTU with some caveats