Shortcut: Using Activity Monitor in SQL Server Management Studio

This is a quick tip but an important one. I see many people using SQL Server Management Studio (SSMS) and they aren't aware of Activity Monitor.

While there are many clever things that we can do with queries, to interrogate the health of the system, don't forget that there is quite a bit of useful information in Activity Monitor, and it's easy to get to.

There are two basic ways to launch Activity Monitor. The first is to right-click the server in Object Explorer:

The other common way to launch it is from the Toolbar:

Note that if you connect to more than one server in Object Explorer, Activity Monitor will connect to whichever one you have selected any object from within.

Activity Monitor puts a bit of a load on the server that it's connected to but I generally don't find it too bad. However, please don't leave it running and go on using other tabs. I've been to sites where there are many copies of it running all the time from several users. Don't do that.

I don't find most of the graphs at the top very useful, apart from perhaps the processor time.

It will show you if the server is running flat out.

The list of Processes is more interesting. If you right-click any session, you get these options:

The Details link will show you the last command executed on that connection. Take note that this doesn't mean it's still running. You can also kill the process (obviously carefully), and you can connect SQL Server Profiler to the server and filter the session immediately, to see what it's doing.

The columns are filterable.

They show you a list of values currently in that column, plus an All, and a choice of Blanks (rows with no value in this column) or NonBlanks (rows with anything in this column). They start as All.

For a simple example of using this though, we could pick sessions that have any type of command running, by choosing Task State of RUNNING.

One that I often use this view for is to look for blocking issues. Every process that's blocked by another process will tell you that. Generally, what I'm looking for is the head of a blocking chain ie: who's the main culprit that's blocking everyone.

For that, I look for a value of 1 in the Head Blocker column. Unfortunately, the way it's designed, you can't select that value until there is a row with that value.

The Application Name, Database Name, and Login can all be pretty useful as well.

The Resource Waits section is only mildly interesting.

The information there is at a bit of a coarse level to be really useful to me. Note that on this system, Buffer I/O is top of the list, but the cumulative wait time (since the server restarted) is small. Over time, if the system has been up for a long time, you can start to get a feel for the main waits in here, but be aware that there are a lot of values that can appear in here, without actually being an issue.

The Data File I/O list is a little more interesting:

This will show you how busy each data and log file is, for all databases. I generally sort it by Response Time (ms) descending. The value here is then basically the latency for the I/O on that file. In this example, it's 8 milliseconds. That's ok.

The Recent Expensive Queries list is interesting. The information is available from the system DMVs but this puts some useful data in an easy to get location:

It keeps updating this over time. Note that this won't be showing you queries currently running, just ones that were expensive and finished recently. If you right-click one, you can either look at the query text, or check out the execution plan that was being used.

The final section with Active Expensive Queries will only have data if you're using Live Query Statistics. I'll write about it another day.

Shortcut: Using the built-in web browser in SQL Server Management Studio

SQL Server Management Studio (SSMS) is a flexible tool. One thing that often surprises people is that it hosts a version of Microsoft Internet Explorer, right inside the application.

Why would SSMS have a web browser I hear you ask?

Well this web browser lets browse URLs, and reference links, without leaving the tool.

You can open it directly by choosing Web Browser from the View menu:

On my machine, it's under the Other Windows section. In fact, it's the only window there on my machine, which makes you wonder why it has a separate section in the first place.

It opens like any other tab:

In SSMS query tabs, you'll notice that there is an auto-detection of URLs and auto-formatting/linking of them:

If I execute that as a query, it doesn't get hyperlinked in the results:

Or on the Messages tab:

If you hover over the URL within the query though, you'll see that you can Ctrl-Click to open it:

If you use Ctrl-Click the link, it will open in the embedded web browser:

I'm hoping there will be an option to get a much more up-to-date browser though as support for Internet Explorer is waning everywhere.

Opinion: Don't buy hardware before a Proof of Concept

Just a short post today to call out something that I'm seeing again and again. It's where organizations purchase all their hardware and software platforms before they start to carry out a proof of concept. This is a very poor option.

I was reading the data strategy for a global company that I was doing consulting work for. They were proudly introducing the new strategy yet I was sitting looking at it, trying to work out what they were thinking. The first step of their plan was to buy everything they needed. The second step was to carry out a proof of concept to see how it would all work (presuming it would work suitably at all).

This is ridiculous.

In that case, I think what's happening is that the IT management wants to seem proactive, buying hardware and software platforms is what they are experienced at, and they want to look like they are "doing something".

Image by RawPixel
Image by RawPixel

Yet, invariably, this locks them into decisions that aren't in the best interests of the organization. Instead of making sensible decisions, they end up making decisions, based on what they have already committed to. And the more expensive that purchase was, the more they will try for years to justify the expenditure decision that they made. Every choice will later be taken, based upon how well it fits with their existing purchase.

Don't do this.

Do everything you can to carry out the proof of concept without buying anything that locks you into a decision path.

Shortcut: Play a sound when a query completes in SSMS

In a previous post, I mentioned that when a long running query completes, I might not have been waiting around for it, and so I wanted to know when it completed.

But sometimes I do wait around for a query to complete, yet I'm distracted by other things and don't realize that the query has actually completed. That's not surprising because if a query takes a long time, I'm probably going to go on with other work while that's running.

So I want to get a prompt when the query finishes.

SQL Server Management Studio (SSMS) does provide an option for this. In Tools, Options, Query Results, there is an option to Play the Windows default beep when a query batch completes.

I do wish it was a stronger option than this but at least it's a start.

What I'd particularly like would be:

  • Ability to play a different sound, not just the default beep.
  • Ability to enable/disable this on a specific query window once a query is already running.

Having this on all the time would be quite annoying, so I'd be pretty selective about using it in its current form.

SDU Tools: Format Datatype Name in T-SQL

We've been building tools to create scripts for various SQL Server T-SQL objects for a long time. Part of scripting a table is the scripting of the data type. That means its datatype name, precision, scale, and maximum length.

In our free SDU Tools for developers and DBAs, we added a scalar function called FormatDataTypeName ot make that easy. It takes the following parameters:

@DataTypeName sysname – the name of the data type
@Precision int – the decimal or numeric precision
@Scale int – the scale for the value
@MaximumLength – the maximum length of string values

You can see the outcome in the main image above.

You can see FormatDataTypeName in action here:


To become an SDU Insider and to get our free tools and eBooks, please just visit here:

Opinion: Take career risks while you can

In the 1980's and 1990's, part of my time was spent as a lecturer and tech services manager at a university. I particularly loved working with final year students and their project work. At our regular meetings though, I also often got into discussion with the students about their career plans, as they were about to graduate. What amazed me was how many super-bright students were looking to take incredibly boring jobs working on ancient technologies, in what were basically programmer graveyards, and when I asked them why they were intending to go there, invariably they'd tell me that they thought those jobs would be long term and low risk.

So a bright twenty-one year old student with no kids, no mortgage or other real commitments, and nothing much in the way of ties, was selling their soul for a low risk job.

Don't do this !

Take career risks while you can!

I understand that once you have a partner, kids, mortgage, etc., you don't have the freedom to try things. I've seen people I work with who are so tied to receiving a pay every fortnight that they can't make good decisions about their careers.

Image by Kevin Delvecchio
Image by Kevin Delvecchio

But if that's not you, don't sell yourself and your future short.

If you're worried about taking a risk, ask yourself what is the worst possible thing you can imagine happening, and then ask yourself if there is any way you could survive it, even if it's painful. And if you could survive it, don't hesitate to try. Anything that happens probably won't be as bad as you've imagined anyway. More importantly, you might just fly.

Image by The Nigmatic
Image by The Nigmatic

No-one flies day one, not even birds. While you can, just try things.

One of the saddest things I hear from older people is regret for the things they felt they could have done but didn't try.

Image by Ozan Safak
Image by Ozan Safak

Don't let it be you with the regrets.

SQL: Adding many single column SQL Server indexes is usually counterproductive

I've just finished delivering a bunch of presentations across New Zealand, and one of the sessions that I've delivered in several places is Things I Wish Developers Knew About SQL Server. In that session, I mentioned briefly that most single column SQL Server indexes that people create are at best pointless, and at worst counterproductive.

I often see people making a poor attempt at indexing, and when they don't know what they need to do, they often add individual indexes on many columns in their tables. This isn't a good idea.

After those sessions I had a few emails from people puzzled about my comments. In particular, one had thought he'd heard that if you had a whole bunch of indexes like that, that SQL Server would mix and match what it needs out of those indexes.

However, if you look at the usage statistics for those indexes, chances are they are never seeked, looked-up, or scanned. They are only updated (ie: causing work to need to be done for no good outcome).

There are times when SQL Server will decide to read data from more than one index on a table but they aren't common situations. Even when it does this, it's rarely a particularly selective (ie: desirable) outcome. Yes, I can construct queries where it would do that, but they aren't common queries.

SQL Server will at times read an entire index (ie: an index scan). Usually when it does this, it's because it wants the value from every row, and the index happened to contain the required column. It's way less work to read the whole index than it is to read the whole table.

Rather than a large number of single column indexes, what you generally need is a set of indexes that cover the most important queries. You can't cover every query but you can do the most important ones.

Another key skill is being able to merge multiple indexes into a single index, without adversely affecting performance much. We call this, along with removing duplicate and subset indexes, "rationalizing" your indexes.

If you'd like to learn more about how to do this work, we have just released a new online on-demand course:

Designing Effective Indexes for SQL Server

We think it's great value normally but until the end of September, it's offered at a 30% discount.

If you need to find out which queries are causing you issues, and that you should focus on, we also have a new free online on-demand course:

4 Steps to Faster SQL Server Applications

I'd encourage you to take a look at them. You'll find these courses, and our other upcoming courses at





Shortcut: Change connection in SQL Server Management Studio

I commonly run into a few connection-related scenarios:

  • I'm working on a large query and need to run it against several servers, not concurrently, but one after the other.
  • I've just made a database connection, and got my query ready, only to discover that I've connected to the wrong server.

Either way, what I've seen people do in these scenarios is to:

  • Select all the text in the current query
  • Copy it
  • Open a new query window
  • Paste the code

That's all good but SQL Server Management Studio (SSMS) has had a simpler way of doing this for quite a while.

If you right-click in the middle of a query window, you can choose to change the connection. Here's an example. I opened a new query window and entered a query, only to find that it connected to the Azure server that I was working with earlier. The easiest option is to right-click and choose to change the connection:

After you click Change Connection, you can log on again:

And then I can continue in the same query window as before. This is particularly useful if I need to run some code against a test server, and once I've decided that it was correct, I can just change the connection and connect to the "real" server.

At the bottom-right of your query window, you can always see which server you are connected to:

And at the bottom-left, you can see your connection state:

SDU Tools: Extract trigrams from strings in T-SQL

Fellow MVP Paul White and others have discussed the indexing of small segments of strings, to make fast indexes that I think are somewhat like full-text indexes but potentially more efficient. Three character substrings are common, and are known as trigrams.

I've experimented with these at a number of site and seen really interesting (good) results.

In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrigrams to help with this. You can pass it a string, and it will pull it apart for you, and return the set of trigrams. This would make it much easier for you to experiment with these types of indexes.

You can see ExtractTrigrams in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

Opinion: Don't have blind faith in hardware

There was a discussion the other day (on one of my lists), where someone pointed out that over many years, they had made tape backups, sent them via secured transport, and then paid for high-tech storage. And when they went to restore one of the tapes recently, there was nothing on the tape.

Over the years, I've lost count of the number of times I've heard stories like this. Long ago, I realized that you must never trust hardware.

There are just too many situations where you can get fooled. I'll give you a couple of examples. (I have many, many more).

I used to do maintenance on mini-computers at HP in the mid-1980's. Tape drives were some of the most fascinating pieces of equipment that I used to work on. The capstan-based devices were something, but the high speed vacuum drives were really wonders of engineering.

This shot from the movie Terminator shows one of the tape drives that I'm talking about, and some of our disk drives that were used with those systems:

I loved the way this guy was poring over the tape drive:

No idea what he would have been thinking about when looking at the drive.

But it reminded me of one of the HP tape drives I worked on where the erase head was stuck hard on. That meant that as the tape went through the drive, it first erased it as planned, then wrote to it, then read it back to make sure it was written correctly. All good to that point. But when it got to the end of the tape and rewound, it erased the entire tape.

I also saw a cartridge tape drive that was a multi-track drive. It had a problem where it wasn't stepping between tracks. So it would write one track, turn around and not step to the next track when it was supposed to, and then write straight back over the same track it just wrote, again and again.

You simply can't trust hardware. No matter how sophisticated it is, no matter how much error checking it has, etc. there's always something unplanned that can happen.

Unless you are periodically restoring your data (wherever it's stored), you have no idea if it's any good.

And importantly, you need to restore it on another system, not the one that you created the backups on. You don't want to find out later that the only device that could restore your backups was the one that created them, and it's now died.

Finally, you need to do a complete rebuild periodically. I've seen far too many systems where even when the restore was successful, people find that there was something else that should have been backed up but wasn't, and they now need it.

Don't be any of these people. You can get burned.