0

After migrating one of our websites that uses Umbraco CMS (v6.2.6) from on-premise web and database servers into Azure, there have been huge performance implications for users in the back-office. I have tried scaling up the resources but this is not the issue as the DTU utilization only maxes out at 35%, even when bursting / spiking is seen. The SQL server is S0 standard service tier (10 DTU's) and the resource group that it sits in is S3 standard service tier (4 core / 7gb) - located US East.

I duplicated the azure app service and sql database and attached an instance of New Relic to it in Azure and the findings are a bit disturbing.

Call Trace #1 - 77% of the time is spent opening SqlConnections

If it makes any difference, I ran the same code / tests locally with a local instance of SQL server and the response times were much quicker - with little to no time being used to open the connection - Localhost StackifyPrefix Trace. I didn't write the data-layer code so adjusting it at this point is definitely out of the question, as cringe-worthy as it may seem...is there anything else that can be done to mitigate this? That seems like an absurdly long time to just open the SQL connection a bunch of times

  • Sorry for the mandatory question, but are these resources on the same datacenter? – Bruno Faria Nov 18 '16 at 19:46
  • @BrunoFaria Not a problem - Yes they are all in the East US, Virginia datacenter – user3267755 Nov 18 '16 at 20:00
  • Very strange indeed. Can you try another datacenter? I work with Azure for at least 5 years now and East US is simply unreliable. I don't use it for production anymore. Try East Us 2. About 2 days ago SQL database was having problems on this DC. – Bruno Faria Nov 19 '16 at 0:26
  • Did you use Server=YourServer.database.windows.net or Server=tcp:YourServer.database.windows.net,1433 in the connection string? – GregGalloway Nov 23 '16 at 1:32
  • @GregGalloway I use the latter, Server=tcp:YourServer.database.windows.net,1433 – user3267755 Nov 23 '16 at 16:37
2

The fix to this was in the connection string - in June of 2016, the Azure SQL Database portal showed this as their copy and paste connection string for ADO.NET

Server=tcp:{your db resource group name},1433;Initial Catalog={your db instance};Persist Security Info=False;User ID={your_username};Password={your_password};Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

At some point in the last 4 or 5 months, they removed Pooling=False from their connection string examples in ADO.NET but at this point I had just copied and pasted the connection string from one site into another because it already had the db creds / resource group names and all I had to do was change the actual catalog that it pointed to.

When Pooling=False is set, each time a connection is called with Close() or Dispose() in .NET, the connection is actually closed instead of being returned to the connection pool for later use. When a new connection is needed, the TCP 3-way handshake and communication between the client and database server must be established which can have big performance impacts on sites with lots of queries. By removing Pooling=False from the connection string, connection pooling functionality is automatically enabled by default with ADO.NET.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.