Sql-server – SQL Server Management Studio slow opening new windows

sql serversql-server-2012ssms

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.

Best Answer

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)

Related Question