9

Azure Web Role and Sql Azure Latency

Hi Just to know that there is a latency and timeout between the web worker role and SQL Azure , Event a Timeout at some times (These a are not random having frequently ) 40% of 100 pings does not have 0ms timeout

if Web worker role and SQL Azure in the same data center WHY there is a timeout as they are communicating using their internal network

Pls Refer the attached screenshots :

enter image description here

Application which runs on this web worker role has a mysteries performance ups and downs .. if may be due to various reasons but what i need to know is that does theses statistics on latency and timeout affects web application performance ?.

miken32
  • 42,008
  • 16
  • 111
  • 154
Sudantha
  • 15,684
  • 43
  • 105
  • 161

2 Answers2

16

I posted this on another thread but it's old and has been closed. I think that it highlights some of your issues.

I've been attempting to move our business app to the cloud. Considering that our onsite servers are 8+ years old there should be a notable improvement with Azure services. However when we tested our app and benchmarked the cloud versus onsite we noticed that there was almost 3X more latency in the cloud then onsite (8+ year old servers) overall and 20X more latent when you compare them using modern equipment. Our app is an asp.net app and the DB is about 11 GB in size.

SQL Azure and Performance

  1. Never use a pooled connection in the cloud. If you do, you will have your queries dropping left right and center. Instead open one connection and keep it open until you are done.
  2. Use caching. You don't have a choice if you have a hope of making this work. I have one site successfully in the cloud but I had to use caching to get any reasonable performance out of it.
  3. Realize that it's not your fault! The Azure team needs to fix their end more than you do yours. We have a lean app, we've been upgrading, tweaking and optimizing for 10 years and if we can't make it work then you won't either.

I like Azure as a concept. I like the options. I like the expandability, but I don't like the performance. I hope Microsoft pays some more attention to this and makes some changes because no one should move their business there until this has been fixed.

Tests

Tests are done by running a series of queries that access the exact same data and are done in code via a custom function that measures the response time from the creation of an object to the time it's disposed (forced to write to the DB). This object wraps the code that's being tested.

No caching was enabled for the test however I did allow the code and DB to execute it once before and took the best results so that the DB server has the opportunity to optimize the query and so the web server could load the assemblies properly.

Test 1 - Web and DB on same good machine

  • Quad core 2.5GHz, 8GB Ram @ 800Mhz with 1300 FSB and SQL 2005
  • yields 290 ms response time.

Test 2 - Web and DB on same machine

  • SQL and Web on 2 Proc (dual core 3.0GHz), 16GB Ram @ 200Mhz with 200 FSB and SQL 2005 Really old IBM Server.
  • Web and SQL both are local to each other
  • yields 656 ms response time.

Test 3 - Web separate from DB

  • SQL on 2 Proc (dual core 3.0GHz), 16GB Ram @ 200Mhz with 200 FSB and SQL 2005
  • Web on 1 Proc dual core 3.0GHz, 8GB Ram @ 200Mhz with 200 FSB
  • Really old IBM Server.
  • Web on one machine and SQL on the other.
  • yields 796 ms response time.

Test 4 - Azure

  • Medium VM on Azure
  • SQL Azure DB
  • yields 3,174 ms response time.

Conclusions

  • The difference in latency for me when moving from a one server scenario to a two server scenario was 140 ms.
  • Moving from that scenario to Azure was 2,518 ms. that's 17.98 times worse performance than on my 8 year old machines.

Don't do it until they fix this and take the time to let them know that it's an issue for you as well.

Middletone
  • 4,190
  • 12
  • 53
  • 74
  • Have you run that test again, recently? Would be interested in an update! – Sam7 Jan 16 '14 at 09:20
  • 1
    I haven't run the test again as we ditched it in favor of a VM hosting SQL. I'd recommend that route for most applications since you have a good combination of control and features. There's value in understanding the mechanics of your database that SQL Azure won't give you. – Middletone Jan 27 '14 at 19:45
  • Is the performance better when running a VM hosting SQL Server vs SQL Azure? – kbmax Sep 19 '14 at 00:30
  • At the time of the testing a VM was far better. I suspect for many applications a VM will be far superior, but if you are not familiar with how to run and manage your own sql server instances, then SQL Azure has the benefit of removing the configuration side of things for you. – Middletone Sep 24 '14 at 14:08
  • I use Sql Azure for years and I experience no more than 200 ms in end-to-end call - ui-api-sql-api-ui. I do not know how are you able to get to 2.5 seconds. By looking at Test 2 Web and DB on same machine, 656 ms for code running on the same machine - I humbly suggest that there may be a problem with the code rather than the performance of sql azure. – Ognyan Dimitrov Feb 27 '19 at 11:10
  • 200ms is brutal. You need <1ms latency for most apps to run effectively when under load. If your query takes 30 seconds to run, sure, no one cares, but when you process thousands a second that's a big problem. @OgnyanDimitrov – Middletone May 18 '20 at 16:34
8

First of all, you need to know that Windows Azure SQL Database is a multi-tenant, high-density RDBMS offered as a service. That means, that a single server is used by possibly hundreds of customers.

I also suggest that you get to know the SLA for the services, Windows Azure SQL Database in particular. Noone has ever claim that there will be 0ms latency. There is also such thing as "Transient Conditions" in WIndows Azure SQL Database.

A good suggested reading is the Windows Azure SQL Database Performance and elasticiy guide.

As for web application performance, after reading the Performance and elasticity guide, I don't think the 200ms that happens once in a while are the core bottleneck.

UPDATE after the first comment

You will always have ups and downs in a shared environment. You should also expect query execution times to change up and down. This is unavoidable in that kind of environment and something you have to design for and live with. There is no magic wand here, and there is no dedicated server for you (for us) in the case of Windows Azure SQL Database. If you think that you app needs more reliable SQL Server services, you might try the Windows Azure Virtual Machines and bring up a SQL Server cluster by yourself. I guess ( and this is only a guess) that the communication between your cloud service and your VMs, given everything is in the same availability set, will be more predictable.

Update after 2nd and 3rd comments:

Well, yes, you might have licensing issues (I'm the expert on licensing). Is the ticket you have opened for the ups and downs? If so, you may try to escalate it (don't know how, but you have your ticket ID, you must also have an assigned engineer and an e-mail about the ticket - reply-to-all to that e-mail). Also, when you created the ticket there must have been a small questionnaire to reflect the business impact of your issue. Then a usual response time must have been assigned to the ticket. If the support has not come back to you within that time, you can definitely escalate it.

UPDATE

Interesting observation that I have is that in all your screenshots only the first packet is delayed, then every consecutive has 0 latency. In all the samples you are providing. If this is your case 10 out of 10 times, then you are definitely not having any issues with latency. I will suggest that you use the "-t" option in the regular ping to send more than 4 packets and observe. I suggest breaking at around 100 packets, and then observe the results. I would not take into account 4 packet samples, where only first one has latency for any performance reviewing.

astaykov
  • 30,768
  • 3
  • 70
  • 86
  • Thanks but we went though the elasticity guide and applied what needed but still .. this issue is random up and downs in performance – Sudantha Jul 22 '12 at 13:12
  • thanks @astaykov for the detailed answer , still i don't think bring up our own sql cluster wont work as its gonna make licensing issues + i think we have to maintain ourselves too , we have opened a support ticket in Microsoft still no reply after 3 days – Sudantha Jul 23 '12 at 10:52
  • + we have a issue of recent timeouts to the SQL server – Sudantha Jul 23 '12 at 10:53
  • Thanks for the answer ! .. will get back to ur with the response of microsoft technical support ,, – Sudantha Jul 24 '12 at 03:37
  • 1
    the bottom line is, if it's performance is bumping up and down, not predictable, probably not many people will need something like this. then, multi-tenant actually becomes an excuse of being bad performance. – liuhongbo Nov 12 '14 at 03:06