NOTE: v8.40 is outdated at this time. Please try v9.57, which you can find here.
It has been only a month and a half since I posted the last version of Who is Active? (v7.30),
but in that time I've made a huge number of changes, fixes, and
enhancements. The new version, v8.40, is faster, more robust, and
includes a few exciting features.
Click here to download Who is Active? v8.40
Following is a list of some of the
things I've done with it, in no particular order, along with
descriptions where applicable.
Added a collection interval
option, @DELTA_INTERVAL. This option causes the script to collect
numeric metrics -- such as reads, writes, and context switches --
twice. The script will wait between collections, based on the duration
passed into the parameter (in seconds). The script will then calculate
the differences (deltas), between the first and second collections, for
any requests that were active for each collection. These deltas will be
output in a new set of columns, reads_delta, writes_delta,
context_switches_delta, etc.
For example, to use the default options and wait two seconds between collections, you would use the following:
EXEC sp_WhoIsActive @DELTA_INTERVAL = 2;
If
you played with Who is Active? in the past you know that the script
outputs a lot of data in a lot of different columns. I started feeling
that the column list was getting unwieldy, so I implemented dynamic column lists
as a fix. There are two changes here. First of all, the default column
list will only contain columns associated with those features you have
turned on. So for example, if you don't have @GET_LOCKS enabled, the
locks column won't appear.
The second part of this fix is a new feature to make things even more dynamic: custom column lists. I sent a slightly earlier test version to Jimmy May,
and his response was something along the lines of, "it's pretty good,
but I really don't like the column order." To make Jimmy happy, I
implemented the @OUTPUT_COLUMN_LIST option. To use this option, simply
pass in whatever columns you would like to see, in whatever order you
would like to see them, and the script will do the rest. Note that the
output will be the intersection of whatever options are enabled and
whatever columns are found in the list.
Here's an example I've been using in my own work recently:
EXEC sp_WhoIsActive
@OUTPUT_COLUMN_LIST =
'[login_name],[dd hh:mm:ss.mss],[sql_text],[reads],[writes],
[context_switches],[physical_io],[wait_info],[blocking_session_id],
[tempdb_writes],[tran_log_writes],[query_plan],[session_id]';
Another feature I added that makes things a bit more dynamic and customizable is the ability to sort the output
based on whatever column and direction you would like. Interested in
seeing the requests that are currently blocked? No need to scroll, just
ask for them to sort high:
EXEC sp_WhoIsActive
@SORT_COLUMN = '[blocking_session_id]',
@SORT_COLUMN_DIRECTION = 'DESC';
While
viewing the results on the screen in SSMS is great, sometimes I like to
capture the results to a table for later analysis. So I added a few
features in this most recent version to support automated collection.
To
begin with, I realized that the default, nicely-formatted output of the
script, while great for on-screen viewing, isn't good if you want to do
your own analysis later. So I added a new option, @FORMAT_OUTPUT, that
lets you control whether the output is or is not formatted. The default value for the parameter is 1, keeping with the previous behavior.
I
also figured that if you want to do some later analysis it might be
nice to know what time period you're working with. So I added a column
called collection_time, which outputs a datetime instance representing
the time that the script finished running.
Taking things
one step further, I decided that with all of the dynamic column options
it would be a pain to put together a table matching the output schema
if you start playing with different settings. So I made things easy; no
need to figure out what the output looks like; the script will write a CREATE TABLE statement for you. Simply enable the @RETURN_SCHEMA option and collect the output value from the @SCHEMA parameter.
Finally,
due to nested INSERT/EXEC limitations, it's impossible to insert the
result into a table when calling the stored procedure. No worries, the
script will insert its output into a table for you. Just tell it where to send the data, via the @DESTINATION_TABLE option.
Here's
an example of asking for the unformatted output schema, creating a
destination table based on the current day, and collecting data 10
times, waiting 15 seconds between each collection:
DECLARE @destination_table VARCHAR(4000);
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112);
DECLARE @schema VARCHAR(4000);
EXEC sp_WhoIsActive
@FORMAT_OUTPUT = 0,
@RETURN_SCHEMA = 1,
@SCHEMA = @schema OUTPUT;
SET @schema =
REPLACE
(
@schema,
'<table_name>',
@destination_table
);
EXEC(@schema);
DECLARE @i INT;
SET @i = 0;
WHILE @i < 10
BEGIN;
EXEC sp_WhoIsActive
@FORMAT_OUTPUT = 0,
@DESTINATION_TABLE = @destination_table;
SET @i = @i + 1;
WAITFOR DELAY '00:00:15'
END;
GO
One of the key reasons I created the original version of Who is Active? was to help identify blocked and blocking SPIDs. And I thought it worked pretty well for that purpose until recently, when I discovered that sys.dm_exec_requests fails to properly identify blocking when queries go parallel. Oops. Fixed in the new version; it now uses the waiting tasks DMV for this purpose, instead of the requests DMV.
As
part of this process I discovered that the joins to the tasks DMVs were
slightly flawed in v7.30, so I fixed them up. And I was able to play
some games to improve performance--enough that I decided to eliminate
the @TASK_INFO option, making this the default behavior. This means
that you'll always get correct blocker identification from Who
is Active? v.8.40, along with a few bonus task-based metrics including
physical I/O stats and the number of context switches.
Interested in information about transactions? So are most DBAs, which is why I promoted the transaction start time, which was previously embedded in the tran_log_writes column, to a top-level column
of its own. This will still populate only with transactions that have
actually done a write, but from what my testers tell me this is okay
behavior. Let me know if you disagree. While I was in there I played
even more performance games, and greatly improved the speed of the
query when the @GET_TRANSACTION_INFO option is enabled.
A few smaller fixes are also worth mentioning here. First of all, I tested the script against a case-sensitive instance, and fixed a few issues there. So it should work for you no matter what collation you're using. Second, thanks to a discussion I had with Roman Nowak, I was able to finally solve the entitization problem that plagued the "clickable" query output produced by the script. Third, I put on my security hat and added some code to verify input arguments, as well as did a review of all of the dynamic SQL used by the script to make sure that it is not injectable.
Thank
you to everyone who sent me feedback and/or feature requests! If you
have an idea for th script, please make sure to send it my way. I have
quite a bit of momentum at the moment and want to keep going and see
just how far I can take this thing. I can only do this with your help.
Enjoy!
Click here to download Who is Active? v8.40