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.

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.

 

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

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

References:

• SSMS Utility: http://msdn.microsoft.com/en-us/library/ms162825.aspx

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