23

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?

Improve this question
4
  • 1
    How long is "so long"? 2 seconds, 20 seconds or 200 seconds?
    – Bevan
    Commented Jul 24, 2009 at 1:15
  • Do you have any odd plugins? Every server I try to connect to takes under 2 seconds on my work network.
    – Bob King
    Commented Aug 4, 2009 at 20:38
  • @Bevan for me, minutes to connect to localhost... it's truly frustrating. (YMMV) And it started at some recent point in time, it was never like this before.
    – ANeves
    Commented Nov 6, 2012 at 19:13
  • @Bevan, for me it takes 50 seconds to start. When I click the icon until something shows up it is 50 seconds which in my opinion is way too much time.
    – FabianVal
    Commented Sep 18, 2018 at 15:02

16 Answers 16

Reset to default
20

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.

Improve this answer
2
  • 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
    Commented Aug 25, 2009 at 14:22
  • In my case, the server wasn't connected to the Internet so it couldn't perform any security check, so it didn't change much, except after that I didn't have to wait for the timeout :). But I agree this is not a good idea on a server that can perform this check.
    – Julien N
    Commented Feb 3, 2014 at 17:29
18

This worked for me: Microsoft SQL Server Management Studio is too Slow

In a corporate environment it is not uncommon for Microsoft SQL Server Management Studio (SSMS) for SQL Server 2005 to take over 45 seconds to start as well as lags and delays when opening various windows and dialog boxes from within the application. If you are experiencing this issue, then a quick fix is to add an entry in your HOSTS. file that points crl.microsoft.com to 127.0.0.1

  • Exit SSMS
  • Press the keys [Win] + [R]

  • Enter the following..

    notepad %systemroot%\system32\drivers\etc\hosts.

  • Append the following..

    127.0.0.1    crl.microsoft.com

  • Save the file.

  • Start SSMS (ah! much better)
Improve this answer
5
  • My god, it's bizzare...
    – Alex Zhukovskiy
    Commented Jul 4, 2016 at 15:44
  • 4
    Since crl stands for "Certificate Revocation List", this is also non-ideal from a security point of view (see the comments on the accepted answer).
    – Heinzi
    Commented Jun 26, 2017 at 10:00
  • For me this seems to have taken the delay from 30++ seconds down to < 5 seconds, but still slow.
    – tbone
    Commented Aug 27, 2017 at 18:14
  • People have it so wrong... Business practices are not the reason to hate Microsoft. This is the reason, this right here... this and all the other arcane nonsense to get just about any standard application running well on their stack.
    – Chris Hatton
    Commented Aug 14, 2020 at 5:44
  • Trying this in early 2021 got the time down for me from 30 seconds to 7 seconds, on a superfast server. I still think its disgustingly slow, this is on a new/big server with huge processing power, SSD disks, 128GB ram and 10 gigabit internet connection. I suspect that MS is phoning home to send your data back since there's really nothing else that would take so long other than waiting for someone else's server.
    – user2728841
    Commented Jan 27, 2021 at 16:59
3

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

Improve this answer
1
  • 1
    So Microsoft recommends running it on a 32-bit OS. Not exactly a practical suggestion. rolling my eyes
    – mhenry1384
    Commented Mar 28, 2018 at 14:03
3

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.

Improve this answer
3

SSMS is attempting to check with Microsoft for the default root certificate, even though it does not us it. If you are using a proxy server use either proxycfg or netsh to set the system proxy settings. Close and then open SSMS and the time to load will be greatly reduced.

Improve this answer
2

Adding to Julien N 's answer above, I fixed the problem (a 10-second delay when running a query the first time) by un-ticking:

"Check for Publisher's certificate revocation"

in Internet Explorer advanced options. No restart required.

Improve this answer
2

For me, the problem was that my machine name was longer than is allowed by NETBIOS (15 characters). Once I shortened it, navigating SSMS became lightning fast.

Windows warns you if you try to set your machine name to longer than 15 characters -- I guess I should have heeded the warning!

Improve this answer
2

The solution for me was to disable the Hyper-V NIC:

enter image description here

Simply right-click and then click disable

Update 2021: Problem came back. This time it was a reverse DNS entry that solved it.

Improve this answer
1
1

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.

Improve this answer
2
  • 1
    This happens to me with both SQL Server 2005 and 2008.
    – Andrew J. Brehm
    Commented Jul 20, 2009 at 13:21
  • ...and all the way up to SSMS 2017.
    – rory.ap
    Commented Apr 26, 2019 at 12:56
1

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).

Improve this answer
1
  • No. It connects to ".". The servers in question are 64 bit and have 8 GB and more.
    – Andrew J. Brehm
    Commented Jul 27, 2009 at 8:43
1

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

Improve this answer
1

In my case it was not every time, but when it did, it would take 10 min to get back even my connection box. So I looked at Configuration manager and disabled shared memory. Works like charm every time !!

Improve this answer
2
  • 2
    A little more detail such as which version, navigation from the app to this setting, etc. would be helpful.
    – Vomit IT - Chunky Mess Style
    Commented Aug 16, 2016 at 0:38
  • This worked for me. To do this run the SQL Server Configuration Manager and disable "Shared Memory" under Network Configuration / Protocols
    – Mike Jerred
    Commented Dec 5, 2017 at 10:45
0

I was having kind of same problem where Views folder was taking long time to expand. Other folders including databases or tables were OK. All of the recommendations above didn't work in my case. What worked for me is the user had sysdamin role, I removed the sysadmin role and give it back and everything start working fine for me.

Improve this answer
0

yups this worked for me. running from an elevated cmd prompt: reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32

Improve this answer
1
  • 2
    What are side effects of this?
    – gronostaj
    Commented Oct 23, 2020 at 10:47
0 
reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32

Try this on admin cmd and agaiin try to install.

Improve this answer
-2

If you're using SQL Express, check that the total size of your databases does not exceed 10 GB (limitation for SQL Express 2012).

Improve this answer
1
  • 4
    If the size exceeds the limitation, would the result be just slow performance?
    – fixer1234
    Commented Nov 12, 2015 at 17:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .