SQL Server Timeout only on production
I have an API endpoint that runs a query. For some reason, when I hit the production endpoint the query times out (after 2 minutes) but when I hit the same endpoint locally with my local environment connected to the same production DB the query runs in a couple of seconds. Is there a reason why this might happen? The issue is definitely not a connectivity issue because when I hit the same endpoint (on prod) with a different token it doesn't timeout. It's only some tokens that timeout on prod (yet when I run it locally connected to my prod DB it runs in a couple of seconds)
Assuming your prod environment is setup with AG listener scaled across multi subnet, with such multi subnet setup the intermittent timeouts would happen without https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-6.0#:%7E:text=Always%20specify%20multiSubnetFailover%3DTrue setting as part of your connection string.
Also, setting appropriate values for https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15#RegisterAllProvidersIP and https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15#HostRecordTTL will help controlling the default behavior, especially with environments that are setup with multi subnet.
When RegisterAllProvidersIP = 1, any clients whose connection strings do not use MultiSubnetFailover = True, will experience high latency connections. This occurs because these clients attempt connections to all IPs sequentially. In contrast, if RegisterAllProvidersIP is changed to 0, the active IP address is registered in the Client Access Point in the WSFC cluster, reducing latency for legacy clients. Therefore, if you have legacy clients that need to connect to an availability group listener and cannot use the MultiSubnetFailover property, we recommend that you change RegisterAllProvidersIP to 0.
Hope this helps and resolves your problem.