SQL Azure very slow RRS feed

  • Question

  • Hi Experts

    We have SQL Azure database that is running very slow, so i took a backup of the database and restored on my on premise sql server express edition. 

    I tried a update statement on a logs tables that have approximately about 1,062,367 records.

    UPDATE logs set FileId = NULL
    where LEN(FileId) < 36

    The above update statement updates about 910,593 records on localhost host in 20 sec and if i run the same updates statement on SQL Azure it took 40mins and 16sec. The update statement was issues using SQL Server management studio installed on my local machine. With reference to internet connection, we have fiber connection with download speed of 70.62mbps and upload speed of 84.09mbps 

    I am not sure whats going wrong with SQL Azure database i.e. could there be a specific fault on my database at Microsoft side etc.

    Any advice or suggestion will be highly appreciated.

    Kind Regards



    Friday, December 5, 2014 10:34 AM


All replies

  • hi Bhavesh,

    I do not think it would be internet connection that may be causing it but to eliminate it completely, I would say run the script using portal, see if it makes a difference.

    To further troubleshoot, could you let us know what service tier and performance level are your sql azure database? in any case i dont think it should take 40 min. lets see if we can figure it out.


    Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

    Friday, December 5, 2014 3:13 PM
  • Hi Vishal

    Thanks for your reply.

    Its a the basic service tier and performance level is set to Basic (5 DTUs)

    Let me know if you require any further information.


    Friday, December 5, 2014 3:25 PM
  • I have waited for 40 min for the update to complete and it has successfully and updated logs table so i can't re-run the update statement again because there will be no records to update.

    I have managed to find another example that took about 10 mins to complete on the SSMS but when ran the same query on the azure management portal it took only 6 secs which puzzled me. 

    Here is the query: 

     l.Value3 as ClientReference,
     l.[Key] as ActivityUser,
     l.Value2 as FormDisplayName,
     l.Value6 as Status,
     l.DateStamp as LastUpdated,
     l.[Action] as FileDescription,
     l.CompanyId as ClientID,
     l.FileId as FileID,
    from Logs l
    LEFT OUTER JOIN files f on f.Id = l.FileId 
    where l.AccountId = 578
    and type = 1

    The above query returns about 3694 records only.

    Here is the details of the query performance 

    Azure Management Portal

    • Duration(ms): 6487
    • CPU(ms): 2443
    • Logical Reads: 74729
    • Physical Reads: 66147
    • Logical Writes: 0


    • Duration(ms): 615259
    • CPU(ms): 3666
    • Logical Reads: 74729
    • Physical Reads: 71370
    • Logical Writes: 0

    Any update will be appreciated.


    Friday, December 5, 2014 4:33 PM
  • hi Bhavesh,

    no clue why that might be happening, do you still see the issue? please let me know and if you still see it I would escalate it to SQL team to check it.


    Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

    Monday, December 8, 2014 6:38 PM
  • Hi,

    Any updates on the above issue ?



    Wednesday, December 10, 2014 9:24 AM
  • Hi Vishal

    It is still happening so please escalate the issue to SQL Team and let us know.



    Wednesday, December 10, 2014 10:37 AM
  • thanks Bhavesh, I will reach out to them today, is there an email where you can be reached to provide any details if needed, you can email me at vishalishere [at] msn [dotcom]

    Mekh, Kindly escalate this to the right team, I will also reach out to SQL Azure team and give them heads up.

    Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

    Wednesday, December 10, 2014 12:42 PM
  • It would be helpful if you could turn on "include client statistics" from SSMS so that we can see the timing details.  You can do this from the "Query" menu in SSMS (shift+Alt+S).



    Friday, December 12, 2014 4:38 AM
  • Hi Tony

    Please find below client Statistics - this time the query took about 19 mins.

    Let me know if you require any further information.


    Friday, December 12, 2014 10:35 AM
  • Hi Bhavesh,

    You are mentioning above that you are using a Basic database. You might be constraint by the performance the Basic Service Tier offers. You can query the DMV sys.dm_db_resource_stats in the database which gives you one hour of history on your resource consumption to see if you hit the limits of the Performance Level. If you reach the limits, you should try to run the operation while the DB is in one of the higher performance levels, for example S1 or S2.

    If generally the performance is fine for you database in Basic, but you need to run more expensive maintenance operations once in a while, you should consider to upgrade the DB for the duration of these operations and then downgrade it back to basic afterwards.

    Hope that helps,


    Friday, December 12, 2014 4:50 PM
  • based in the stats it looks like you are spending most of the time waiting for the server to return the 1MB of data.  I would recommend that you upgrade to an S2 and rerun the query and see how much better the results are.  This would cost you $0.10 for 1-hour of testing.


    Friday, December 12, 2014 7:45 PM
  • Thanks for your reply

    I have updated the database to S2 and the query took about 1min and 26s.

    Why the query is faster when running on SQL Azure portal directly?


    Monday, December 15, 2014 10:54 AM
  • We are experiencing extremely low response time on East US SQL server. The latency has increased 10 times compared to normal.

    The database is in 'Web' mode.

    Is there any fix?

    Tuesday, December 16, 2014 7:16 AM
  • We have the same problems started with our SQL Database East US on 12/10/14. MS support cannot help, and it seems they even do not want to admit that the problem is on MS side, as they advise to rebuild and create missing indexes. I do not object against these actions in general, but this is irrelevant in this particular case, when during 1 hour the response times increased 10 times. Since that situation became even worse.

    Does anyone know is there any solution?

    Wednesday, December 17, 2014 3:02 AM
  • Hi nick_gonpav,

    Please have a look at this discussion about Azure SQL Database performance issue.




    Friday, December 19, 2014 7:24 AM
  • This is interesting, we also sudently started to experience performance issues with our production database around this day.

    It is true there are a lot of improvements we can do, but the fact is that this performance issue happened from one day to another. In our case, the workload is stable, so I still believe is not an optimization issue.

    It seems something in the infra structure has suddenly changed.

    Any news about this?


    .NET Software developer for industrial internet and automation system.

    Monday, December 29, 2014 10:54 AM