SQL Swiss Army Knife #11 - Locking, blocking and active transactions - Updated

Hello all,
Here is another post on SQL scripts that may help DBAs, following the series "SQL Swiss Army Knife". I’ve been using and tweaking this script for years now, and I’ve been asked to share it several times, because I can get a quick overview of query execution on a given SQL Server instance. The script leverages on some known DMVs (sys.dm_exec_requests, sys.dm_exec_sql_text, sys.dm_exec_sessions and sys.dm_tran_locks) to report on all running requests, waiter and blocking, SP execution statistics, and list sessions that have been granted locks or waiting for locks.

The output will look something like below, and you can get further detail on statements and plans by clicking on the XML links:

On the Waiter and Blocking Report section, you can look into the queries that are blocking or blocked in further detail, by clicking on the XML link, like this:

Also, just for SQL Server 2012, you can get some details on Stored Procedures statistics, like below:

And finally, a list of sessions that have been granted locks or waiting for locks:

Hope you find it useful.

EDIT (08-04-2012): Added more useful information to output, plus revised for SQL 2012 compatibility.

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.