up vote 7 down vote favorite
1
share [g+] share [fb]

Considering that Microsoft SQL Server is not, technically, a slow database management system, I keep wondering why it often/usually takes such a long time for SQL Management Studio to connect.

This happens even when nobody else is accessing the database server and when Management Studio runs on the same machine as SQL Server.

Plus SQL Management Studio often takes a very long time to do something, even just displaying a login's permissions can take several minutes on database servers otherwise unused.

Is there some obvious explanation?

(Couldn't create an mssql tag. Please retag.)

link|improve this question
79% accept rate
2  
This possibly belongs on Serverfault – Diago Jul 17 '09 at 16:31
I've retagged... – RSolberg Jul 17 '09 at 16:34
Is there a reason for the sqlite tag? – TheTXI Jul 17 '09 at 16:47
Actually, it could be SF or SO depending on what he's using it for. Checking a login's permissions could go either way. – John Saunders Jul 17 '09 at 17:03
1  
How long is "so long"? 2 seconds, 20 seconds or 200 seconds? – Bevan Jul 24 '09 at 1:15
show 3 more comments
feedback

6 Answers

up vote 3 down vote accepted

This sounds stupid but I had a problem of a very slow SQL Server Management Studio and this trick solved it:

  • Open Internet Explorer
  • Go to Tools -> Internet option
  • Open the "Advanced" tab
  • Uncheck "Check for server certificate revocation (requires restart)"

This trick is only effective if the computer where your SQL Server Management Studio is installed is NOT connected to Internet. Only doing that solved my problem. Maybe it will solve yours.

link|improve this answer
Oddly enough this seems to improve things a bit. But the 32/64 bit issue mentioned below could also be right. I'll accept this answer and upvote the other. – Andrew J. Brehm Aug 25 '09 at 14:22
feedback
up vote 3 down vote

by default, when connecting to a local server, the network protocol used is shared memory. This can be quite slow sometimes.

I was experiencing the same speed issue when connecting to a similiarly specced box locally, but not remotely.

Do you get the same slowdown if you specify the TCP/IP network protocol when connecting?

EDIT Also, according to Microsoft, this may be an issue caused by Management Studio being 32bit and being run on a 64bit OS. See here: http://support.microsoft.com/kb/906892

link|improve this answer
feedback
up vote 2 down vote

Possible broken IPv6 implementation? Are you running dual-stack? We are and have had this problem before. IPv6 is tried first, and if there's any configuration errors it'll eventually fail to IPv4.

link|improve this answer
feedback
up vote 1 down vote

My colleague has the exact behavior as you on his PC. He was using SQL Server Management Studio of 2005. He solved installing the new version that comes with SQL Server 2008.

If you notice that this problem happen only on your PC I suggest you to try uninstall and re-install the whole Microsoft SQL Server suite.

link|improve this answer
1  
This happens to me with both SQL Server 2005 and 2008. – Andrew J. Brehm Jul 20 '09 at 13:21
feedback
up vote 1 down vote

Could this be a Name Resolution problem? If the SQL Server engine is on the same machine, try using (local) as the name or even 127.0.0.1 (or the IP address of the machine). Netbios over TCP/IP and/or deficient WINS/DNS can lead to slow connecting times.

Also make sure that the server has enough memory to operate. SQL Server can work well under 512 MB ram, but the performance degrades very fast.

There shouldn't be a delay longer than 5 secs when connecting to a server. (Less if local).

link|improve this answer
No. It connects to ".". The servers in question are 64 bit and have 8 GB and more. – Andrew J. Brehm Jul 27 '09 at 8:43
feedback
up vote 1 down vote

I added sqlservr.exe to the exception list of the windows firewall on SQL server 2005. Result: very quick respons!

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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