June 22, 2010 | SQL Server

SSMS : Which parts do you love? Which parts do you hate?

I find myself categorizing a subset of SSMS features ranging from "must have" features to "cringe when they are mentioned."  I'll try to summarize the standouts for you here, then ask you these questions: Which parts of SSMS do you rely on? Which do you wish were not there at all?

The "I almost always use it to get the task done." variety

  • SQL Server Agent's Job / Job Step Editor.  While not pretty, the wizard does make quick work of creating jobs with several steps, multiple schedules, and various execution paths.  (Let's just pretend the 2008 R2 version isn't a no-op due to a debilitating bug.)
  • Profiler.  Similar to the job procedures, I am unlikely to ever get the hang of scripting a server-side trace from memory.  So, I use the profiler UI to set up the trace details, then save the script for future use.  If I have to run a slightly different server-side trace, I can use the same script with minimal changes; but in many cases it is just as quick to use the UI again and start from scratch.  (Technically, I guess this isn't part of SSMS, but it is certainly a management UI that has had its share of criticism over the years.)
  • Right-click object > Modify, or Script as ALTER to New Window.  This is by far the quickest way for me to get at the *current* version that is deployed, and when I am done testing my revisions against the dev server, I can then move the changes to source control.  I see a lot of people using sp_helptext or manually selecting from syscomments / sys.sql_modules / OBJECT_DEFINITION(), but this can involve several more steps to get the code into suitable (and more importantly, maintainable) shape.
  • Registered servers.  These are a god-send, especially since I've learned to export carefully constructed nodes and share them across all machines.  I wish the tool in general was more consistent at remembering passwords depending on how I've initiated or switch a connection, but that's another story I suppose.

The "I will use it if I have to, but usually feel icky and prefer better alternatives." variety

  • Table Designer – occasionally I will use this tool (right-click a table, Design), but only at the beginning of projects, where I want to slightly adjust the column order in a new table.  Even in that case, more often than not, I'll drop and re-create the table.  For tables already in use, I always use DDL commands like ALTER TABLE, knowing that any new columns end up at the end of the table.  And that's okay with me.
  • SQL Server Agent > Job History.  This dialog has some real quirkiness; in our environment, it is always confused because it is being run on a desktop in one time zone against servers in another.  I have been complaining about this since before SQL Server 2005 was released (though first officially documented in 2006; see Connect #124841).  I also find the interface cumbersome to use and drill down – usually I get more complete and consumable information, and more immediately, from SQL Sentry's Event Manager – which also has the ability to store more history in the repository without bogging down production instances of msdb.
  • Select Top N Rows.  This is a quick way to grab an arbitrary 1000 rows (or whatever number you customize) from a table.  Sadly, it just blindly executes the query without giving you the opportunity to modify the column list, where clause or isolation level (personally, I prefer Script > As SELECT To > New Query Editor Window).  Oh, and it won't necessarily put you in the context of the right database; instead, it uses a three-part name for the table, which can be frustrating if you try to add a simple join or subquery and forget which database you're really in.

The "I will not touch it with a 10-foot pole"! variety:

  • Activity Monitor.  While this replacement of the useless view of the same name in previous iterations of the tool shows a lot of potential, and its motivation is in the right spot, it is in its infancy and has a lot of quirky behaviors.  If I want a good look at what is going on in my server, I will use SQL Sentry's Performance Advisor – which does a great job of warning me about issues even when I'm not paying attention.  For a really quick pulse of current activity or in environments where I haven't convinced them to use Performance Advisor, then I turn to Adam Machanic's sp_whoIsActive.  For a really quick look, yes, Activity Monitor can be useful… but for very little incremental effort you can get to a lot more information without memorizing all of the DMVs.
  • This will be ridiculously shocking, but any of the features that allow you to shrink a database or a file.  I acknowledge that there are some cases where shrinking a file is necessary, and I don't want to get into a religious battle about it.  I just wish that the task was much more difficult and thought-provoking to complete.  Rather than one button or checkbox, imagine if the process to shrink a file were as convoluted as setting up SQL Server?  There is one case where I'd applaud a longer and more cumbersome set of steps to accomplish a simple task.
  • Most other designers / wizards.  Including, but not limited to:
  1. View Designer
  2. Query Designer
  3. Edit Top N Rows (formerly Open Table)
  4. New/Edit Database
  5. New/Edit Login

Go ahead, search Connect.  There are dozens of bugs against these dialogs, and few if any will ever be fixed.  I can quote myself on twitter from earlier tonight: "Never send a UI to do a DDL's job."  As I've explained above, this isn't always true; but in most cases the DDL is going to be more predictable and also infinitely more repeatable.

The "Wait, why isn't that feature there?" variety

I'm cheating here a little bit, but there are some noticeable omissions from Management Studio that I can't even complain about because they never bothered to create them.  UIs to support features that are cumbersome to set up using code alone, such as Service Broker and Extended Events (they are gradually getting to others such as Mirroring and of course the new DACPAC stuff).  Or things that are long overdue but are otherwise covered by great 3rd party add-ins, like Mladen Prajdic's SSMS Tools Pack.

The "Oh yeah, I didn't even think of that one!" variety

It is a pretty vast application and there are probably several features I didn't even think to mention.  So again, I'll ask: which parts of SSMS can you not live without?  Which ones would you throw off a pier if you were in control?

12 comments on this post

    • Kendra Little - June 23, 2010, 7:06 AM

      Interesting! I actually like Activity Monitor. Maybe it's because I am a Ken Henderson fan and he worked on it to make it so much better than the previous version. But also, having the wait stats up in particular and being able to dock it at the top of ssms while working on an issue with scripts in the lower pane has been really useful to me.
      I think one of my favorite things is all the 'Script' options that went into the GUI dialogues. I think it's fantastic to let people set things up so they look right in the gui, then script it to a new window. It helps people learn the syntax at the beginning, as well as understand (and sometimes change) the sequence of actions that SSMS would take. It's a great GUI design concept and as far as things I use it's impressive how consistently it was implemented.
      Intellisense I would put solidly in the 'Frenemy' category. I like it for helping with long schema/object names, but I am still laughing at Brad Schulz's TSQL Tuesday #007 post because it's just so true. (http://bradsruminations.blogspot.com/2010/06/my-favorite-sql2008-feature.html)
      As far as the 10 foot pole, I'm just going to say that although I sometimes need to open Replication Monitor, I work hard to manage as much as possible with scripts. Replication Monitor, where would I begin?

    • Valentino Vranken - June 23, 2010, 2:47 PM

      "Select Top N Rows. … (personally, I prefer Script > As SELECT To > New Query Editor Window).  Oh, and it won't necessarily put you in the context of the right database; instead, it uses a three-part name for the table, which can be frustrating if you try to add a simple join or subquery and forget which database you're really in."
      I also wish they'd put the context to the DB from which you've requested the select. I have a habit of removing the "TOP 1000" so that it always generates a full select. Now if only that DB context was updated then the functionality would be like Script > Select To, with less menu items to go through.
      Also agreed on the useless designers. Now and then I by accident select Design when wanting to retrieve a view's T-SQL, really annoying.
      Interesting post!

    • Michael J Swart - June 23, 2010, 3:43 PM

      I've been using sp_helptext mapped to Ctrl+3 So to look at a sproc or view, I select text and then use CTRL+T3 (text output then sp_helptext) Then a cut and paste from results into query window.
      The script to new query window seems to "mousy" for me.
      But that's just my preference.
      (I also like to use vi for text editing)

    • Rob Volk - June 23, 2010, 9:31 PM

      I'd love it if they had a macro feature, or at least make the IDE components more easily customizable.  It would be great if they could make the various panes customizable, or have a generic pane that you could add small pieces to.  The object explorer annoys the crap out of me, because none of the nodes immediately under a server has any useful information.  Nor do most of the next level nodes.

    • John Lynn - June 25, 2010, 8:32 PM

      Edit a stored proc or two using "Modify", look at a few table definitions with "Script table as/CREATE To/New Query Editor window",
      and pretty soon your list of open windows is out of hand and getting back to find that one query is much more difficult than it should be. The lopsided "Ctl-Tab" display is no help — I waste lots of time cycling through the cryptic window list.

    • Chad Miller - June 27, 2010, 6:05 PM

      Two new 2008 features I really like in SSMS:
      Multiserver queries — Great for a quick way to run those ad-hoc queries looking for configuration information on many servers or deploying a bunch of DBA scripts
      Central Management Server (CMS) — Entire team can see same registrations and since registration data is stored in msdb tables on CMS server I can drive PBM and other admin scripting from the data.
      One of the features I wish would get fixed is in CMS. The same registration properties supported in a local server registration aren't supported in CMS i.e. connection colors. There is connection item related to this, but having trouble finding it.

    • S.K. - June 29, 2010, 5:56 AM

      I would love to be able to have the option of right-clicking on an SSIS package that is deployed to MSDB from the Integration Services node and choosing "Schedule via SQL Server Agent".  That was a major loss since SQL 2005 where we now have to manually create the job to schedule a package.  Albeit DTS was less powerful and less integrated, that little right click of the DTS package and dropping it into a job was VERY convenient.  I'd put that in the "nice to have" column.
      Another "nice to have" would be to have the ability to "copy login + permissions" to be able to mirror login access.  I'm sure we all get the request of "mirror my access with user x".  Then the process of using customized scripts to mirror the permissions on each DB begins.  AD has it, so why can't we 🙂

    • Saggi Neumann - July 2, 2010, 2:53 PM

      @John Lynn – The answer to your woes is ctrl-f6 – it works like ctrl-tab used to work in Query Analyzer…

    • Stan the Man - September 2, 2010, 6:17 PM

      Looks like the create/edit job problem is fixed in CU3:

    • AaronBertrand - September 2, 2010, 6:33 PM

      Thanks Stan, hadn't noticed that fix but that's definitely good news.

    • Lee Dise - August 6, 2015, 10:48 PM

      I have a tiny little problem, and haven't yet been able to find the solution…
      The SQL Server Agent job/job step editor is in a font that really don't like.  I think it's Arial, which must be French for "I Hate Old Guys Who Have Trouble Reading Small Print."  I googled around and found a bit of documentation proclaiming that you can set any font in SSMS by going here:  Tools -> Options -> Fonts and Colors.  There is a drop-down that lists supposedly lists all the places in SSMS where you can have a go at those fonts.
      Best as I can tell, either the documentation is tragically in error, or the SQL Server Agent job/job step editor is not considered a part of SSMS.  I managed to change practically every font in SSMS (menu, Object Explorer, Registered Server, the SQL editor, etc.) to Courier New ("Everything Looks Better in Courier New"), but the SQL Agent editor is a stubborn holdout.
      1.  Am I blind?
      2.  Did I miss something?
      3.  Is there a way to change that font?
      4.  If not, will that erroneous documentation get fixed?
      5.  Will the Steelers win the Super Bowl this year?

    • AaronBertrand - August 6, 2015, 10:57 PM

      1. No
      2. No
      3. No
      4. Depends. Can you point me to documentation that explicitly states that you can control every single implementation of any textual character throughout all of SSMS? My guess is that it says you can control certain elements, but does not iterate through them, so you've interpreted that to just mean all…
      5. No

Comments are closed.