Azure SQL DB - Resources Governance

Question

We use Azure SQL Database, currently with S3 Tier and we have a problem with one of our services, who is pushing data to the database. It's called very often and most of the time our DTU is more than 95%. We already optimize what we could, but basically it's too many DB hits. We are working on other optimization, caching, etc..

The problem is, that this DB is used by our other application and because of DTU is consumed by other service, we have performance issues.

I was thinking, if there is a way, when we can somehow set up max limit for one SQL User for DTU? e.g. 30%. I was trying to google it, but couldn't find anything related to this topic. Thanks a lot for the answers or suggestions

Answer 1

There is currently no way in SQL Database to limit the resources on a per Query / client basis. Which resource dimension are you maxing out (CPU, reads, writes)? If you cannot further optimize you might have to bite the bullet and scale up. If you are IO bound, than switching to P1 will help you. If you are CPU bound you might have to go up to P2.

Answer 2

The % is always based on the S2 tier db. If you are at 95 it means you are at 95% of a S2 tier db on your DTU usage. In this case you are close to the 100%, so you probably soon need a tier larger as the S2. You are using the S3, so you have the right tier.

azure-sql-database-introduces-new-near-real-time-performance-metrics

For example, if your DTU consumption shows a value of 80%, it indicates it is consuming DTU at the rate of 80% of the limit an S2 database would have. If you see values greater than 100% in this view it means that you need a performance tier larger than S2.

As an example, let’s say you see a percentage value of 300%. This tells you that you are using three times more resources than would be available in an S2. To determine a reasonable starting size, compare the DTUs available in an S2 (50 DTUs) with the next higher sizes (P1 = 100 DTUs, or 200% of S2, P2 = 200 DTUs or 400% of S2). Because you are at 300% of S2 you would want to start with a P2 and re-test.

Based on the DTU usage percent you can determine if your database can fit within S2 performance level (OR a lower/higher level as indicated through DTU percentage and relative DTU powers of various performance tiers as documented in MSDN site).

When you have locking problems, you need to find the queries that lock the db and rewrite them. Scaling to a larger db tier will only help a little, and giving the application that causes the problems less db performance will only extend the lock times.