Links

The procedure I demo is sp_WhoIsActive, a free procedure from Adam Machanic.

Transcript

There’s a few other configuration changes we can make in Management Studio to make it easier to work with scripts.

Changing display text on tabs

One of them is to change what’s shown on the tabs at the top of SSMS.

Right now I have it showing lots and lots of stuff, which results in this tab not being completely readable. I have the name of my script, I have my login, I have my session ID showing. And when I highlight over it, when I hover over it, I do get a tool tip which gives me the full information about my query, as well as the instance, the database, and my login.

And I actually like that, but sometimes I want less information there, and it really varies.

Maybe I always use the same login in an environment, and it’s just not even an interesting piece of info. This isn’t always true, this is only true sometimes. But if that’s the case, I don’t even want that login showing.

We can control all of this by going into Tools, Options. And what we’re going to do is go and look at our Text Editor Section, or Text Editor Options. Under Text Editor here, I’m going to expand that out and then go down to the Editor Tab and Status Bar, so we are here, and then here. There are so many options in Management Studio, it’s easy to get lost.

We’re going to navigate down in this window to the right, and what we’re looking at here is Tab Text.

If you’re not interested in anything but the file name, for example, you can toggle everything else off and say, I just want to see the filenames there. And in some environments, that’s the case for me.

In some contexts, I actually do want to see all of them.

For me, this one will vary depending on where I’m working and what I’m doing. And I’m going to say OK.

But wait, I don’t see a change

Now, nothing changed here, right? This is the same tool tip we had before.

A lot of things in Tools -> Options don’t change until you open a new session window. My existing sessions aren’t changed by this.

What I’m going to do is I’m going to close this session and I’m going to reopen it, and now, on the reopened session, we can’t see my login name on the version of the tab. When I hover over it, my tool tip is shorter as well. If you only want to see the filenames, you can simplify your life a little bit.

Now, if I want to copy that path, I love this option of right-clicking on a tab, and using Copy Full Path. If I then have a file that I want to go open just in my File Manager or File Explorer, or I just want to get the path to its use and documentation, I love being able to right-click and copy that there as well. By reopening my script we ended up back at the top, of course.

Pinning sessions

Another option, and this is recent, I really, really like this one, is this ability to be able to pin the tabs. There’s a little pin now in Management Studio on all of our tabs, and I’m going to pin this tab, and this is the way that I like to configure it.

When I pin a tab, I like for it to be in a separate area, but that’s not how it defaults. It defaults to just being in line with all the other tabs.

We can configure this in Environments. We’re going to go Tools, and Options, and then under this Environment bar at the top, we’re going to go down to Tabs and Windows. And under Tabs and Windows, I like to say, show the pinned tabs in a separate row. That makes it more useful.

Now, these tabs, when we close Management Studio, they are going to be forgotten. It might seem like this option would change that, but at least, as of now, in Management Studio, it doesn’t. Removing them from the well is just dragging them out of that document well. That isn’t closing Management Studio.

This isn’t quite Pinterest. They aren’t pinned forever. They’re just pinned for the life of you having Management Studio open.

But I do like having this pinned option, because maybe there’s a script that I need to refer back to periodically during the day. Having it in its own little space there makes it much easier for me to remember not to close it.

Makes it easier to not get lost amongst my work. I really like that.

Creating custom shortcuts for stored procedure commands

I also, for procedures that you don’t even want to open the file for them, you just have a procedure installed, and you want to be able to conveniently run it, I love saving shortcuts for the procedure names.

I’ve done this for many years, and now, I really love the free sp_WhoIsActive procedure from Adam Machanic at whoisactive.com. Before, I had my own version of a similar script that said hey, what’s running?

Whatever script you like to frequently run to see what’s up, and maybe there’s more than one, but for me there’s usually one, we can configure that in Tools and Options. Under Environment, we go to Keyboard, and then we go to Query Shortcuts. So, we’re in Tools Options, Environment, Keyboard, Query Shortcuts. And there are some built-in ones here.

I don’t actually use the built-in ones a lot. What I like to do is, and I like to use control + 0, and I like to set up sp_WhoIsActive right there, under control + 0. It’s an easy one to remember for me. I’m going to say OK.

And now, again, it’s not going to do anything in my current session. I’m hitting control + 0 and nothing is happening here, and that’s because, like a lot of things in Tools Options, I have to do a new session window.

I just did control + n for new window, and now, control + 0 runs sp_WhoIsActive.

I don’t really have anything going on in my test instance, so this isn’t really very interesting, right?

What we’re going to do is we’re going to go over to another session. I’m going to throw in a BEGIN TRAN, and then run a select in the wrong database. Let’s see if we actually got anything there. Yes, my failed query. I did start a transaction even though I followed it with an error, followed by a batch terminator, oops. If I now, and I’m running it again, you can see the time increasing on my flubbed transaction. That’s magical.

I’m going to go ahead and I’m going to roll it back. We’re going to rollback our transaction. We’ll pretend this never happened, right? It’s just between me and you.

When I go back there and hit control + 0, sp_WhoIsActive says it’s all cleaned up.

But, when I need to know quickly, hey, what’s up on this instance? I don’t even want to have to type the name of it.

That’s why I love that shortcut key, so I can immediately check and see if all is peaceful, or if there’s something up on the SQL Server.