locked
Azure SQL very inconsistent performance RRS feed

  • Question

  • I am trying to convince my team to migrate our production DB from SQL on an Azure VM to hosted Azure SQL DB. However, during my initial testing, I am finding extremely inconsistent performance results. For example, a simple Select query on a table with about 200k rows can take anywhere from 2 seconds to 2 minutes. I have tried with and without indexes. The same query on the VM takes about 1 second or less consistently. 

    Why is the performance all over the place like that? What can I do to get consistent results? I have tried troubleshooting based on online guides I found, and even tried scaling up to an instance of Azure SQL with 32 GB of RAM, but it made no difference. The results are always extremely inconsistent.

    Thursday, November 29, 2018 7:26 PM

Answers

  • Hello,

    How you determined the tier that is required to have equivalent performance as the IaaS environment? Let’s do some DTU sizing and collect some days of performance counters data on the IaaS VM and provide it as input to the DTU calculator. I am pretty sure you will find you need to adjust the database tier as soon as you see the report/graph of the DTU calculator. The inconsistent performance you see may be related to throttling.

    http://dtucalculator.azurewebsites.net/


    When processing a good number of rows, it is recommended you do it using batching techniques for best performance.


    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-use-batching-to-improve-performance


    Moving to PaaS may require some adjustments to your application or database programing but it can save a huge amount of money for your organization. Do not get discourage quickly.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com





    Friday, November 30, 2018 1:15 PM

All replies

  • That is weird, we host some Tier1 databases with high transactions in Azure SQL database and have not see performance being inconsistent. 

    How wide is the dataset that the query is returning? Do you have express route or querying over the internet? In order to eliminate the data transfer latency, try running the query using query editor or a VM in the same data center you have setup the Azure SQL database.

    Thursday, November 29, 2018 7:42 PM
  • I have not seen this kind of behavior either. When you run the query, do you see differences in the execution plan from one run to the other? Are you possibly seeing contention from other users? Are you maxing out the service tier? There has to be something. Azure SQL Databases more or less just work. Except for some kind of network issues, I've never seen abnormal behavior from Azure SQL Database.
    Thursday, November 29, 2018 8:50 PM
  • We tried from a VM in the same region as the SQL instance (East US region). Does it have to be in the same VNet? We were also testing from SQL Studio, and it is imperative that we be able to work with SQL Studio when querying this database.

    If you think it will make a big different to try from the same VNet then I'll test it out...

    Friday, November 30, 2018 1:22 AM
  • Hello,

    How you determined the tier that is required to have equivalent performance as the IaaS environment? Let’s do some DTU sizing and collect some days of performance counters data on the IaaS VM and provide it as input to the DTU calculator. I am pretty sure you will find you need to adjust the database tier as soon as you see the report/graph of the DTU calculator. The inconsistent performance you see may be related to throttling.

    http://dtucalculator.azurewebsites.net/


    When processing a good number of rows, it is recommended you do it using batching techniques for best performance.


    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-use-batching-to-improve-performance


    Moving to PaaS may require some adjustments to your application or database programing but it can save a huge amount of money for your organization. Do not get discourage quickly.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com





    Friday, November 30, 2018 1:15 PM
  • How are you measuring the performance? Are you using Query Store or Extended Events to get a precise measure or just from the client? If that last thing, network transfer could be the entire explanation.
    Friday, November 30, 2018 1:53 PM
  • No I simply tested from SQL Studio - the performance of running against a local instance vs the Azure SQL instance. I tested it from a VM in the same region as the SQL instance. How could I get a better idea of the actual performance?
    Sunday, December 2, 2018 3:28 AM
  • Hello,

    Use the DTU calculator as I mentioned previously. Please collect the performance counters required by the DTU calculator. It will tell you the service tier required by your workloads.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    Monday, December 3, 2018 2:31 AM
  • The problem is that Azure SQL instances have to be "warmed up".

    Even if the instance has 32gb of RAM, it is created with a much smaller amount, maybe 4gb, and will grow slowly if the database is kept active.  And then, unlike a real SQL Server instance, if it is left idle for any length of time it starts to give the memory back!

    Once it is warmed up and kept active, the performance should be consistent enough.

    Of course Azure doesn't make this plain to you, but if you fiddle enough with the appropriate DMVs you can see what's happening.

    Josh

    Tuesday, December 4, 2018 3:59 AM
  • And then, unlike a real SQL Server instance, if it is left idle for any length of time it starts to give the memory back!

    This is IMPORTANT for differences in behavior

    * I cannot point to the reason for this specific case without more information and you should start by monitor resources like Monitor Memory Usage

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, December 14, 2018 10:29 PM