Love and Hate for SSMS

People have strong feelings about SQL Server Management Studio: they love it AND they hate it. In this week’s episode, I talk about why people have such conflicting feelings about SSMS, and how to work it all out.

No time to watch right now or read the transcript below? Listen in podcast format on iTunes , on Google Play, or plug this RSS feed into your favorite podcast app: http://dearsqldba.libsyn.com/rss

Transcript of this episode

Please forgive errors in grammar and punctuation: robots helped create this transcript. And luckily for us, robots don’t know a ton about SQL Server yet.

Hello and welcome to Dear SQL DBA, a podcast and YouTube show for SQL Server developers and database administrators. I’m Kendra Little from LittleKendra.com.

Today I’m going to talk about what people love and what people hate about SQL Server Management Studio. I’m not going to get super technical today, I’m just going to dish a little bit about Management Studio and why people feel really strongly about it.

The question that inspired this episode is this: I was giving a webcast on shortcuts and secrets for using Management Studio, and one of the very first questions that came in before the webcast even started was:

Why does Management Studio crash so much?

I’ve had Management Studio crash on me plenty of times in the past, too, so let’s talk.

Configurable vs. confusing

People love that SQL Server Management Studio is so configurable and the shortcuts and secrets webcast that I gave– it contains a lot of all of the ways you can customize Management Studio. Also, how to do some things that it might not be obvious that you can even do them in a faster way.

But one of the reasons that people also really can dislike Management Studio is that they find it really confusing. This is related to why people have really strong feelings about Management Studio: because there’s a pretty strong learning curve. There’s a ton of things that you can do in SQL Server Management Studio and it’s very very highly configurable, but to a level that that actually makes it pretty confusing!

Sometimes I get into a situation myself where – I’ve been using this thing for many many years since it came out with SQL Server 2005, back in the day. I remember when Management Studio first came out, I was like, " I don’t know if I like this thing!" Because I was very comfortable with the old tools. Even now, sometimes there will be a setting that I’m looking for, and just finding it can be pretty tricky. There’s even a little “find” bar in the settings where you can search for a given setting – under tools / options, when you’re going into the different things you can configure 00 and sometimes that “find” bar doesn’t even find me what I’m looking for.

To make the best of this, what I do and what a lot of people do is: I have a setup checklist for SQL Server Management Studio that I like to use. When I’m configuring Management Studio I have a list I can run off: these are the things that give me what I want to set Management Studio up to my preferences, because preferences do differ.

Having a setup list helps you remember what Management Studio doesn’t do by default

I have worked in some situations as a consultant where I’m not connecting to things always from my version of Management Studio. I’m looking remotely at someone else’s computer, and I’m using their Management Studio set up.

And it’s not always cool to just change someone’s setup.

Having the setup checklist helps - even if I’m not going to run through it, knowing what’s on my list of things that I change sets me up to expect, “ok I know the items that aren’t going to be normal to me because they are the things that I typically change.” I can tell for instance: I’m using someone else’s install of Management Studio, if I script out indexes and the indexes are compressed, it’s not going to script out the data compression settings, because that that’s not configured by default. I know what to look for if I’m going to do certain things. (My setup list is included in my free course on SSMS Shortcuts & Secrets.)

Shortcuts are awesome! But a bit overwhelming

One of the other things that people love about Management Studio– I love this too– is that there’s a lot of shortcuts, and you can configure a lot of your own shortcuts.

You don’t have to reach the mouse super often. You can keep your hands on the keyboard in Management Studio.

That’s great, and when you’re in the flow and you’re using a lot of the keyboard shortcuts, it just feels really cool. It feels really good: there’s something about that flow of being able to do a lot of things on the keyboard that is fun. But there’s also the fact that it’s hard to remember all of these shortcuts.

Even shortcuts that you know pretty well, if you don’t use them for a while and then you go back, you’re like, “oh wait, how did I do that thing again?” The muscle memory is gone.

To learn shortcuts, what I like to do is to practice them in small groups. I found that I don’t do super well learning lots of shortcuts at once. Instead, I like to have a list of them – and I put this together with my setup checklist in my free course on Management Studio Shortcuts and Secrets – and I not only have the list of things I like to configure, but I keep a list of shortcuts there too.

I like to periodically review them and practice using them. Because even even shortcuts I kind of know, if I’m not in the habit of using them, I don’t remember them. So I’ll periodically look through the list and be like, “oh yeah, I should get back to using this shortcut on the keyboard to get me down to the results pane, so that I don’t just grab the mouse every time I want to go down to the results pane,” for for example. (That’s F6, by the way.)

My favorite shortcuts these days are using the little IDE that you can use to switch between sessions. If you do ctrl + tab it brings up this little display and hitting ctrl + tab iterates you through all of your Management Studio sessions. You can easily switch between what you’ve got open. If you do alt + f7 that brings up the same IDE, but lets you switch over to tools like Object Explorer, and things like that. You can actually arrow around when you bring it up with alt + f7, say if you are on Object Explorer and then you want to switch back to a session. Alt + f7 is really flexible - I don’t love it because on some keyboards I have to hit a function key - I have to hit “fn” to be able to use a function keys, so control tab works on more of my keyboards and is easier. I just I can’t get enough of that switching between sessions. It’s a lot like the Windows alt + tab, but within Management Studio for your sessions.

People also love the flexibility of Management Studio

It does so many things. When you think about all of the different things– there’s a wizard you can open that scripts out all the objects in the database. There’s security you can configure. There’s objects you can create. I mean, there’s just so many little tools in Management Studio, and it’s even getting bigger. Right now we have an Extended Events GUI within Management Studio, whereas ye old SQL Profiler – there used to be a shortcut to it, and I guess there still is, you can open it from Management Studio, but it’s another application.

Management Studio is getting more and more integrated as this giant Swiss Army knife, but there’s always things that people dislike that it’s missing.

One thing I found is that certain things that I thought Management Studio didn’t do are in there! I just didn’t know how to get to them. A lot of these– for example, the scripting options I was talking about.

I thought for a long time that Management Studio just Couldn’t script out certain things if you’re scripting a partitioned index. That it just wouldn’t script out the filegroup that it’s on– or wait, that’s not it, it’s the partition scheme that it’s created on.

That’s actually like the compression settings: that’s one of the options you can configure. There’s whole series of settings for how things get scripted out under Tools / Options. There’s a whole scripting section for Object Explorer.

I just didn’t know it existed. I didn’t find out that it existed until tweeted about this: “is this a bug that it doesn’t script out _____?” And Adam Machanic answered: “it does that, and here’s where it is.” Lo and behold, the things that I thought were missing were actually there.

If you do have things where you’re like, “I really think it should do this differently,” you can file a suggestion or a bug at https://connect.microsoft.com/SQLServer.

One of the ones that I’ve seen recently is when you drag over the all the column names – you’re dragging over the columns folder, rather: in Object Explorer you can grab the column folder for a table and drag that column folder over, and it will auto-populate the names of all of the columns in a comma delimited list in your session. But it doesn’t put the square brackets around the column names. If you drag over the table name it will put safety brackets around the table name, so if you have any weird characters in there everything still works. So there’s an inconsistency between what you drag over from Object Explorer to your window, and someone filed – I can’t remember if it was a bug or a suggestion – but basically, “plz make it consistent.” The developer looked at this and was like, “oh yeah, we can totally fix that.”

I believe that the fix hasn’t been released yet, but the code for the the bug is in the “completed” state. I don’t think that release for Management Studio is out now, but I can’t remember. (I’m like, “did that happen the last time I tested it?")

But filing a Connect item got things changed and got things fixed. That’s one of the cool things: if you want it to be different, first of all ask on Twitter and see, “is it actually there and I just don’t know about it?” But also, Microsoft is open to knowing what do you want this thing to do, and how can we do things better.

(Note: this is the Connect item, and I just re-tested and indeed the change is active in SSMS 14.0.17177.0. I see [column names with brackets] after dragging over a ‘columns’ folder.)

Your change could become reality! Because SSMS gets frequent updates these days

I really love how active the Management Studio coding team has become. For Management Studio, it used to be that we only got updates when SQL Server got updated. Now Management Studio releases on its own, and this is fantastic because they can release as often as they’re ready to release. When we update Management Studio, we can choose: do we want to update it on the production server at all? Or do we just want to update it on our client machines now, and maybe update Management Studio more quickly on our client machines than we would on the production Server itself, where we may not want to reboot every time we just have an update to Management Studio.

There are still folks who are afraid to upgrade for a variety of reasons. If I’m running a new version of Management Studio, is everything going to work? This is valid! With any software, when things change, sometimes things break. There was a recent version of Management Studio where if you were connecting to a case-sensitive instance, there were certain graphical elements that didn’t work as they had before.

The good news is you can choose how you update Management Studio, in the sense of how many versions of Management Studio you have. This is particularly useful with major versions of Management Studio. We recently had just a big major version come out, and with Management Studio you can do side by side installations.

So you can do things like say: ok I’m going to try out the new version on my client computer, but if anything’s weird I can you know run a version on a different machine that’s older.

But you can ALSO install side by side on your computer the latest and greatest and keep the old one there if you want to go back even the last major version. “I’m going to just keep this last major version of it around for a while. It doesn’t have the new look and feel, and it doesn’t have some of the cool things in the new version, but for accomplishing basic tasks it’s my old faithful.”

You can keep that old version around for a really long time, and that lets you say, “okay, I’m only going to use the new updated SSMS against our dev instances. I still have my older version to use against production.” You can be as careful as you want to be, which i think is terrific.

And always, always, test against development, and update on your workstation before you ever go and touch things on the production server.

There’s so much great community content on using SSMS

One of the things that I can’t get enough of is that lots of people in the community document things for SSMS. They create their checklists for “here’s the things I change in SSMS,” and they also mention and create presentations for their favorite shortcuts and their tips and tricks. I can’t get enough of those sessions. I seriously would watch one a week or maybe even more often if there were a ton of them out there.

On the other hand, there is a lot of community participation because it’s so complicated.

There may be times where something you see in a presentation, and you try to do it, and it doesn’t work for you. Maybe that’s because you’re connected through a VM and something’s overriding a key in the shortcut you’re trying to use. Or maybe it’s for another reason.

So let’s talk about that crashing problem…

Let’s get back to the original question that inspired me to put this together. It was, “why does my Management Studio crash so much?” This is similar to, “why doesn’t X feature work for me in Management Studio?”

One of the first things to just check is what version of Management Studio are you running? How old is it? You may be hitting a bug in an older version of Management Studio. It might be a bug that causes it to crash, or it might be a bug where just some things didn’t work.

For example, one of my favorite shortcuts is ctrl + u. It takes you up to the “use database” box. I’ve had some folks be like, “it just doesn’t work for me.” Well, there was a bug in an older version of Management Studio that was “ctrl + u doesn’t work.” For some of these folks, updating Management Studio fixed it.

Some of the crashing bugs are from older versions as well.

Also, are you using a plugin, or are you connecting via a virtual machine that could be causing the problem?

Some of the cases I’ve had where ctrl + u wasn’t working for people was actually caused by the fact that they were using a plugin that had broken ctrl + u as well! You’ve got to test out, “what do I have installed that isn’t from Microsoft, that’s extra in here, and could it be causing the problem as well?”

Sometimes it might be also that of course your virtual machine provider thinks that keyboard shortcut really means something else, too.

If you are seeing repeated crashing, and it isn’t any of these things, have you reported it to Microsoft? Because you should report it to Microsoft! If Management Studio is crashing out all the time, they don’t want that to happen. Use sites like connect.microsoft.com/SQLServer if you’re getting a specific error message. If the crash is happening when you do a given sequence of tasks, you can let the developers know about that as well.

So do something about it!.

And please please please PLEASE share the things that you love doing in Management Studio. The tips and tricks that you know that make using it easier and more fun. Like I said, I really can’t get enough of that stuff. I would love to have even more shortcut cheat sheets be out there, and even more checklists of how to get more out of your day with Management Studio.

Because, like you, I spend an awful lot of time in Management Studio, and the more fun I have, the more fun my day is!

Thanks for joining me for Dear SQL DBA this week. I’m Kendra Little, and I’ll see you again soon.