Adam Machanic : monitoring, Scripts

Who is Active? v9.57: Fast, Comprehensive DMV Collection - What's Really Happening on Your Server?

Adam Machanic — Thu, 03 Dec 2009 19:59:00 GMT

UPDATE, April 28 2011: Who is Active v9.57 is outdated. Please use v11.00 instead. Happy December, SQLblog readers! My gift to you, just in time for the holidays: The newest "official" release of your favorite SQL Server activity monitoring stored procedure...(read more)

Who is Active? v8.82 - Harder, Better, Faster, Stronger

Adam Machanic — Thu, 20 Aug 2009 15:03:00 GMT

I've had a lot of great feedback since my last "official" Who is Active? release-- v8.40, back in March --and since then I've made 42 distinct changes, bringing us all the way to v8.82. My versioning scheme is quite simple: one change, one increment,...(read more)

Who is Active? v8.40 - Now With Delta Power!

Adam Machanic — Mon, 30 Mar 2009 16:41:00 GMT

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

Who is Active? v7.30

Adam Machanic — Wed, 18 Feb 2009 15:34:00 GMT

Who is Active? is a comprehensive DMV-based monitoring script, designed to tell you at a glance what processes are active on your SQL Server and what they're up to. It has a number of optional features so that you can get results quickly, or monitor deeply, depending on your needs when you happen to be using the script. Today I'm happy to present a new version, v7.30, which includes a few bug fixes an a new feature.

Click here to download Who is Active? v7.30

Now that I've covered that (for people who haven't been paying attention to this blog as of late), a bit of commentary. After posting Who is Active? v7.22 on December 31, I expected to receive a huge amount of feedback from the community. Alas, there were a few issues:

It turns out that December 31 is not a great day for a blog post that you would like to get lots of hits.
Apparently many readers were confused about where to actually download the script.
I was told that I need to document the thing better.

Luckily, some users did manage to download it, and I even received some feedback. Great feedback, primarily from a reader named Bill Kan. Thanks for all of your time, Bill! I also discovered a couple of issues on my own, and added a new feature that I thought of while watching Grant Fritchey do an excellent talk at New England Data Camp v1.0.

So now to address those three issues. I'm posting this post on a Wednesday, it's not a holiday, and it will hit midday, so that covers issue #1. For issue #2, see above and see below. I think the download links are painfully obvious at this point. And as for issue #3, well, I'm sorry to say that today is not the day for that one. I've done a fairly good job of documenting the script in the script (download it and take a look), and I think most readers will be able to feel their way around well enough. If you have any questions, post them here and I will be happy to answer. And eventually I will write up a comprehensive document on the thing. But until then, two out of three isn't bad...

Click here to download Who is Active? v7.30

Please give it a try and let me know what you think. I've run out of new feature ideas for the moment and could certainly use some input. If you are using it, please let me know what features you've found to be helpful and how you're integrating it into your work. I'm hoping to identify a number of use cases as talking points for the forthcoming document on the script and actual user feedback will be key.

And, most importantly, enjoy! As I mentioned in December, this script has become a vital part of my toolkit; I have to say that it's made certain aspects of monitoring a lot easier and more interesting for me and I hope it does the same for you.

A Gift of Script for 2009: Who is Active, Redux

Adam Machanic — Wed, 31 Dec 2008 14:49:00 GMT

Last year on December 31 I posted part of a larger monitoring script that I had been working on for a few months. I received lots of great feedback on the little script in both the comments and from people I was working with, and over the course of the last year I estimate that I have invested at least a couple of hundred hours in the script. The result of all of this time is attached, and I hope that this script is as useful for others as it has been for me; I've been using it pretty much nonstop while modifying it and it has become an indispensable part of my toolkit.

Alas, while I had planned to write a comprehensive blog post on the script for publication today, I simply don't have time to do it correctly at the moment--but I wanted to get the script out on the 31st in order to create a kind of tradition. So instead of telling you everything it can do, I will ask that you instead take a look inside the script, where I've done a fairly good job documenting both the parameters and the outputs. I'll do a followup post soon (I hope) and do a better job of telling you about the various options. In the meantime, I'll leave you with a list of the script's major features:

Wait stats collection, if you enable the GET_WAITS switch. This mode aggregates the various resources that the tasks associated with the session are waiting on, and lets you know how long the tasks have been waiting.
Collection of lock information, if you enable the GET_LOCKS switch. A lot of effort went into this particular feature--all of the locks for each session are aggregated in an XML format, and work is done to map the various identifiers associated with each lock to actual object names so that you don't have to do the footwork yourself.
Information about sleeping SPIDs holding open transactions, if you enable the GET_TRANSACTION_INFO switch. If you're being blocked by a SPID that is not active, you should be able to find out about it. This option lets you do it. As a bonus, it also pulls information about transaction log writes in any database in which the transaction has done a write.
Collection of the "outer" command/batch, if you enable the GET_OUTER_COMMAND switch. One of the comments on the script I posted last year asked why, if you issue SQL like "ALTER TABLE dbo.xx ALTER COLUMN yyy FLOAT NULL", you'll end up with statement text that looks like "UPDATE [zzz].[dbo].[xxx] SET [yyy] = [yyy]". This is an internal representation of what's actually happening, but in many cases is not esepecially useful for end-users. So if you want to see what was actually run, now you can.
Every expensive or potentially expensive option is now parameterized. I spent a lot of time tweaking performance and with no options running the thing is pretty fast. Enable some options--especially lock collection--and it will slow down considerably, so use these advanced features with care.

Enjoy, and please keep sending feedback! Let me know what else you would like to see, if you find bugs, etc. I'll post updated versions as major changes are made.

To finish I would like to thank a few of the people who had an impact on the evolution of this script over the past 12 months: Michael Condon, Erland Sommarskog, Aaron Bertrand, Louis Davidson, and Jonathan Kehayias.

Have a great 2009, everyone!