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

AaronBertrand — Wed, 23 Jun 2010 00:56:00 GMT

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:

View Designer
Query Designer
Edit Top N Rows (formerly Open Table)
New/Edit Database
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?

Search results matching tags 'performance monitoring' and 'Connect'

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