DTU estimation


  • Hi,

    I am trying to estimate the number of DTU required for my Microsoft Azure SQL Server.

    For the moment I am not able to use the DTU calculator tool as I have not yet finished to implement the whole infrastructure.

    However, our clients demand a quotation for our service and I am not quite sure on what to tell them yet. I want to make sure to don't lose money as well as to not overprice them.

    Each of our machine will continuously transfer approximately 45 records (some datatype Real and some datatype String) per minute from one Siemens HMI KTP400 panel to one SQL database located in a Microsoft Azure SQL Server.

    I wish to know if it is possible to estimate the number of DTU required to transfer from 1000 different machines to that SQL Server.

    1000 x 45 records per minute from 1000 different connections to the server.

    Thank you

    Fabio Baldini

    miercuri, 16 mai 2018 08:38

Toate mesajele

  • Ciao Fabio,

    Without running an example workload and collecting perfmon statistics and analyzing the resulting .csv data with the DTU calculator, there is no absolute way to estimate which pricing tier is needed. I could estimate that your customer will need Premium P4 or P6. It is best to run an example workload to be sure.

    I have provided some additional information in regard to pricing and DTU estimation. 

    How can I determine the number of DTUs needed by my workload?

    If you are looking to migrate an existing on-premises or SQL Server virtual machine workload to Azure SQL Database, you can use the DTU Calculator to approximate the number of DTUs needed. For an existing Azure SQL Database workload, you can use SQL Database Query Performance Insight to understand your database resource consumption (DTUs) to gain deeper insight for optimizing your workload. You can also use the sys.dm_db_ resource_stats DMV to view resource consumption for the last hour. Alternatively, the catalog view sys.resource_stats displays resource consumption for the last 14 days, but at a lower fidelity of five-minute averages.

    Using the Azure Pricing Calculator

    Azure SQL Database Query Performance Insight

    Managing and tuning the performance of relational databases is a challenging task that requires significant expertise and time investment. Query Performance Insight allows you to spend less time troubleshooting database performance by providing the following:

    • Deeper insight into your databases resource (DTU) consumption.
    • The top queries by CPU/Duration/Execution count, which can potentially be tuned for improved performance.
    • The ability to drill down into the details of a query, view its text and history of resource utilization.
    • Performance tuning annotations that show actions performed by SQL Azure Database Advisor
    miercuri, 16 mai 2018 16:12
  • 1,000 connections is a significant load even if they are individually modest.

    If all they do is dump rows into a minimally indexed staging table, with minimal database work even to validate foreign keys, then it might all work in a single Azure SQL database, otherwise you're talking an average 700 rows/second which might just have peak activity over 1000/second, and you'd want to do some proof of concept on that before committing to the idea that Azure SQL can handle it at all.

    Now you can always shard this out, all the way down to each customer/connection going to a small private database, all the databases grouped into one elastic pool.  That is actually an architecture that was anticipated for Azure SQL to support.  Or perhaps 10 to 50 writing to a smaller set of P1 shards.

    Or even have the app log the data to sequential files first and then import the rows periodically, so any delays would never hold up continued operation.


    • Editat de JRStern miercuri, 16 mai 2018 23:23
    miercuri, 16 mai 2018 23:22
  • Hi Mike,

    Thanks for the reply. I imagined that this was a tricky question and without a workload example would have been difficult to provide a complete answer.

    Knowing that your estimation is for a Premium P4 or P6 will surely help me to roughly estimate the costs.

    I'll make sure to use your links as a guideline while testing and monitoring my workload.

    Thank you


    sâmbătă, 19 mai 2018 06:36
  • Hi Josh,

    Thanks for your reply.

    What was worrying me was, as you mentioned, the worst case scenario where all the 1000 machines will transfer the data simultaneously to the database.

    Even if it is pretty much unlikely that the 1 minute timer that I use on each PLC to transmit the data will tick simultaneously on all the 1000 machines, I still wanted to have that situation somehow covered.

    I like your suggestion to shard down to small private databases grouped in one elastic pool.

    I'll look into it.

    Thank you


    sâmbătă, 19 mai 2018 07:09