#0136 – SQL Server – SSMS – Slow load times – Improving launch performance in 2 simple steps


As you know, I recently completed a 10-part tutorial series on the SQL Server Management Studio (SSMS). As I finished writing the tutorial, I remembered that one of the issues most SQL Server developers encounter are the slow load times of the SSMS. Sometimes, SSMS is just not fast enough for a quick session. In this post, I will share with you 2 simple tweaks I use to improve the launch performance of SSMS.

Simplify the SSMS interface

Almost each dedicated pane in SSMS that allows you to directly interact with your SQL Server instance establishes a connection to the SQL Server. The Object Explorer (which normally opens by default when you launch SSMS) and the Object Explorer Details window launch 2 separate connections to the SQL Server instance, which are in addition to the connection opened by the Query Editor window. Read my post: http://beyondrelational.com/blogs/nakul/archive/2011/01/17/the-multiple-connections-of-ssms.aspx – for details on this fact.

Each new connection means an increase in the resources consumed and additional time taken to establish the connection. Therefore, the first rule of optimizing SSMS launch times has to be to keep the SSMS interface simple and close out any unused panes/monitors open within SSMS.

Controlling the startup windows

To configure what windows should be opened up by SSMS at startup, simply navigate out to Tools –> Options within an SSMS session. In the “Options” window, go to the “Startup” options under “Environment” node. (For SQL Server 2005, 2008 and 2008R2 users, you would go to the “General” page)

You may want to select either “Open new query window” or “Open empty environment” for some of the fastest SSMS launch times.

SSMS Startup Options

Adjust the Visual Experience based on the client performance (SQL 2012/”Denali” and up)

Starting SQL Server 2012 (code named “Denali”), the “General” page of the Options window has a section that helps you tweak the visual experience of the SSMS based on the client performance. By default the SSMS automatically adjusts itself based on the client performance, and I have found that if I leave it as-is, things work just fine for me.

SSMS Options - Adjusting Visual Experience features 

Automate the launching of SSMS and connecting to your preferred SQL Server instance

Many a times, improving the load times may not just be about launching a particular application faster. In fact, for me, launch time for any application is the time the application takes to load up + the time it takes for me to actively use the application. In case of the SQL Server management studio, the launch time would therefore be a summation of:

  1. Launching the SSMS
  2. Selecting the SQL Server instance
  3. Entering connection information
  4. Clicking OK to the connection dialog

Wouldn’t it be much faster and simpler for a developer to have all these steps rolled up into one?

Using the SSMS command line options

SSMS allows us to specify a few command line options that eliminate the need for a user to select the required SQL Server instance and enter the connection information every time they need to use SSMS. Most developers need to connect to only a handful of SQL Server instances. These instances would not change for the duration of the project they are actively working on and therefore using customized shortcuts for these instances makes sense.

The parameters that I generally use are:

Parameter Meaning
-S servername The SQL Server instance name to which the SSMS should establish a connection
-d databasename The database within the specified SQL Server instance to which the SSMS should switch to after establishing the connection
-E Connect using Windows authentication.

If your server uses SQL Server authentication, you may use the [–U username] and [-P password] options

-nosplash Do not display the SSMS splash screen during startup

In addition, you may also use the parameters [scriptfile], [projectfile], [solutionfile] to specify a script, project or solution to open by default.

I therefore changed my SSMS shortcut to be the following. The shortcut connects to the AdventrueWorks2008R2 database on my test instance. The SSMS would use Windows authentication.

"C:Program FilesMicrosoft SQL Server110ToolsBinnManagementStudioSsms.exe" -S W2K8DENALISQL11 -d AdventureWorks2008R2 -E –nosplash

SSMS Shortcut

As can be seen from the screenshot below, the SSMS launched with windows authentication and connected directly to the AdventureWorks2008R2 database by default.

SSMS - Fast Load

References:

Do you use any methods to speed up the SSMS launch times? If yes, do share your method in the discussion section below.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql

Advertisements

3 thoughts on “#0136 – SQL Server – SSMS – Slow load times – Improving launch performance in 2 simple steps

  1. Nakul Vachhrajani

    Here are some more points to keep in mind when attempting to improve the launch performance of SSMS: [http://onetooneglobal.com/otocorporate-posts/2009/06/25/making-management-studio-start-faster/][1]

    In addition, Aaron Bertrand has requested a “pre-loader” process for the SSMS to speed up the launch: [http://connect.microsoft.com/SQLServer/feedback/details/714652/ssms-background-helper-pre-loader][2]. Do vote in to support the request.

    [1]: http://onetooneglobal.com/otocorporate-posts/2009/06/25/making-management-studio-start-faster/
    [2]: http://connect.microsoft.com/SQLServer/feedback/details/714652/ssms-background-helper-pre-loader

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s