1

I have a not simple query. When I had 10 DTUs for my database, it took about 17 seconds to execute the query. I increased the level to 50 DTU - now the execution takes 3-4 seconds. This ratio corresponds to the documentation - more DTU = work faster.

But!

1 On my PC I can execute the query in 1 sec.

2 In portal-statistics I see that I use only 12 DTU (max DTU percentage = 25% ). In sys.dm_db_resource_stats I see that MAX(avg_cpu_percent) is about 25% and the other params are less.

So the question is - Why my query takes 3-4 sec to exec? It can be executed in 1 sec. And server does not use all my DTU. How to make server use all available resources to exec queries faster?

Anton T
  • 11
  • 2
  • 1
    You should EXPLAIN PLAN and see what the query optimizer comes up with for a query plan. If you see TABLE SCAN, you should add indexes before more threads. I'd optimize for a single thread before parallelizing. – duffymo Mar 16 '17 at 12:02

4 Answers4

1

DTU is a combined measurement of CPU, memory, data I/O and transaction log I/O.

This means that reaching a DTU bottleneck can mean any of those.

This question may help you to measure the different aspects: Azure SQL Database "DTU percentage" metric

And here's more info on DTU: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu

Community
  • 1
  • 1
Bernard Vander Beken
  • 4,848
  • 5
  • 54
  • 76
  • 1
    According the statistics I don't reach any limit ( CPU, memory, data I/O and transaction log I/O.) But I can't reach the PC speed execution.... – Anton T Mar 16 '17 at 13:41
1

On my PC I can execute the query in 1 sec

We should not be comparing our Onprem computing power with DTU. DTU is a combination of CPU,IO,Memory you will be getting based on your performance tier.so the comparison is not valid.

How to make server use all available resources to exec queries faster?

This is simply not possible,since when sql runs a query,memory is the only constraint ,that can prevent the query from even starting.Rest of the resources like CPU,IO speed can increase or decrease based on what query does

In summary,you will have to ensure ,queries are not constrained due to resource crunch,they can use up all resources if they need and can release them when not needed.

You also will have to look at wait types and further fine tune the query.

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • "the comparison is not valid" - OK. But as we can see on my example execution speed depends on DTU. More DTU - more speed(the same information in the documentation). I have 75% free DTU and can't reach simple-PC execution speed. May be there is a big internal difference between DB in Azure and DB on PC? May be there is any information about it? I would be grateful for the links. – Anton T Mar 16 '17 at 14:03
  • If your query is running long in azure,then you must be hitting some limit(any one among cpu,ram,IO).see this link for more understanding on DTU.https://learn.microsoft.com/en-us/azure/sql-database/sql-database-benchmark-overview – TheGameiswar Mar 16 '17 at 14:20
  • 1
    further your on prem pc may have more RAM and can cache the query plan or keep the data in buffer for more point of time.So your query must have returned results quickly – TheGameiswar Mar 16 '17 at 14:22
0

As Bernard Vander Beken mentioned:

DTU is a combined measurement of CPU, memory, data I/O and transaction log I/O.

I'll also add that Microsoft does not share the formula used to calculate DTUs. You mentioned that you are not seeing DTUs peg at 100% during query execution. But since we do not know the formula, you may very well be pegging components of DTU, but not pegging DTU itself.

Azure SQL is a shared environment, and each tenant will be throttled to ensure that the minimum SLA for all tenants

Rob Reagan
  • 7,313
  • 3
  • 20
  • 49
0

What a DTU is is quite fuzzy.

We have done an experiment where we run a set of benchmarks on machines with the same amount of DTU on different data centers. http://dbwatch.com/azure-database-performance-measured

It turns out that the actual performance varies by a factor of 5. We have also seen instances where the performance of a repeated query on the same database varies drastically.

We provide our database performance benchmarks for free if you would like to compare the instance you run on your PC with the instance in the azure cloud.

Espen Brekke
  • 404
  • 3
  • 7