Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSMS Tricks

By Steve Jones,

One of the huge complaints I've seen with SQL Server Management Studio is the time it takes to start up. I've complained to a number of people at Microsoft and while my pleas for a separate query writer have fallen on deaf ears, I have received some tips on various aspects of SSMS. So here they are:

Custom Startups

I hope that most of you are comfortable editing the executable behind an icon in Windows. If you're not, then I'm concerned about you working with my database, but that's another article.

Assuming that you know how to get the properties of an executable, I've got a few tips for you (courtesy of Buck Woody's blog) on changing the startup behavior of SSMS.

Open My Database - One of the things that I was very used to in Query Analyzer was having to click the "Windows Login" box and immediately be placed right in the database I used most often. In many of my jobs, I'm often using one database 90% of the time, so this was handy. I accomplished this by changing my default database (for my login) to that particular database.

However that's kludgy and what if you use a separate log in, like "sa" or some other log in to do certain work. Wouldn't you like to always start in a database? Well there's a way.

If you check the properties for Management Studio, you should see something like this:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"

You could easily edit this to add the -d parameter and start in a database:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -d model
When I start SSMS on my local machine, I drop into a query window for the "model" database. There are other switches for servername (-S), trusted authentication (-E), username (-U), password (-P) and a filename to open in the query window (just include the path and file name).

No Splash - I hate splash screens. While they're cool the first 1 or 2 times I open something, it's annoying to see them over and over, especially for applications that I open and close constantly. Save the code, make the thing open faster. Add "-nosplash" to your SSMS startup to remove it's screen.

NOTE: In BIG BOLD LETTERS, I don't recommend using these switches for SQL authentication and having your password in a shortcut. It's a bad habit, even if it is your personal machine. I'd only use this for the -E, trusted connections.

Speeding It Up - I'm not sure if the service packs have helped or my new machine made a difference, but it seems that SSMS starts up on both my laptop and desktop systems, as well as the SQLServerCentralcom database server, in less than 10 seconds. That's a far cry from the easy 25-45 sec it used to take when the product was first release.

I saw a workaround posted last year about a way to more quickly start SSMS: Disable the IE check for a publisher's certificate. Since there is a lot of managed (.NET) code in SSMS, the checks for valid code (based on a signature) can take time. It's not recommended to stop this check because this is one way you can be sure a virus or other malicious code has modified your software, but you could see if that's the issue.

Maybe a bigger pipe would help there.

Sharing Registrations

How many times have you wanted to move your list of server connections to a new server? Or you have a new person start working with you and they constantly ask you which servers need to be registered and the credentials? This was a pain to manage in SQL Server 2000 and Enterprise Manager, but it's gotten easier in SQL Server 2005.

You need to open the "Registered Servers" pane, as shown below. You can access this from the View menu (Registered Servers) or click CTRL+ALT+G

If you right click one of the groups, you will see a menu with an "Export" option, as shown below.

Once you select export, you will be presented with the dialog below where you can export one server, a group of servers, and choose whether or not you will include user names and passwords in the export file.

To import these registrations on another copy of Management Studio, right click in the Registered Servers pane and select "Import."

A Quick Directory

Have you ever needed to execute the "DIR" command or some other command prompt executable while you were working with SQL Server? I often have and it's a pain to open a command prompt and run something. Now you can do that from within Management Studio.

Click the SQLCMD mode, shown below, and your commands will be highlighted in the upper window. Prefix your command with two exclamation points and hit Execute, CTRL+E, or ALT+X and your command will run.

Bookmarks

I have to admit that I wasn't sure this was a great tip, but it has actually come in handy. How often are you working on a few different things at one time. You might have a stored proc you're editing, the execution in a separate window, and someone asks you to look at another object, query, or anything that distracts you. With today's procedures often running into dozens if not hundreds of lines, how can you easily get back to your place?

Bookmarks let you quickly mark places within your code. From the View menu, open the Bookmark pane as shown below.

Now you can select a line of code, click the leftmost icon (Toggle Bookmark) in the bookmark pane, and you'll have a bookmark entry. You can easily rename them, as I've done below. Double clicking one of the bookmarks brings that tab to the front and puts your cursor on that line. You can see the bookmark icon to the left of the line in the right pane.

One word of caution. If you close an editing window that hasn't been saved to disk, the bookmark is essentially lost. If it has been saved, it will reopen the file.

Execution Plans

There's a lot that can be said and written about execution plans, but I'll cover one handy little item here. How often have you run a query, gotten an execution plan and you want a second opinion on what might be wrong or what to focus on? How handy would be be to email or send a copy of the plan to a friend to brainstorm with?

Now you can do it and it's pretty cool. Include execution plan in your query and then right click inside the plan window as shown below. You select the "Save Execution Plan" option.

This will save the execution plan as an XML document with a .sqlplan extension. Send that to your friend and he or she can open it in SSMS and view the plan, including the details behind each step that appear when you hover over it.

Conclusion

These are a few of the tips I've run into with Management Studio that make working with this tool a little easier. There are a few more items I'll talk about in future articles, but they really require more information than a paragraph or two.

Please let me know what you think through the discussion link and if you have any tips to share, add them as well.

Total article views: 7277 | Views in the last 30 days: 26
 
Related Articles
ARTICLE

Bookmark Lookups

Building high performance applications with SQL Server can be a challenge if you do not understand h...

FORUM

Bookmark Lookup Options...

Need some advice on options for dealing with a bookmark lookup

BLOG

SQL Server Quickie #5 – Bookmark Lookup Deadlocks

Today I have uploaded the 5th SQL Server Quickie to YouTube. This time Santa Claus talks about Book...

BLOG

SQL Server Quickie #5 – Bookmark Lookup Deadlocks

Today I have uploaded the 5th SQL Server Quickie to YouTube. This time Santa Claus talks about Bookm...

FORUM

Export option (right click in the database - tasks - export data...)

Export option (right click in the database - tasks - export data...)

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones