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:

shortcut

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

ie1

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

ssms_options22

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:

sql2008_error_usage

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

ssms_options

What if SSMS hangs?

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

My customer service WTF of the day for Monday, June 11, 2012

I have two services with National Grid: electricity and natural gas. I have opted in to both of their paperless billing systems. When an e-mail comes telling me that a bill is ready, the subject says:

Your NATIONAL GRID bill is ready

When you open the e-mail, there is nothing in the body whatsoever that tells you whether this is a gas bill or an electric bill. They do have the courtesy to show the last five digits of your account, so you can take those numbers and match them up against the last paper bill you received, check the payee settings in your bank’s online bill payment system, or log on to National Grid’s own site and cross-check there. Or just memorize which account number is which. After four or five months of doing one or more of these, I sent the following e-mail to customer service:

Can you please make it more obvious in these e-mails whether this is an electric bill or a gas bill? While a lot of customers don’t have both, I do and it is frustrating to have to go into my online banking system to compare account numbers, or try to remember the correlation in my head. All I’m asking is that the subject say “Your National Grid ELECTRIC bill…” or “Your National Grid GAS bill…”.

Thanks,
Aaron

Here was their response (several days later):

Thank you for your email inquiry and patience.
I apologize for the delay and appreciate your feedback.

We have recently updated the site and I apologize for any inconvenience this may have caused. It is necessary to log in online to view your billing statement for needed clarification and the last four digits of your Account# will display.

If you are experiencing any difficulty, please do not hesitate in replying back for further assistance. I apologize for this inconvenience.

I have to assume that, because I mentioned “account numbers” in my e-mail, this was some kind of an automatic reply triggered by the system – after a many-day delay – to help me figure out how to check my account number? No human with any reading comprehension at all would really provide this response for the question I asked, right? I mean maybe they meant that I could only check which account is which by going online, but why not at least say something explicit acknowledging that the e-mails don’t say anything, and maybe hint at why? So being even more frustrated now, I replied:

Well, that answer isn’t very helpful, but thanks I guess.

Here was their response this time:

You are welcome.
Sincerely,
Jen
National Grid

So either they really don’t understand sarcasm, or they are being particularly and intentionally jerky. Which I can do too.

Bottom line: I think I was pointing out a legitimate issue in their e-mails which, while admittedly probably doesn’t affect a majority of their customers, could certainly be eliminated with a very simple change to one line of code somewhere. I don’t think they’ve provided very good customer service here at all.