37

After upgrading both SQL Server and Management Studio from 2008 to 2012, Management Studio 2012 is very slow when new query windows and dialog boxes are opened.

Even right clicking on tables is slow. There is typically at least a 5 second delay before I can start doing anything within a window. This happens every time, even if I open the same window twice in a row. Why does this happen and how can I fix it?

Other applications establish connections to the database very fast.

Things I have tried that did not help:

  • A hit on Google where I'd need to modify my hosts file
  • Resetting SSMS "user-defined settings"
  • Updating video drivers, turning off hardware acceleration, disabling DirectX
  • Disabling the Biometric Authentication Service (I don't have it installed).

My computer should be more than fast enough, and I also have 16GB RAM. My hardware should definitely not be a problem. It looks like SSMS is waiting for something - I can operate other programs smoothly while this is happening.

I don't have the opportunity to install the SQL Server 2012 CU1 update because there seems to be some risk of errors installing hotfixes and I can't risk that right now.

Improve this question
1
  • I don't see this behaviour. If I did then to investigate further I might try (1) Using Process Monitor to see what the process is doing and if that gives any clues. (2) Using SQL Server Profiler to see if any of the queries sent from SSMS have high duration (e.g. blocking) (3) Attaching a debugger to the SSMS process and breaking into it during the hangs to see if the names of the functions in the call stack revealed anything.
    – Martin Smith
    Aug 25, 2013 at 17:27

22 Answers 22

Reset to default
13

SQL Server Management Studio Startup

When Microsoft's SQL Server Management Studio (SSMS) starts it tries to connect the Certificate Revocation List (CRL) of Microsoft:

http://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl

The underlying .NET components of SSMS are trying to contact the Certificate Revocation List and SSMS is unable to do so. This slows down the overall loading procedure. (15 seconds per certificate apparently)

Ok so here is what is happening. SSMS has a high percentage of managed code, all of this code is signed when we ship it. At start up (if this setting is checked) the .Net Runtime tries to contact crl.microsoft.com to ensure that the cert is valid(there were some fake certs issued in Microsoft’s name a while back so this is a very valid concern). If there is no internet connection or there is a problem contacting the certificate revocation list server then this will delay SSMS startup.

Reference: FAQ, Why does SSMS take 45s to start up? (MSDN Blog)

One issue that can cause this problem is that if the server does not have access to the internet, then the .NET framework can’t access the crl.microsoft.com website to verify that the digital signatures used to sign the binaries for managed applications are valid. Each certificate check has a 15 second timeout in the .NET runtime implementation. Depending on what features are installed, this can add up to a minute of startup time for Management Studio.

Reference: SQL Server Management Studio Startup Time (MSDN Blog)

Solutions

You can circumvent part of the issue, by downloading the certificate directly be entering the link into your browser and then importing the certificate to your certificate database

  • OR -

You can reconfigure your (company's) firewall to allow connections to Microsoft's CRL

  • OR -

You can reconfigure your personal antivirus/firewall to allow connections to the Microsoft CRL

  • OR -

You can configure your (company's) firewall to send a timeout faster to your client for requests accessing Microsoft's CRL.

  • OR -

You can configure IE to no longer "Check publisher's certificate revocation" in the advanced settings.

(See above mentioned blogs 1 and 2 for details)

Improve this answer
0
7

I have the same experience as you when I connect to Server Name "./". By dumb luck I found that I do not experience the performance issue when I connect to Server Name "(local)". There's probably an issue with the way SSMS 2012 resolves the server through "./".

Edit: I don't think it's related to SSMS since I can reproduce the slowness with ./ in an ADO.NET connection.

Improve this answer
0
7

It might have happened that SSMS has imported settings that can not be treated correctly anymore (like connections to some server or maybe some add-in settings).

There is a brutal way to "reset" user-defined settings of SSMS.

Close SSMS, go to folder:

C:\Users\YOURWINLOGINNAME\AppData\Roaming\Microsoft\SQL Server Management Studio\

You will find subfolder "11.0". Rename it to "11.0_", so you can always rename it back. Now start SSMS - it will re-create clean settings and maybe your problem will be solved. If not - delete newly generated "11.0" and rename "11.0_" back to "11.0".

This also might be a behaviour of some add-in, that got corrupted after upgrade. Add-ins register themselves as handlers for some commands or events and system might get unstable if they are missing/throwing exceptions.

Actually, v2012 uses another mechanism for registering add-ins, but some add-ins install themselves on all instances. To check, look into this registry key: 

[HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0_Config\AutomationOptions\LookInFolder]

You will find folders list there. Check all these folders - they must be empty (means you have no add-ins).

Improve this answer
0
4

I would guess the problem is that opening a new connection to your server takes 5 seconds. Why exactly this is the case I cannot tell and it would require a wire trace to analyze and diagnose. You should investigate what protocol(s) are being attempted and what protocol eventually succeed. It could be that your client attempts named pipes first (which depends on SMB) and then falls back to TCP (although the usual delay in such a case is much longer, around 20-30s seconds).

I would recommend you check the order of client protocols in the Client Network Configuration. Check the client used protocol by finding your own entry in sys.dm_exec_connections.

Improve this answer
0
4

Try disabling the antivirus and then check the performance for sql server 2012 management studio.

Improve this answer
1
  • 4
    What is the rationale for this suggestion?
    – Martin Smith
    Aug 25, 2013 at 14:38
4

I had the same problem with SSMS 2016. I used procmon to identify that the last thing it did before a several-second delay was make a network connection to a Microsoft IP address. I then tried turning off the Customer Experience Program by setting registry key HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft SQL Server\130 value CustomerFeedback from 1 to 0. After restarting SSMS it's working fast again. Presumably that doesn't always lead to slow new query windows but for me it did.

I don't know if that's what was going on with your SSMS 2012 but perhaps worth a try. Registry key will probably be 110 instead of 130.

Improve this answer
1
  • On my Server 2016, the path began with HKEY_LOCAL_MACHINE instead of HKEY_CURRENT_USER
    – DenverCR
    May 4, 2021 at 15:41
3

I have not personally experienced this issue, however there is a long thread (here) where many people have. The general advice seems to be to related to working with your video driver by either

  1. Installing an updated video driver -OR-
  2. Reducing or turning off hardware exceleration -OR-
  3. Turning off DirectX support

From the thread there was also this answer... "Windows 7 Clean Boot procedure revealed the service Biometric Authentication Service (DigitalPersona DPHostw.exe) was the problem. Disabling the service fixed the problem."

I would be interested to know if any of these approaches work in your case as well, as the thread I found relates to older versions of SSMS, i.e. 2005, 2008 , etc...

Improve this answer
0
3

I have the same problem and it turns out Trend Micro Office scan is causing the problem.

I tried everything described in several other similar threads about this problem with no avail. I figured i'll try disable the AV software.. Once Trend Micro Office scan is off, SSMS is lightening fast.

This didn't happen on Windows 7.

Improve this answer
0
3

I too was experiencing a 5 to 10 second delay when right-clicking a table to pull up the context menu. My situation might be a little different than some however because I use local databases only. My solution:

After reading the answer from imran about disabling antivirus, I took that concept a step further since I do not want to disable my antivirus completely JUST to overcome this issue.

I have reduce the delay significantly (it is now 1 second max) by changing the default database locations and then excluding that directory from the anti-virus (in my case I am using Microsoft System Center Endpoint Protection 2015).

For changing the default database locations see the following article for detailed information. http://thomaslarock.com/2015/02/changing-default-database-file-locations-in-sql-server/

Improve this answer
3

Concerning SSMS 2016 and the registry change. My path was 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\Tools\Setup\UserFeedbackOptIn

I changed it from 1 to 0 and SSMS was speedy again.

Improve this answer
0
2

Sometimes this issue can be solved by simply closing a large number of cached query windows.

My instance of SSMS had about 15 queries in the saved cache (which opened every time I opened SSMS) - I closed all these windows & restarted SSMS & the issue immediately disappeared.

Improve this answer
2

I have ssms v17.0, I also had the problem with slow right click context menus.

The name of the computer had dashes in it, renaming the computer something simple, fixed it.

I uninstalled ssms, uninstalled sql server, renamed the computer something simple like 'MyBox' instead 'xja-13-asdfa3'. Then I reinstalled sql server, then ssms, and this fixed ALL my SLOW context menu problems when right clicking tables. I imagine that somewhere DNS problems were occurring and the waits were related to waiting for dns timeouts.

I had a laptop with 16 gig of ram, ssd drives, a 3ghz cpu straight out of the box from the factory with the default random pc name.

Improve this answer
1

It is quick on my computer.

SSMS in SQL Server 2012 now uses the Visual Studio 2010 shell, which is more resource intensive than the shell used by SSMS in SQL Server 2008. I suggest upgrading your computer or using SSMS from SQL Server 2008 R2. You should be able to perform most tasks with the old version.

You could also try installing the latest cumulative update as they will update the client tools as well. The latest version for SQL Server 2012 is currently CU1.

http://support.microsoft.com/kb/2679368/

Improve this answer
0
1

changing the computer name to have no '-' is what fixed it for me.

Improve this answer
1

It's important that you don't use the short NetBIOS name!

If you do, the management studio uses some kind of legacy connection. Everything will be annoyingly slow. Just use the IP or full name of the machine that the sql server runs on.

If you wanna find out what the NetBIOS name of your machine is, run a query:

select @@Servername

This returns the short, I think 15 characters long legacy name of the machine. Don't use that one.

Improve this answer
0

For me it was the BitDefender firewall that made my SSM ultra slow Apparently, some sort of a firewall blocking for getting access to the database list

Improve this answer
0

I know this question is old. But here's my solution. It worked for me every time:

  1. Open C:\Windows\System32\drivers\etc\hosts with Notepad
  2. Add this line to it 127.0.0.1 SomeNameHere
  3. Save (make sure it's saved by reopening it)
  4. Now connect to SQL Server Management Studio, using SomeNameHere
Improve this answer
-1

One should check the status of the database property "Automatically close" into "Automatically" group.

If true, after each query, the connection will be closed, and re-opened at each new query. That surely slow down your work in ssms. Default is false.

Improve this answer
-2

Could it be that you don't have enough RAM on your computer?

The recommended RAM for SQL 2012 is 4 go. It was 2 go for SQL 2008. http://msdn.microsoft.com/en-us/library/ms143506.aspx

Since you've upgraded from SQL 2008 to 2012, maybe you need more RAM?

Improve this answer
0
-2

When I start SSMS using Administrator privileges (right click and run as administrator), then it works fine, otherwise new query window is taking longer

Improve this answer
-2

Ssms / SQL Server Micrsoft Studio slow load time FIX

Disable Fusion Logging:

  • Press Win + R to open the Run dialog.

  • Type regedit and press Enter to open the Windows Registry Editor.

  • Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion

  • On the right pane, set the LogFailures DWORD value to 0 to disable logging. Optionally, also set ForceLog DWORD value to 0 to further ensure logging is disabled.

  • Close the Registry Editor.

  • Clean Up Existing Logs (if desired):

    Navigate to C:\FusionLog\ in File Explorer.

You can safely delete logs in this directory to reclaim disk space if needed. Restart SSMS and observe the behavior in Process Monitor again. The flood of Fusion Log writes should no longer be present. In case Visual Studio was loading slow. This will fix that too.

Improve this answer
-4

The cause of this is almost always a missing network drive - e.g. if you have a drive-mapping to your laptop, but your laptop is turned off.

The resolution unfortunately is to turn on your laptop (so the drives reconnect) or to disconnect your drives in Windows Explorer (but then you have to reconnect them later).

See here for more info: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/64c13a84-3e62-4b0c-a1ce-65fcc78d6787

Improve this answer
1
  • 1
    This issue is specific to SSMS 2012 - I doubt an msdn thread from Feb 2010 will be relevant.
    – JNK
    Jul 5, 2012 at 12:06

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