Azure SQL DTU Exhaustion: Top Causes and Fixes That Actually Work

Cause #1: Missing or Ineffective Indexes (The Usual Suspect)

If your DTU is pegged at 100% and your users are staring at spinning cursors, the first thing I check is the index situation. Nine times out of ten, the root cause is a query that's scanning a whole table when it should be doing a seek. The reason this hurts so much on Azure SQL is that every page read counts against your IO DTU budget. A table scan on a 10GB table can blow through your entire DTU allocation in one go.

The fastest way to confirm this is to look at sys.dm_db_resource_stats and see if the IO or CPU percentage is consistently near 100. Then, pull up the missing index DMV:

SELECT
    migs.avg_user_impact,
    migs.avg_total_user_cost,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
WHERE migs.avg_user_impact > 30
ORDER BY migs.avg_user_impact DESC;

What you're looking for here is a row with a high avg_user_impact (like 70 or above) and a simple set of columns. The fix is usually to create that index, but don't just blindly copy the DMV's suggestion. That DMV is a hint, not gospel. I've seen it suggest an index on 10 columns that would never be used. Instead, ask yourself: what's the WHERE clause doing? What columns are in the JOIN? Keep the key columns narrow and add only the columns you absolutely need in the SELECT as included columns.

For example, if you have a query like:

SELECT order_id, customer_name
FROM orders
WHERE order_date > '2024-01-01' AND status = 'Shipped';

Create this index:

CREATE INDEX IX_orders_status_date
ON orders (status, order_date)
INCLUDE (customer_name);

Why does this work? Because the index lets SQL Server do a seek on status and order_date, and the customer_name is already in the index's leaf level, so no key lookups back to the heap. That's your IO savings right there. After creating the index, re-check sys.dm_db_resource_stats for the next hour. You should see the DTU percentage drop noticeably. If it doesn't, then the index wasn't the bottleneck and you move to cause #2.

Cause #2: A Single Expensive Query (The Quiet CPU Hog)

Let's say your indexes are fine, but DTU is still spiking. The next thing I do is look at what's actually running right now. The Azure portal's Query Performance Insight is decent for this, but I prefer going straight to the DMVs because the portal has a 15-minute delay and I don't have time for that.

Run this to see the top queries by CPU and IO in the last 5 minutes:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    qt.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY avg_cpu_time DESC;

The query that shows up at the top with a huge avg_cpu_time is your problem child. But here's the thing: the fix isn't always to rewrite the query. It's often to look at the actual execution plan. I've spent hours staring at estimated plans only to find out the real issue was a parameter sniffing problem or a cardinality estimate gone wrong.

Let me give you a concrete example. I had a client whose dashboard query ran fine at 9 AM but crawled at 3 PM. The query was the same, the data was the same size, but the plan was different. The reason? Parameter sniffing. The first execution used a parameter value that matched 1% of rows, so SQL Server chose a nested loop join. Later, a different parameter value matched 50% of rows, but the plan was still stuck on nested loops. That's your CPU spike.

The fix here is to use OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR UNKNOWN) on that specific query. It's not elegant, but it works:

SELECT *
FROM orders
WHERE status = @status
OPTION (RECOMPILE);

Now, don't go sprinkling RECOMPILE everywhere. That's a hammer. But for a query that runs once per minute and takes 5 seconds of CPU, the recompile cost is negligible compared to the savings. Another tip: if the query is a stored procedure, check if it's got a WITH RECOMPILE hint at the procedure level. That might be overkill. You want to be surgical.

Cause #3: Undersized DTU Tier (When Your Queries Are Fine)

So you've added the indexes, you've tuned the queries, and DTU is still sitting at 95%. At this point, the problem might not be the workload — it might be the tier. What's actually happening here is your application has outgrown the DTU allocation. This is more common than you'd think, especially with dev/test databases that get promoted to production without a tier bump.

How do you know if it's the tier vs. the workload? Look at sys.dm_db_resource_stats over a week. If you see sustained DTU usage above 80% for more than a few hours each day, even during off-peak times, that's a strong signal. Also, look at the avg_cpu_percent and avg_io_percent columns. If one of them is maxed out but the other is low, you might be able to switch to a different tier within the same DTU level — like going from a Standard S3 to a Premium P1 if IO is the bottleneck. But if both are high, you need more DTUs, plain and simple.

The fix is to scale up. In the Azure portal, go to your database, scale it up a tier, and watch the DTU line. But here's my advice: don't just bump from S2 to S3. Look at the DTU-to-vCore transition. If you're already at S12 or above, you might be better off moving to a vCore-based model. Why? Because vCore gives you more granular control and you can use elastic pools more efficiently. Also, the DTU model's pricing is based on a fixed basket of CPU, IO, and memory — you might be paying for IO you don't use, or vice versa.

One thing people forget: scaling up doesn't fix a poorly designed schema. If you have a table with 50 columns and you're selecting 48 of them, no amount of DTUs will save you. So before you hit that scale-up button, make sure causes #1 and #2 are truly ruled out. I've seen too many people throw money at Azure when a single covering index would have solved it.

Quick-Reference Summary

Remember: the order matters. Check indexes first because they're cheap and often fix everything. Then look at the query. Only after that should you consider spending money on a bigger tier. You'll save yourself a headache and a few bucks.