How to Find Blocked Processes in SQL Server - Part 1

Blocking is part of a life in a production database management system, but it can lead to very visible performance issues. SQL MVP Jon Kehayias shows how to detect blocking using a couple different methods. Tomorrow we'll show you Part 2 (but you can watch it today if you want!).

Jonathan Kehayias is currently employed as a Principal Consultant and Trainer for SQLskills, one of the best-known and most respected SQL Server training and consulting companies in the world. Jonathan is a SQL Server MVP and one of the few Microsoft Certified Masters for SQL Server 2008, outside of Microsoft. Jonathan frequently blogs about SQL Server, presents sessions at PASS Summit, SQLBits, S...

BillRoger on 2/21/2009

short but nice information.

Luca Zavarella on 4/29/2009

Is possible to get the code I can see in the video?

Marc Voyer on 4/29/2009

too short

Diana Dee on 4/29/2009

Question 1's answer, though the best of the 4 suggested answers, is incorrect. A SELECT can be blocked; it is not waiting for exclusive access -- it is waiting for a SHARED lock.

Lonnie Meinke on 5/5/2009

Need a bit more info (such as on SPIDs < 50..etc...) and a little less "reading" to instill confidence from the listener....Good content though.

4B47653576 on 5/17/2009

does it need a restart to take effect? no details of how to set this specific trace up. Very good demo.

Syed Arshad on 8/21/2009

Sound is too bad

Ashish on 2/25/2010

need traceid script decription.

Jonathan Kehayias on 2/26/2010

The trace to capture blocked process reports was generated in SQL Profiler. Create a trace and add the blocked process report event to it. Then you can script it using File->Export->Script Trace Definition.

Debojyoti on 4/20/2010

Again a nice one , Cheers Jonathan

Andy on 5/5/2010

Good video, but it assumes knowledge of some facts e.g. SPIDS. It would benefit from an explanation of numerical values like IDs used in system table queries that are not self explanatory.

desmond on 7/2/2010

skimmed very quickly over the difficult sps sp_trace_setevent and sp_trace_create! Phew!

yeskay on 7/8/2010

excellent thanks

dineshbharat on 1/25/2011

good