This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

---

Pop quiz: What happens when you promise to write 30 posts on a topic, but you don’t plan properly and lose a bit of steam toward the end?

Answer: A recap post, a day or two early! Woo hoo!

 

The month started with a few introductory posts. A history of monitoring (from my point of view), some information on Who is Active’s design philosophy, and a few words on the Who is Active license.

The key takeaways from these first posts? The monitoring story has been pretty poor for most of SQL Server’s history. It’s better now, but it’s still a lot of work. Who is Active was created to make things more accessible. It’s free, with a few restrictions designed to keep people who make money from monitoring from making money on Who is Active. Anyone else can and should use and enjoy.

 

Next I got new users up to speed. My post on installation covered security considerations, followed by a post on why you may not see as much data as you're used to when you first start using Who is Active. I continued with a post on the various options supported by the procedure (plus a nod to the online help mode) and another post describing the default output columns.

One of the more important posts of the month described how Who is Active treats active requests and sleeping sessions. This can be a confusing area for new users, since it's not always obvious what's going on unless you're looking in the right place at the output (the [status] column).

 

Once the refresher was finished I began covering some of the basic options exposed by the procedure. Filters were one of the first things I implemented, so it was a logical place to start (the "not" filters came much later). Seeing the query text is a key part of the procedure's functionality, and that was next on my list. If you're not sure what the [sql_text] column means, this post will set the record straight.

The ability to see the query text is nice, but so is the ability to get a query plan--and of course Who is Active supports that too. And since everything a query does is transactional, the procedure allows users to collect information about what those transactions are up to.

 

The next section of the month was all about query processing. I started with a couple of background posts: One on how the query processor works (at a somewhat high level), and another on what blocking really means.

Who is Active exposes two different modes that help with analysis of real-time waits. My 15th post of the month covered the default--lightweight--collection mode. My followup post covered the more extensive full waits collection mode.

 

Sometimes a query plan and wait information isn't quite enough to diagnose a performance issue. What if the query plan usually works well, but isn't performing properly only in this specific case? Who is Active has a feature to help you figure that out. And what if you need a bit more information on some of the settings that the request is using?

 

Mining wait information yields some amazing returns. One of the additional pieces of information that you can get is the actual name of the object that's causing a block. Another thing you can see is the exact node within a query plan where a task is currently doing work. You can (and should!) also use waits to figure out whether tempdb is properly configured. There are still more tricks you can play with waits, but they'll have to wait for a future post.

 

Like wait information, lock data can also be mined. However, there is so much locks information that the real challenge is rendering it in a human-readable manner. Who is Active does that using a special custom XML format. The procedure also helps with another type of blocker analysis, in the form of finding the "lead blocker."

 

Once you've figured out what information you're interested in, why not set up the output so that you can see the information the way you want to see it? Who is Active helps with this by allowing users to dynamically customize output columns and sort order in a few different ways. And while you could always run Who is Active interactively, that might get a bit dull. Automated data collection is a much nicer methodology in many cases.

 

Most of the numbers shown by Who is Active are cumulative. But oftentimes it's more interesting to compare data between two snapshots. The procedure can help you do that, using its delta mode.

 

What fun would a month of Who is Active be without an official release? v11.00 has numerous new features, several of which were perfected this month thanks to feedback I received on the various posts in the series. It's great to have such enthusiastic users! Some of these new features didn't make it into prior posts, and other existing features are a bit hidden. So I did a writeup covering the more important things that you may not have noticed while working with Who is Active.

 

I finished the month with a discussion on security for slightly tougher situations. I hope that the module signing technique will allow Who is Active to be used in a number of places where security auditing requirements have made things difficult.

 

And that's that. A month of activity monitoring with Who is Active. Thank you for reading! Next, this text is going to be edited, expanded in places, and put into a much more comprehensive form. Watch your RSS reader for more information.

Enjoy!

This post is part 28 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

---

Over the past month this series has covered a lot of ground. After writing almost 30 blog posts on the stored procedure it’s interesting to look back and see the fact that it’s gotten much bigger and more complex than I realized. Kind of like watching a plant grow, you don’t notice the day-to-day changes until one day you go to water it and see that it’s taken over your entire garden.

A few things that I’ve added along the way have been especially useful, but there hasn’t been a good place to mention them thus far in the series. With just three posts left for the month, now is the time.

 

Always Show Blockers

In a post earlier this month I talked about filters. Filters let you decide exactly what you want to see, and what you not don’t want to see (“not” filters). But sometimes you have no choice: if you’re filtering so that you only see session 123, and it’s being blocked by session 456, you’ll also see information about session 456. The idea is that you should always get enough context to fully evaluate the problem at hand. Even if it means that you see more information than you were intending to see the first time around. You probably would have asked for 456 next anyway.

 

“RUNNABLE” Waits

In @get_task_info = 2 mode, you may see waits called “RUNNABLE.” This could strike you as an oddity, given that there is no such wait type in SQL Server. I wanted to show tasks on the runnable queue, and making up a fake wait type seemed like a reasonable way of accomplishing the task. In practice, it has worked extremely well—I’ve used this feature countless times to help understand scheduler contention on a SQL Server instance.

 

Accurate CPU Time Deltas

CPU time is a tricky metric. It gets handled by Who is Active’s delta mode, and has for several versions. But historically, never very well. The data simply isn’t represented in an easily-obtainable fashion in the core DMVs. Recently I decided to dig deeper into this problem and discovered that I could get better numbers from some of the thread-specific DMVs. They’re cumulative numbers, based on the lifetime of the entire thread—not too good for the usual Who is Active output. But for snapshot and delta purposes, just about perfect. Meaning that in v11.00 of Who is Active, you can see the [CPU] column show a value of 0, while the [CPU_delta] column shows a value in the thousands. It’s not a bug. It’s a feature. (It really is!)

 

Text Query Plans and the XML Demon

SQL Server 2005 introduced query plans as XML. Management Studio knew how to render these plans graphically. And we were able to pull the plans from DMVs. Life was great. Except, perhaps, when you actually wanted to view one of these plans, and you had to save the thing out to a .SQLPLAN file, close the file, then re-open it. That’s about three steps too many for my taste, so I was overjoyed when the Management Studio team decided to wire things up the right way in SQL Server 2008. Click on a showplan XML document, see a graphical plan. Simple as that.

Unfortunately, the XML data type has its own issues, including one particularly nasty arbitrary limitation that has to do with nesting depth. The idea is to make sure that XML indexes don’t crash and burn too often (not a big concern for me, given that I’ve never seen one used in a production environment—but I digress). The problem is that query plans are heavily nested. And to get that nice graphical plan workflow, SSMS needs the plan rendered as XML.

In prior versions of Who is Active I gave up and returned either an error or a NULL. But in v11.00 I decided to make things a bit better. If the nesting issue occurs, Who is Active will now return the plan as XML encapsulated in some other XML in a text format, along with instructions on how to view the plan. This won’t give you a nice one-click experience, but it will give you the ability to use Who is Active to see some of the bigger plans that are causing performance issues.

 

Service Broker Needs Love Too

One of the most interesting features of Service Broker is activation. But a vexing design choice on behalf of the Service Broker team was to make activation procedures launch as system sessions. This means, among other things, that prior versions of Who is Active filtered them right out of the default view. To see them you’d have to enable @show_system_spids. And then you’d have to ignore all of the other system stuff. And you’d get woefully bad time information (no, the activation process hasn’t been running for 25 days; that’s the last time you restarted the SQL Server instance). In Who is Active v11.00 this has been fixed. Service Broker activation processes are now displayed by default along with other user activity. And I found a way to fix the timing issue, thanks to some advice on Twitter from Remus Rusanu, one of the guys who originally worked on Service Broker. So if you’re using activation and monitoring with Who is Active, life is good.

 

Homework

The homework section of this series is officially closed. Your assignment is to go enjoy the rest of your day.

This post is part 26 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

---

The CPU is pegged. Your hard disks are fried. Memory consumption is through the roof. Who is to blame?!?

Well that’s easy. Blame the session with the most CPU consumption. Blame the session doing the most I/O operations. Ask Who is Active what’s going on—it’ll tell you...

Clearly session 52 is the problem here. It’s consumed over 2,000,000 milliseconds of CPU time. Session 53, on the other hand, has consumed a paltry five seconds. No brainer, right?

Except that it’s not.

Session 52? It’s in a wait state. (WAITFOR, to be exact.) Not consuming any CPU time at all at the time I ran Who is Active. Session 53? Using plenty. So why the discrepancy? Does Who is Active have a major bug?

The fact is—and this will be obvious to a lot of you, so forgive me—most of the metrics reported by Who is Active are cumulative. They’re totals from the entire session, or the entire lifetime of the request (depending on what’s going on). In the case of session 52 in the image above, that much CPU time was consumed over a five-minute period prior to the point in time when I ran Who is Active. Session 53, on the other hand, is currently consuming CPU time. But how do I figure that out?

The answer: delta mode. This feature is something for which you can thank Jimmy May, who kept telling me about his “SQL Deltoids” script that he’d written back in the bad old days of SQL Server 2000. “If only there were a way to apply that script to the SQL Server 2005 DMVs,” he lamented one day. So I added the functionality into Who is Active. It’s quite simple, and surprisingly effective.

To use delta mode, simply invoke Who is Active’s @delta_interval option. Setting an interval will cause the procedure to execute its main logic branch, then wait the specified amount of time—a number of seconds between 1 and 255—before running the same main logic branch again. All of the numeric values that changed during that period will be compared, and the changed numbers will be output in a series of columns named with _delta at the end.

For example, here are the same two sessions as above, viewed in delta mode:

EXEC sp_WhoIsActive
    @delta_interval = 5

During the five-second delta interval, session 52 consumed no CPU time. Session 53, on the other hand, consumed over 5,000 milliseconds of time. If the CPU is pegged, 52 is no longer contributing—53 is the session of interest.

Enabling delta mode will add some or all of the following columns to the output, depending on what other options have been set:

• physical_io_delta
• reads_delta
• physical_reads_delta
• writes_delta
• tempdb_allocations_delta
• tempdb_current_delta
• CPU_delta
• context_switches_delta
• used_memory_delta

By leveraging these delta columns in conjunction with the @sort_order option, it’s easy to quickly see which sessions are currently consuming your valuable server resources—which is generally more interesting than seeing which sessions may have been consuming resources but are now waiting or sleeping.

Note: The various sessions and requests you'll see in delta mode pertain to the information captured after the wait interval. Information about requests that were running as of the first collection but are not running as of the second is discarded. New requests that started after the first collection will be shown, but with delta values of NULL. Locks, transactions, and other optional information, will also be captured only for the second collection, since no delta calculation is possible for those metrics (at least, not yet).

 

Homework

Delta mode, in its current form, is only really useful if you have requests that last longer than a second (at minimum—I usually do five or ten-second intervals). What kind of workload do you see on your servers? Is delta mode as it exists today something that you’re making use of (or that you’ll start making use of, now that I’ve written a post about it)?

This post is part 24 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

---

Early in the process of creating Who is Active it became clear that there was no way everyone was going to agree.

With each release I received requests to move some column so that it would show up on the lefthand side, or to change the sort order, or to make some other modification that someone felt was necessary to help them more easily digest the data. The problem: it was impossible to accommodate all of these requests. So I decided to go with a self-service model.

In order to allow users to dynamically customize the output (without touching any code), the stored procedure exposes three options: @output_column_list, @sort_order, and @format_output. Each of these is discussed in the following sections.

@output_column_list controls not only whether or not certain columns will be shown in the output, but also the order in which the columns are displayed. The correct argument is a list of bracket-delimited column names (or partial names with wildcards). Delimiters are not necessary (use whatever delimiter you like, or none at all; they’re ignored). The key to successfully using this option is to remember that inclusion of columns in the output is additive: many columns (such as [additional_info]) are only added to the output if both the correct options are enabled for the stored procedure and the columns are included in the column list. If you start modifying the list and don’t take this into account, you may not see the columns you’re expecting when you go back later and start changing options. To keep things flexible, make sure to use wildcard columns, especially a generic wildcard ([%]) at the end.

Using the column list can be as simple as specifying the exact columns you’re interested in:

EXEC sp_WhoIsActive
    @output_column_list = '[tempdb_allocations][tempdb_current]'

Notice that no delimiter is used here. Again, any extraneous text aside from the column specifiers is ignored, so the following call is equivalent:

EXEC sp_WhoIsActive
    @output_column_list = 'this[tempdb_allocations]is[tempdb_current]ignored'

Easier than specifying exact column names is to use wildcards that match the pattern of the columns you’re interested in:

EXEC sp_WhoIsActive
    @output_column_list = '[tempdb%]'

All three of these calls will yield the same output, similar to the following:

Of course, this will return only these two columns. Generally I’ll use the column list feature just to move things around so that I don’t have to do as much scrolling, and in those cases I almost always want everything else, too. That’s where the generic wildcard comes into play:

EXEC sp_WhoIsActive
    @output_column_list = '[tempdb%][%]'

Now the tempdb-specific columns appear on the lefthand side, and all of the other columns follow, in a default order.

 

The next option we’ll cover is @sort_order. This option controls the order of the rows output by the stored procedure. Like @output_column_list, the input is a list of bracket-delimited column names. In this case, wildcards are not supported, but the keywords ASC[ENDING] and DESC[ENDING] are supported after the column name.

The following call returns data sorted by [login_name] ascending (ascending is default, so the keyword is optional), with ties broken by [session_id] descending:

EXEC sp_WhoIsActive
    @sort_order = '[login_name][session_id] DESC'

As before, delimiters are optional and are ignored. Please note that the current betas of Who is Active have bug where multi-column sorts like this one are not properly honored. That issue will be fixed in the next version of Who is Active.

Sorting is especially useful when doing any kind of comparison of the various requests currently running. I use it extensively in block leader mode ([blocked_session_count] DESC), and it is quite handy in delta mode—which will be covered in a post in a few days.

 

The final option this post will cover is @format_output. This one is based on the fact that Who is Active is designed to leverage SSMS as a “graphical user interface” of sorts. Query text is sent back in an XML format, numbers are formatted as right-justified strings, and elapsed time is formatted in the more easily-digestible [dd hh:mm:ss.mss] form.

If you’ve been using Who is Active for a while you’ve probably noticed the right-justified numbers:

This format was suggested fairly early on by Aaron Bertrand, and is one of my favorite things about the stored procedure. It makes it much easier to pick out bigger numbers when you’re looking at a large set of data. By default, SSMS uses a non-fixed width font for grid results, so the default argument to @format_output, 1, takes this into consideration. But some people—like Aaron Bertrand—change the SSMS settings and use a fixed width font instead. If you’re one of these people you can use an argument of 2 to get nicely-formatted numbers. If you don’t change the argument you might notice that the numbers don’t seem to properly line up when you’re working with a set of data containing numbers of greatly differing size.

Still other people don’t like the right-justified numbers or are doing collection to a table (see tomorrow's post), and so formatting can be completely disabled by using an argument of 0. It’s up to you...

 

Homework

Today’s homework is to enjoy your Sunday. You’re a busy DBA; you’ve earned a break. Get outside and off of the computer!

This post is part 22 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

---

Note: Before reading this post, please download the most recent Who is Active build, which includes a key fix for the locks mode.

---

Blocking has been a theme of a couple of recent posts in this series. And that’s not even the end of it. Helping you find and properly evaluate blocking issues is a core part of Who is Active’s raison d'être.

Behind every block is something causing the block. Something that, without which, there could be no block. And that thing is called a lock.

Locks are synchronization objects. Their mission in life is not to give you headaches when dealing with blocking issues, but rather to help maintain the ACID properties that are a big part of the reason that DBMS technology is so popular. For locks in particular this means the “I” property: Isolation. (And, to a lesser extent, the "C" property: Consistency.) Locks keep readers from reading data that writers haven’t finished writing, and they keep writers from overwriting data as it’s being read or written by someone else. This is a good thing. Failure to take these kinds of precautions would result in chaos. And a lot of really bad data.

When a DBA sees blocking, her first instinct is to eliminate it. Kill the blocker! Add a NOLOCK hint! Change the processes around! But blocking is not necessarily a bad thing. Blocking means that your data is being protected. Readers are getting consistent results. Writers aren’t overwriting each other. Everything is as it should be—even if your queries are waiting an inordinate amount of time for data.

When you see blocking, the correct move is not to eliminate it, but rather to evaluate it. Figure out what’s causing the blocking. Figure out why (or whether) it’s necessary, and what the alternatives might be. Then—and only then—should you start killing sessions, adding hints, or taking similar action.

Evaluating blocking can be a painful experience. The sys.dm_tran_locks view (formerly syslockinfo) contains a large number of columns. Many of these are numeric values that need to be referenced elsewhere in order to be meaningful to the average human. And even then, it’s simply not a very nice user experience...

Glancing at this list of lock information, it’s impossible to tell what’s going on. (The query I ran to pull up this list returned 2700 rows.)

Who is Active solves this problem by putting locks into a somewhat more human-readable form: a custom XML format. The stored procedure does all of the work of going to the databases with locks and decoding the numbers. So instead of seeing something like 72057594038845440, you’ll see something like Sales.SalesOrderHeader. Whether or not you think that XML in general is a very readable format, the fact that the various object names have been resolved for you makes it a lot better than a straight query against sys.dm_tran_locks.

To get lock information, use Who is Active’s @get_locks = 1 option. This will add a column called [locks] to the output. The column is typed as XML, and you can click on it to see the full contents. The document will have one root node per database in which there are locks. For the table listed above, the collapsed nodes look like this:

Under each database node is one node that represents locks on the database itself, and a node called Objects that contains subnodes for each object in the database that’s locked. These are grouped by object name and schema name:

Any given object can have multiple types of active locks issued against it at one time, so inside of the Object nodes are one or more Lock nodes:

This format allows for quick and simple exploration of the various locks that are active on behalf of your session. Each Lock node has an attribute called request_status. If its value is “GRANT,” the lock is held by the session. If its value is “WAIT,” the request is waiting to acquire the lock.

A full description of the various lock types is well beyond the scope of this post, but most of them are documented in the Books Online entry for sys.dm_tran_locks.

A cautionary note: Using the @get_locks option can seriously slow down Who is Active. The sys.dm_tran_locks DMV is known to be one of the slowest DMVs, and in some cases it can hold a huge number of rows. I have seen numerous cases where a simple SELECT * against the DMV took 20 or more minutes to finish. When dealing with locks, which can change rapidly, that’s far too much elapsed time for the results to be meaningful. Recent Who is Active builds include blocked object resolution mode, which is designed to be a much lighter weight alternative to using the full locks mode.

 

Homework

Using the various DMVs it’s possible to write a number of queries that can deeply analyze block situations. Although Who is Active already does this, it’s an interesting exercise. Can you write a query that shows all blocked requests, the lock mode for each request, the blocker session or request that the blockees are waiting on, and the blocker lock mode?

This post is part 20 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

---

Query plans are packed with information about what’s going to happen, or what has happened. But they’re markedly quiet about what is happening.

This is probably best illustrated with an example. Consider: you’re sitting there at your desk one morning, enjoying a nice hot cup of coffee, when the phone rings. It’s Steve, the harried middle manager who sits on the 2nd floor. “Help!” Steve shouts into the phone, making you flinch and immediately move the headset away from your ear. “My report has been running for 10 minutes! I have a meeting in three minutes! Is it going to finish?”

You calmly open Management Studio, fire up Who is Active, and glance at the various columns. No blocking. No extreme waits. Nothing too interesting in the plan. Just a big query chugging along.

At this point there’s really not much you can do. But there is one hint that might help give you some more insight. Who is Active collects, along with CXPACKET waits, a node identifier. These are displayed along with the wait when @get_task_info = 2 is used. This can help you figure out approximately what your plan is up to right now.

Too see this in action, first create a blocking situation so that we have something to look at:

USE AdventureWorks
GO

BEGIN TRAN
GO

UPDATE TOP(10) Sales.SalesOrderDetail
SET
    OrderQty += 7
GO

Next, in a new window, fire up the following query:

USE AdventureWorks
GO

SELECT
    *
FROM
(
    SELECT
        sh.*,
        sd.ProductId,
        ROW_NUMBER() OVER
        (
            PARTITION BY sd.SalesOrderDetailId
            ORDER BY sd.ProductId
        ) AS r
    FROM
    (
        SELECT TOP(1000)
            *
        FROM Sales.SalesOrderDetail
        ORDER BY
            SalesOrderDetailId DESC
    ) AS sd
    INNER JOIN Sales.SalesOrderHeader AS sh ON
        sh.SalesOrderId = sd.SalesOrderId
) AS s
WHERE
    s.r = 1
GO

Assuming that you have a multicore machine, the plan for this query will have a few different parallel sections. Which is what we need for CXPACKET waits. Let’s see what Who is Active has to say about the situation:

EXEC sp_WhoIsActive
    @get_task_info = 2,
    @get_plans = 1

The query has 10 tasks. One of them waiting on a lock; we’ll ignore that one since we’ve created the lock to “pause” things in this case. The other nine are all waiting on CXPACKET waits—but there are two different groups. The first group consists of eight tasks, all waiting on node 17. The second group has only a single task, and it’s waiting on node 0.

So what are these nodes? Bring up the plan, hover over any of the iterators therein, and you’ll see:

Every iterator in the plan has an associated node identifier. These range from 0—for the leftmost node in the plan—on up, as you move to the right. CXPACKET waits are associated with exchange (parallelism) iterators, and the wait information includes the actual node identifier from the plan. By using this information you can begin to understand the flow of data through your larger plans, as task waits move from node to node.

This is certainly not a perfect solution, and chances are very good that you won’t be able to give Steve from the 2nd floor the exact amount of time remaining. But every bit of information helps, and in this case you may be able to come up with a reasonable estimate. Even if he ends up running to the meeting with only half of the report printed.

 

Homework

Yesterday’s task was to cause an error to occur in Who is Active’s blocked object resolution mode. Easily enough accomplished by creating a permission issue:

USE master
GO

CREATE LOGIN active_error
WITH PASSWORD = 'abcd1234!!!!'
GO

GRANT VIEW SERVER STATE
TO active_error
GO

EXECUTE AS LOGIN='active_error'
GO

EXEC sp_WhoIsActive
    @get_task_info = 2,
    @get_additional_info = 1
GO

Running this will cause a new node to appear inside of <block_info> in the [additional_info] column:

<query_error>The server principal "active_error" is not able to access the database "AdventureWorks" under the current security context.</query_error>

Today we'll take a break from the homework to celebrate the middle of the work week and two-thirds of this thirty-part series behind us.

This post is part 18 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

---

Sometimes you just need more.

With over 20 columns in the default output plus several more than can be dynamically enabled and disabled, Who is Active was already overwhelming enough for certain users. But requests kept pouring in for various additional information—metrics to help debug trickier situations and edge cases.

Rather than cluttering the output, I decided to create a single, special-purpose column for everything that’s not quite important enough to be on its own in the output. The [additional_info] column is an XML column that returns a document with a root node called <additional_info>. What’s inside of the node depends on a number of things, but by default you can expect to see:

• text_size
• language
• date_format
• date_first
• quoted_identifier
• arithabort
• ansi_null_dflt_on
• ansi_defaults
• ansi_warnings
• ansi_padding
• ansi_nulls
• concat_null_yields_null
• transaction_isolation_level
• lock_timeout
• deadlock_priority
• row_count

Rather than repeat the documentation, I’ll point you to the BOL entry for sys.dm_exec_requests for information about what all of these mean. Most of them are various settings that can be manipulated by a given user, batch, or stored procedure. They impact the results of a query and, in some cases, its plan. So it’s a good idea to be able to pull them up when needed.

Beyond these, the [additional_info] column might also contain various other pieces of information, depending on which options are selected and what happens to be running. For example, if a SQL Agent job is running [additional_info] will be populated with:

• job_id: the identifier for the job in MSDB
• job_name: the name of the job, from MSDB
• step_id: the identifier for the job step in MSDB
• step_name: the name of the job step, from MSDB
• msdb_query_error: included when an error occurs that renders Who is Active unable to resolve the job and step names

Today’s post is just a quick overview; I’ll cover other things you can expect to see in [additional_info] in a later post. In the meantime, how do you get all of this information? Simple:

EXEC sp_WhoIsActive
    @get_additional_info = 1

 

Homework

What information would you like to see added to the [additional_info] column? Let me know in the comments below. I’ll carefully consider every suggestion but there are no guarantees; remember, too much data is just as bad as not enough (and maybe worse). I plan to be just as careful with [additional_info] as I am with the rest of the columns output by the stored procedure.