1

Usually when connecting to our SQL Server 2005 clustered instance it takes less than 20 milliseconds, but sometimes it can take much much longer, 7-30 seconds.

What are the potential bottlenecks in creating new connections?

3

Could be a number of things:

  • Server's temporarily run out of memory so can't create a new login
  • Server's temporarily run out of threads so can't create a new login
  • All CPUs are heavily loaded with other activity so they can't process the login
  • The IO subsystem is overloaded so the server can't read the necessary metadata to find out the connection's security, default database etc
  • The network is overloaded so the round-trip time to the server and an ack back to the application takes longer than usual
  • There could be a logon trigger that has a variable run-time depending on any number of factors, including all of the above
  • The database you're trying to connect to may still be in the process of coming online
  • There could be corruption in the database you're trying to connect to causing a page IO latch wait (long-running IO)
  • There could be a locking contention issue with something the application or logon trigger does

You might find some errors in the SQL Server error log about any of the above, or have to do some more digging with DMVs to see what's eating resources.

Hope this helps!

1
  • Never figured out the problem with 2005, we upgraded to 2008 shortly afterwards and it went away. But when we ran into the problem again it was threads. A bad execution plan caused poor parallelization.
    – rjdevereux
    Apr 14 '11 at 20:30
0

In addition to Pauls suggestions above, I would add

  • Check that the machine isn't swapping heavily.
  • If your environment authenticates connections based on PTR records, is there a delay in name resolution ?

When clients fail to authenticate swiftly, do you notice any other symptoms on the server, most likely a drop in query processing on existing connections ? If so I would look for underlying causes, possible software or hardware corruption, like a failing disk.

1
  • If you're using Windows Auth via NTLM, does the response time of the domain controller come into things?
    – mwardm
    Apr 14 '16 at 14:38

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.