Re-Blog : Making Management Studio Start Faster

Since my blog content at my old company’s site seems to be selectively disappearing, I’m grabbing the content that I can and re-publishing here, before it all goes away. This was originally published on June 25th, 2009.

As a SQL Server professional, I use Management Studio (SSMS) every day.  Several of my colleagues use this tool as well; developers and DBAs alike.  I have seen a lot of complaints that SSMS takes an excruciatingly long time to load, and I have seen a lot of little tidbits and so-called solutions scattered about the Internet, but I have yet to see a collective post that lists all of them together.  So I felt kind of obligated to fill that gap.

Note that some of these ideas are my own, some are very general, and not all have been tested.  My intentions are to comment on all of them (if not now, then eventually).

General, and probably obvious…

Add more resources

If you are running within a virtual machine, give it more memory.  And for the better enjoyment of your computing experience as a whole, if your machine is capable of being upgraded in the real physical RAM department, then by all means, do so.  Last year I upgraded my crappy OptiPlex at work from 3GB to 8GB of RAM, and it has a new lease on life.  Of course you need to be running an x64 flavor of Windows in order to take full advantage of more than 3GB RAM.  But if youre currently

Don’t open and close the application so often

One thread on a newsgroup talked about a guy whose boss was complaining multiple times throughout the day that SSMS was taking too long to load.  My immediate reaction was, why was he opening and closing the application so often?  Maybe to save on resources, but I would think that opening and closing the app so often would be far more frustrating than whatever pain it caused by staying open the whole time.  I always leave a copy of SSMS open, and simply minimize it when I’m working on something else.

Exclude the application from virus scanners and malware/spyware programs

Several of these applications are running in real time, “protecting” you from yourself – though several have options to exclude certain files or programs.  I think it is safe to say that you do not need to protect yourself from Management Studio.  If your software has an exclusion list, add the instance of ssms.exe (SQL Server 2008) and/or SqlWb.exe that you know is the one you installed (who knows, some clever trojan writer might take this post as a cue to proliferate some bastardized version of that executable to slip past your radar).

Do not expect too much from your system

Your operating system can only handle so many tasks and applications running simultaneously.  If you are using Photoshop and Word and Outlook and TweetDeck and Firefox and Internet Explorer and Minesweeper and <on and on and on> … take a break from a few of those while you are “working.”  Outlook is probably the biggest bang for your buck… not only will you save those heavy resources, you will also not be interrupted by pesky e-mails while you are making those schema changes.

Experiment with local vs. remote

While it is probably never a good idea to run SSMS within a remote desktop session in the data center, this might provide temporary relief from frustration if your workstation is acting up.  Since the server probably has gobs of resources compared to your own machine, it will likely respond better.  However, there are two gotchas: (1) you are trading off local response delays for network video-related delays, and (2) it could backfire completely; if the server is pegged, the remote copy of SSMS might actually have *less* resources to work with.

Specific startup recommendations…

Add the /nosplash switch to your shortcut

The /nosplash argument prevents SSMS from loading the “splash” screen with the logo and copyright message.  For a long time, this was about the only recommended way to get SSMS to start faster.  In my opinion, it is 100% placebo effect.  You don’t feel like SSMS is taking as long because you are not staring at the splash screen.  I have yet to see any evidence that this actually helps, but a lot of people swear by it, so I guess it can’t hurt.  Within your start menu or quick launch bar, right-click your shortcut to ssms.exe or SqlWB.exe, and select Properties.  Then add /nosplash as follows:

Turn off certificate revocation checking from within IE

One of the things SSMS does when it starts up is it checks for certificate revocation (since it uses a lot of managed code).  I am hoping that someday they add the ability to turn this check off from within SSMS, but until then, one way you can turn this off is within Internet Explorer’s Tools / Internet Options / Advanced tab.  You can uncheck these two options way down under Security:

• Check for publisher’s certificate revocation
• Check for server certificate revocation

This affects one user but affects everything they do with certificates, so I don’t really agree that it is a good idea.  It seems to be most effective, though.  And Euan Garden has talked about it for a long time, starting here.

Block certificate revocation checking via your hosts file

You can send all requests to crl.microsoft.com by modifying your hosts file.  Open Notepad, use File|Open, and browse to this folder:

%WINDIR%\system32\drivers\etc\

You may need to go into Windows Explorer’s Tools | Folder Options | View tab and turn off the “Hide protected operating system files” setting in order to see the file called “hosts” (no extension).  Open this file and add the following line:

127.0.0.1     crl.microsoft.com

This affects all users and again affects everything you do with certificates.  Use with caution, as this could create unnecessary exposure to real certificate problems.  You can accomplish something similar enterprise-wide if you are using your own internal DNS.  Again, use with caution.

Allow certificate revocation through firewall

In a lot of cases, crl.microsoft.com is probably blocked by your firewall or not proxied correctly, and this can add to the delay if you are still allowing revocation checking through.  I am far from a firewall guy, so I would talk to your NOC personnel if you want to pursue this route.

Turn off online Help content

By default, SSMS enables options to search online, both in Books Online and community web sites, when searching for help on specific issues.  You can turn this off in Tools | Options | Help | Online.  I tell it to “Try local only, not online” and uncheck all the checkboxes below except “Local Help.”

Note that this option is in a different place in SQL Server 2012, and likely has less impact on SSMS anyway.

Check your registered servers

I have not proven this, but I have a feeling that if you have several registered servers, they may be polled “on your behalf” for reachability and status.  So periodically it might be a good idea to test your registered servers and make sure they are current by either deleting inactive ones or updating the credentials.

Turn off error usage and reporting

I am not convinced this will affect startup time, but it may help avoid inexplicable stutters during your usage of SSMS.  Open your Start menu’s program directory and navigate to SQL Server > Configuration Tools > SQL Server Error Usage and Reporting.  Turn off the options as follows:

Open an empty environment

By default, SSMS is set to launch both Object Explorer and a query window when it starts up.  When this setting is on, it also sits there with a connection dialog, prompting you to give the okay to connect to the most recently-connected server.  It will not do anything else until you dismiss this prompt.  Well, you can change this to avoid this overhead when SSMS starts up.  Under Tools | Options | Environment | General, change the “At startup:” dropdown to read “Open empty environment.”

What if SSMS hangs?

Funny, I just wrote about this over on sqlblog.com.