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

Blog do Ezequiel
SQL Server Insights



Latest Updates
06/02/2015 - Latest update on the AdaptiveIndexDefrag procedure v1.6.1. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

11/03/2015 - Updates to Maintenance plan deployment scripts available HERE

17/05/2015 - The SQL Swiss Army Knife Series Index

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

Rate This
  • Comments 3

EDIT (12-04-2013): Added page type information (PFS; GAM or SGAM) when wait type is PAGELATCH_ or PAGEIOLATCH_ .

EDIT (23-05-2013): Fixed parse page issue.

EDIT (17-10-2013): Added memory grant info, statements to blocking and blocked sections of blocking report, and fixed head blocker info.

EDIT (09-12-2013): Fixed blocking section showing non-blocked sessions also

EDIT (09-04-2014): Added information to blocking section, and fixed conversion issues

EDIT (09-12-2014): Handle illegal characters in XML conversion

Hello all,   
Following the series "SQL Swiss Army Knife", here is another version of this script that I’ve been using and tweaking for years now. This way I can get a quick overview of query execution on a given SQL Server instance, from SQL Server 2005 up to SQL Server 2012.

The script leverages on some known DMVs (sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_os_tasks, sys.dm_exec_query_plan, sys.dm_db_session_space_usagesys.dm_resource_governor_workload_groups (for 2012) and sys.dm_tran_locks) and DMFs (sys.dm_exec_sql_text and sys.dm_exec_query_plan) to report on all running requests, waiter and blocking and SP execution statistics.

The first section will report on all requests. In here there is info on running batches, the statement in the batch that is currently running and its status. Also, there is information about the cached query plan, current I/O, CPU and time stats, the current wait type, resource (if any) and other useful data.

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



The second section is the Waiter and Blocking Report, where we find the queries that are blocked, such as the spid, its wait type, resource and time. Still on the blocked query, the associated batch, blocking elapsed time and database name. On the blockers, there is information on the spid, if it is the head blocker in a given blocking chain and what is the blocker batch. Additional information on the involved hosts and programs is also available.

As before, you can get further detail by clicking on the XML links:



And last, just for SQL Server 2012, you can get some details on Stored Procedures execution statistics from the sys.dm_exec_procedure_stats DMV, like below:



That’s it for now, hope you find it useful.

Until next time!

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.

Social Media Sharing
Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Can't download the file, since it points to your local drive. Please fix the download, thanks

  • Thanks for that! All fixed now!

  • EDIT (30-04-2014): Detailed categorization of memory related waits


    The topic of Waits and

Page 1 of 1 (3 items)