Azure SQL Database Connection Delay

Asiful Nobel 196 Reputation points
2022-01-18T20:30:31.213+00:00

I am running an Azure Functions app on an Isolated app service plan. The app service plan is hosted inside an App Service Environment. The app makes database calls to Azure SQL on Business Critical pricing tier. Usually the database calls return response fast as in within milliseconds, but sometimes it takes 14 to 28 seconds for the app to establish connection (through profiling) to the database. According to the app insights, around 10% of the requests have this amount of delay. I do have default connection pooling and there does not seem to be any connection leak anywhere either. App service plan has service endpoint enabled too according to teammate. But this delay still happens.

My question - is there anyway to decrease this connection establishing duration or is this normal even for Business Critical pricing tier? I have thought about reducing the Connection Timeout with increased ConnectionRetryCount and ConnectionRetryInterval, but docs advise against having the Connection Timeout less than 30 seconds.

Azure SQL Database
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,336 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,828 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,901 Reputation points MVP
    2022-01-19T03:24:22.613+00:00

    Please make sure your database connections are not getting throtled. When throttling occurs you usually see connection timeouts and poor performance.

    The following query tells you when you need to scale up. 

    SELECT     
(COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent',
(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent',
(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats
--service level objective (SLO) of 99.9% <= go to next tier

    When avg_log_write_percent is at 100% or near 100% then throthling occurs.

    In addition, verify you connect to Azure SQL Database specifying "Encrypt=Yes" to speed up the connection.

    Another thing you need to consider is that functions go into a "cold" state after five minutes of inactivity except when using premium plans, and when they come out of the cold state you can expect up to 10 seconds. Some performance issues have been reported when functions load large external libraries, so try to consolidate them in one file.