Sudden performance problems in Azure app <=> Sql Azure communication

Question

Hi to all,

we are running a Windows Azure hosted application for our customer. Starting from last saturday - 7th of April, our application suffers terrible lost of performance. After analysing the problem it seems, that the bottleneck are database queries comming from the Azure app to Sql Azure. Interesting is, that querying the Sql Azure database from outside - from my dev machine thousand miles away - gives back results as always - very performant. We tested it on a simple query - really simple select. From my machine the average time to execute is around 200ms, when we installed a linqpad on remote desktop of the Azure instance and tried it there, the results came in wide variety of times - from 40ms up to 15 seconds! We tried to stop an IIS there, to be sure the system is not overloaded by tasks from outside, because in last weeks we installed a bunch of new clients, but even that did not bring any improvement.

Is there anything what we could try to get back the performance we had before? Currently our application is unusable, with live clients outside...

Both the Azure application and database are located in Northern Europe area

thanks for any hint

Josef

Answer 1

Are you saying that the same SELECT query is returning results ranging from 40 ms to 15 seconds?  If so, you are right, that is certainly not normal or expected.

I would recommend opening up a support case at https://support.microsoft.com/oas/default.aspx?gprid=14919&st=1&wfxredirect=1&sd=gn so that someone in our Support organization can take a closer look at your specific clients and SQL Azure instance.

Evan

Answer 2

Here I send a code snippet used for testing, even a call for GetDate() takes over one or two seconds when executed on the instance running on Azure in 10 of 100 cases, the rest cases are executed almost immediately.

void Main()
{
 this.Connection.Open();
 try
 {
  var list = new List<Int32>();
  using (var cmd = this.Connection.CreateCommand())
  {
	cmd.CommandText = "select getdate()";
   foreach(var i in Enumerable.Range(0,100))
   {
	var t0 = DateTime.Now;
	cmd.ExecuteScalar();
	list.Add((Int32)DateTime.Now.Subtract(t0).TotalMilliseconds);
   }
  }
  list.GroupBy(r=>r).Select(r=>new{ElapsedTime = r.Key,Occurences = r.Count(), TotalTime = r.Count()*r.Key}).OrderBy(n=>n.ElapsedTime).Dump();
 }
 finally
 {
  this.Connection.Close();
 }
}

Answer 3

Thanks for tip, our customer, who is renting the Azure installation did so.

Here I was hoping to get some hidden tip. We are not sure what could be cause of such behavior, as I wrote already - there were installed some new clients last weeks, so if we reach some communication limits or what else that could be... All the performance counters looks good, low CPU usage, acceptable network traffic...

Answer 4

So, thanks for the right tip. Technicians of Microsoft support analysed the problem and everything seems to be solved by moving our web roles onto different node within the Azure hosting. There was some network problem and a lot of communication between the web servers and database was redirected, therefore slow. This was first time we had some problems and we were not sure, if this has something to do with growing load on the application, or if it is really an infrastructure problem.So I was looking here for some tips we could check, but the problem was there even when IIS services has been stopped, so...

Good experience is, that even in case of trouble one is not completely orphaned and some help can be found.