Lately, I noticed that SSMS was taking longer and longer to start. When I started at my company a couple years ago, SSMS would come up fairly quickly. Now, it was taking about 3 minutes to start up. As the time gradually increased, I initially chalked it up to newer versions of SQL – in my time here, I’ve gone from using the SSMS version that comes with SQL 2005 to 2008 R2 to 2012. We all know software experiences bloat over time as more features get added to newer releases. But today, during an unrelated issue, I happened to be going through my computer’s System Event log and saw a bunch of DCOM errors:
I decided to track down the cause. The timestamps all seemed to indicate that problem occurred shortly after my computer booted. I spent too much time chasing down processes that ran on my machine during the Windows boot process before I finally realized my problem was in how I use SQL Server Management Studio:
As I decommissioned servers, I would move their entries in my SSMS Registered Server list to to a special folder. I kept the entries around because on some of my servers, I connect via Windows authentication, some use SQL authentication, some are referenced by DNS name, some by IP address, etc. I wanted to save all that connection configuration info in case I needed it later for some reason.
As it turns out, this was what was causing SSMS to take so long to launch. When SSMS launches, it tries to query all the servers in the Registered Server list to see if they are offline or online so it can display the little green arrow or red square icon next to the server name. By leaving the decommissioned server entries in the list, SSMS was trying to connect to servers that no longer existed. Thus, on launch, I had to wait for several DCOM timeouts to occur before SSMS was available for use. As soon as I removed these entries, SSMS started quickly once more and the DCOM errors no longer appeared in my Windows event log.
As my length of employment here has increased, the number of decommissioned servers has increased, which explains the slowly increasing SSMS start up delay. Why did this always seem to occur when my system booted? SSMS is the first program I launch when I power up and log in each morning. Mystery solved!