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 a SQL Server MVP with experience in Database Administrator and Development. He is currently the SQL Database Administrator for OSI Restaurant Partners in Tampa, FL. His primary passion is performance tuning, where he frequently rewrites queries for better performance and performs in depth analysis of index implementation and usage. His current projects include building http:/...

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