Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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!

share|improve this answer
    
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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.