Search results matching tags 'Monitoring' and 'DMVs'

Your Transaction is in Jeopardy -- and You Can't Even Know It!

Adam Machanic — Wed, 27 Jun 2012 01:10:00 GMT

If you're reading this, please take one minute out of your day and vote for the following Connect item:

https://connect.microsoft.com/SQLServer/feedback/details/444030/sys-dm-tran-active-transactions-transaction-state-not-updated-when-an-attention-event-occurs

If you're really interested, take three minutes: run the steps to reproduce the issue, and then check the box that says that you were able to reproduce the issue.

Why?

Imagine that ten hours ago you started a big transaction. You're sitting there waiting for it to finish, and it's running, and running, and running. At some point, you notice that the drive with the transaction log has filled up, so you create a second log file. And the transaction is sitting there running, and running, and running. Is it still doing work? Or did it catch the low disk space issue and start rolling back? Wouldn't it be great if you could actually answer that question? Are you surprised to learn that you can't answer that question?

SQL Server currently has a few DMVs that are supposed to tell us the status of a given request, transaction, and so on. These DMVs are unreliable in the vast majority of situations. That means that we are unable to answer important questions like, "is my transaction still doing any work, or did it die three hours ago?"

The SQL Server team needs to fix this. End of story. Please vote and help me convince the powers that be to do the right thing.

A Warning to Those Using sys.dm_exec_query_stats

Adam Machanic — Fri, 23 Apr 2010 02:15:00 GMT

The sys.dm_exec_query_stats view is one of my favorite DMVs. It has replaced a large chunk of what I used to use SQL Trace for--pulling metrics about what queries are running and how often--and it makes this kind of data collection painless and automatic. What's not to love? But use cases for the view are a topic for another post. Today I want to quickly point out an inconsistency.

If you're using this view heavily, as I am, you should know that in some cases your queries will not get a row. One such case, as it turns out, is any time an ALTER TABLE appears in your batch. "No big deal," you might be thinking. "I don't regularly alter tables in stored procedures." But think again. Do you ever create a temporary table using SELECT INTO? Do you ever create indexes on the resultant table? I know I do--and quite often those indexes are primary keys, or unique constraints.

The interesting thing is that CREATE INDEX does not suffer from this problem. You can create unique indexes all day long and still get entries in the query stats DMV. And thus the inconsistency: Using ALTER TABLE to create a primary key or unique constraint on a temporary table is, for all intents and purposes exactly the same thing as creating a unique index using CREATE INDEX. But one works with the query_stats DMV and the other doesn't.

It's not difficult to imagine how this inconsistency arose; just rather annoying in practice. I went through a bunch of code today and changed several instances of ALTER TABLE to CREATE INDEX, and my monitoring scripts are already working better as a result. I recommend that you take the time to do the same, if you use this view.

To conclude this post, a quick repro so that you can see the issue:

SELECT
    'abc' AS a
INTO #x

ALTER TABLE #x
ADD UNIQUE (a)

SELECT *
FROM sys.dm_exec_query_stats
WHERE
    plan_handle IN
    (
        SELECT
            plan_handle
        FROM sys.dm_exec_requests
        WHERE
            session_id = @@SPID
    )

DROP TABLE #x
GO


SELECT
    'abc' AS a
INTO #x

CREATE UNIQUE INDEX whatever
ON #x (a)

SELECT *
FROM sys.dm_exec_query_stats
WHERE
    plan_handle IN
    (
        SELECT
            plan_handle
        FROM sys.dm_exec_requests
        WHERE
            session_id = @@SPID
    )

DROP TABLE #x
GO

Enjoy, and best of luck in all of your monitoring endeavors.

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.

Click here to download Who is Active? v9.57

Since the last release--v8.82, from August of this year--I've made a number of modifications to the script, resulting in six interim "beta" releases. The uniting theme of all of these changes is more, better quality data, faster.

More data.

Two new core options were added at the behest of users: @show_sleeping_spids and @show_system_spids. These options cause the procedure to return information that wasn't previously available via Who is Active, about--you guessed it--sleeping and system sessions, respectively.
Another major change was adding a feature so that the script now shows blocking sessions whether or not they're included in the default filter criteria. This is not something you turn on or off--it just happens--and will ensure that if you're debugging a blocking scenario you'll automatically have all the information you need.
And for you query plan geeks out there, the full wait stats collection mode (see the "Faster data" section below) now returns node identifier information with CXPACKET waits--helping you to track progress of tasks as a plan is executed.
Finally, a small modification. The online help system (@help=1) now returns information about both the available input parameters as well as all of the available output columns.

Better data.

A few minor bugs were fixed, mostly having to do with the evils of MARS and the fact that the DMVs don't properly deal with MARS sessions in many cases.
Workarounds were also added for inconsistencies in how the DMVs report SQL handles, even without MARS.
Two features were added to help you get only the data you need when you need it, and not the data you don't:

Dynamic sort ordering, via a parameter called @sort_order, lets you pass in a list of columns and column directions by which to sort the output.
And "not" filters, implemented using parameters @not_filter and @not_filter_type, work exactly the opposite of regular filters. These are useful in those cases where you have a bunch of sessions that aren't of interest, and you don't want them cluttering your output.

Finally, I've changed the default output column order to something I think is a bit more useful. Don't like my selections? No problem--override me using the @output_column_list parameter.

Faster data.

This is the area in which I made the most modifications. A monitoring tool borders on useless when it takes a minute or more to return key metrics when your server is on fire, and alas, previous versions of Who is Active were doing just that for some users.
I've made major changes to the core queries in this version of Who is Active, bringing down query times from minutes to a few seconds in many cases.
In conjunction with these changes I added a new lightweight wait stats collection mode, which is the new default. This mode collects only the top non-CXPACKET wait, giving preference to blocked waits, so that you can see the worst problems without having to sort through a lot of output that may or may not apply.

If you miss the complete stats collection mode, it's still there--simply set @get_task_info=2 when calling the procedure.

I'm quite happy with this release, and I hope that it will help people quickly solve a number of tricky SQL Server problems. As always, your feedback is very much appreciated! Leave me a comment here, e-mail me (my address is in the script), or track me down at a conference. Most of the features in the past few versions are a direct result of requests I've gotten from users.

A huge thank you to those who tested and gave me feedback since the last version! Aaron Bertrand, Rajiv Jain, Michelle Ufford, Uri Dimant, and everyone else, I really am thankful for your efforts. To these people and all of my readers, I wish you a happy December and a prosperous 2010.

Until next time, enjoy!

Click here to download Who is Active? v9.57

Please ignore the text below. Putting in it for search purposes.

sp_whoisactive whoisactive sp_who sp_who2 sp_who3 sp_who4 sp_who5

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, with occasional numbers skipped or major version incrementation if I do a really major change.

If you've been watching the SQLblog closely you may have noticed that I've spun up a "beta" program where I've been posting intermediate "builds" of the stored procedure. These builds represent the latest updates and are published pretty much immediately, which means I haven't tested them enough to declare them stable. Builds posted here on my main blog--such as this one--have been tested fairly heavily and I believe them to be in very good shape.

In the case of v8.82 I've made only one minor change in the past month and a half, and in the meantime I've been heavily using the code and have not encountered any issues. Nor have I received any change requests. So I am declaring v8.82 stable and ready to roll. You can get it from the following link, or at the bottom of this post.

Click here to download Who is Active? v8.82

So What Has Changed?

If you've been watching the beta builds you will have noticed the change log I've been keeping since v8.67. But that leaves around 20 changes unaccounted for. Between 8.40 and 8.67 I did a number of things: I implemented a number of minor bug fixes and several workarounds for strange ways that data is exposed by the various DMVs.

One change you'll quickly notice is that all of the numbers have been right-aligned. This is something that was originally requested by Aaron Bertrand, who later was unable to use the feature as-is because, as it turns out, his font choice in SSMS is not compatible with the way I made the feature work. I did a poll here on SQLblog and discovered that Aaron is fairly unique with regard to fonts, so I'm keeping the feature as-is. Personally, after the 10 minutes it took me to get used to seeing the data a new way, I found it to be a really great enhancement. I'm quite interested in hearing your feedback on how you like the change.

From 8.67 on I have a comprehensive change log, and next I'll go over some of the more important enhancements, in chronological order based on when I implemented them. There is a central theme here: each of these changes directly resulted from someone giving me feedback. But more on that below.

At the request of Joe Sack I added a parameter called @help which, when set to 1, returns information about all of the available options. Online help, in other words. This is much easier than pulling up the definition for the stored procedure and I've found it to be quite helpful on a number of occasions since making the change.

Jimmy May asked for an option to help find "block leaders", SPIDs that are at the front of the blocking chain. From this the @find_block_leaders parameter was born. It causes the stored procedure to add a new column, blocked_session_count, which is an integer representing the number of SPIDs blocked downstream. Sort by this descending (@sort_column='[blocked_session_count]', @sort_direction='DESC') and the block leader(s) will all sort to the top. I've already been able to use this one to debug a production blocking problem in a matter of around 30 seconds, which may have otherwise taken me minutes. Thanks, Jimmy!

After an e-mail thread with Michelle Ufford I decided to support simple wildcards in the @output_column_list parameter. Interested primarily in information about tempdb? No problem: @output_column_list='[temp%][%]'. Now everything about tempdb will show up in the leftmost columns, and every other column will show up afterward. No need to mess around and try to re-construct the complete column list.

Around this time I received a few requests for better filtering capabilities. I removed the @spid parameter and replaced it with @filter and @filter_type. The default filter type is "session" in order to support some form of backward-compatibility. Other valid filter types are "program", "database", "login", and "host". Wildcards are supported, so if you're interested in everyone logging in with hosts that start with the letter "C", I've got you covered: @filter='C%', @filter_type='host'.

An e-mail exchange including both Jimmy May and Joe Sack got me really interested in the Runnable Queue, and yet another enhancement was created. I added a wait type called "RUNNABLE" to the wait_info column. This wait type doesn't actually exist, but is rather an indication of how long tasks have been sitting on the queue. It's a way to quickly see when you're experiencing scheduler pressure.

Next Steps

At some point I'm going to fully document this thing. Really. But to be honest I'm much more interested in continuing to enhance it. If you've found it to be useful, please let me know. If you have feature requests, definitely let me know what they are. And if you find a bug, drop everything and e-mail me right away (well, within reason). My e-mail address is in the stored procedure on purpose; most of the enhancements to this stored procedure have come about thanks to users, and I really do want to hear from you. Don't be shy.

So without further ado, enjoy. And thank you to everyone--both those listed and not listed in this post--who helped make this version what it is. I really appreciate your help.

Working with SQLFool's Index Defrag Script

AaronBertrand — Mon, 13 Apr 2009 14:16:00 GMT

Last week I decided to play with SQLFool's Automated Index Defrag Script and found it very usable and worthwhile. Terrific work Michelle! The script rivals Ola Hallengren's broader maintenance scripts that I use often.

On smaller databases it ran quite quickly and with the correct and desired effects. On larger databases, due to the amount of time it takes to run, I found a couple of things lacking on the reporting side. Not serious things, mind you, and nothing that is really all that hard to work around. But I thought I would share them anyway, in case you were using the script and coming across similar issues.

What index is currently being reorganized?

There is a great output table that records the index reorganize tasks, called dbo.dba_indexDefragLog. This records an entry as each index reorganize statement completes. A great enhancement would be to record an entry when each index reorganize command *starts*, so that I can track its progress (for log entries like this I typically have a Start/End column pair, with Start populated by default, and End updated after the task is complete). In the meantime, assuming you know the session_id (SPID) that is running the script, you can figure it out easily by using the following query... which not only tells you which index is being reorganized, but also an estimated completion time (lucky us, ALTER INDEX REORGANIZE is one of the commands that shows percent_complete in sys.dm_exec_requests):

SELECT
   r.percent_complete, 
   estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP),
   t.[text]
FROM
   sys.dm_exec_requests AS r
CROSS APPLY
   sys.dm_exec_sql_text(r.[sql_handle]) AS t 
WHERE
   r.session_id = <SPID>;

Before vs. After Fragmentation

The output table dbo.dba_indexDefragLog shows the fragmentation of the index *before* the reorganize happened. An excellent addendum to the logging table would be to show the fragmentation level *after* the reorganize was complete. Currently, to see the before and after picture, I would need to go look at sys.dm_db_index_physical_stats for each index (since you cannot use CROSS APPLY against this particular DMV). Paul Randal to the rescue: he explains how to work around this issue. So taking that information, I created the same function, and then wrote the following, which queries the dbo.dba_indexDefragLog table and then runs a CROSS APPLY against the new function to get the *after* picture.

IF OBJECT_ID('dbo.dba_index_physical_stats') IS NOT NULL
   DROP FUNCTION dbo.dba_index_physical_stats;
GO

CREATE FUNCTION dbo.dba_index_physical_stats
(
   @database_id        INT,
   @object_id          INT,
   @index_id           INT,
   @partition_number   INT,
   @mode               SYSNAME
)
RETURNS @result TABLE
(
   database_id                     SMALLINT,
   [object_id]                     INT, 
   index_id                        INT, 
   partition_number                INT,
   index_type_desc                 NVARCHAR(60),
   alloc_unit_type_desc            NVARCHAR(60),
   index_depth                     TINYINT,
   index_level                     TINYINT,
   avg_fragmentation_in_percent    FLOAT,
   fragment_count                  BIGINT,
   avg_fragment_size_in_pages      FLOAT,
   page_count                      BIGINT, 
   avg_page_space_used_in_percent  FLOAT,
   record_count                    BIGINT,
   ghost_record_count              BIGINT,
   version_ghost_record_count      BIGINT,
   min_record_size_in_bytes        INT,
   max_record_size_in_bytes        INT,
   avg_record_size_in_bytes        FLOAT,
   forwarded_record_count          BIGINT
)
BEGIN
   INSERT INTO @result 
       SELECT * FROM sys.dm_db_index_physical_stats
       (
           @database_id,
           @object_id,
           @index_id, 
           @partition_number,
           @mode
       );
   RETURN;
END;
GO

-- now I can run queries like:
SELECT TOP (10) 
   l.databaseName,
   l.objectName,
   l.indexName,
   l.dateTimeStart,
   dateTimeFinish = DATEADD(SECOND, l.durationSeconds, l.dateTimeStart),
   l.durationSeconds,
   fragmentation_before = l.fragmentation,
   fragmentation_after = s.avg_fragmentation_in_percent,
   page_count_before = l.page_count,
   page_count_after = s.page_count
FROM 
   dbo.dba_indexDefragLog AS l
CROSS APPLY
   dbo.dba_index_physical_stats
   (
       l.DatabaseID,
       l.ObjectID,
       l.IndexID,
       l.PartitionNumber,
       NULL
   ) AS s
ORDER BY
   indexDefrag_id DESC;

Obviously this function is pretty expensive, which is why it would be better if it were called once per index at the end of the task, instead of querying every time. But I don't want to touch Michelle's script... if she thinks these enhancements are worthwhile, I am sure she will add them.

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!