March 31, 2021 at 10:26 pm
I never came across this issue before. Application connecting to the listener is taking approx 20 - 25 secs Vs connecting to server directly is less than 2 secs. Would this be network latency issue or something else?
April 1, 2021 at 7:30 pm
I would load up extended events or profiler to see what is happening on the database side. What queries are being sent during the time the application is connecting to the listener? Are they being "slow" to process on the SQL side or the application side?
Next, I'd double check in the code that it is ACTUALLY the connection to the listener that is taking 20-25 seconds. Does the application work faster if you comment out the code to make that connection?
Is the application connecting via a DNS name, a SQL Alias, IP and port, etc? The fewer lookups it needs to do, the faster it should connect. Mind you a DNS or SQL Alias lookup SHOULD be quick, but IP and Port will almost certainly be quicker. I expect you'll only shave off a few fractions of a second with that change, but if your DNS is slow, it may help (assuming you are using DNS lookups).
Another things you could try to remove the "network latency" is set up SQL Server on the local machine and have the application connect to localhost or 127.0.0.1. This will remove network latency as being a problem.
But basically, with what you have given us, there are a near infinite number of things that could be the problem. 20-25 seconds of network latency is INCREDIBLY high and in my experience would not be the problem (especially if connecting to the server directly is less than 2 seconds). A more likely problem would be blocking (if it is at the database side) or processing on the application side of things.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 6, 2021 at 11:00 am
In addition to the advice above. One thing that might be worth checking, if you haven't done already, is pinging the DNS name and see whether it returns the IP address you expected.
Depending on your setup I think it's possible that the listener could be pointing to a different instance. I doubt it would explain 20-25 seconds but it might explain part of the delay especially if the other instance is in a different network location.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply