Azure SQL Server slow to open connection

azure

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

Best Answer

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.

Related Topic