THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

The Key to Your Locks (A Month of Activity Monitoring, Part 22 of 30)


Published Friday, April 22, 2011 11:00 AM by Adam Machanic



jai said:

many thanks..

April 22, 2011 12:19 PM

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM

Mike McAllister said:


Here's a (modified) extract from a locking situation I was looking at over the weekend. sp_whoisactive reports the following on session 1, the blocker:-

   <Object name="MyTable" schema_name="dbo">


       <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="1" />



It reports the following in session 2, which is blocked:-

   <Object name="MyTable" schema_name="dbo">


       <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="4" />

       <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />



I understand that Sch-S blocks Sch-M. What I don't understand is why session 2 is shown as having been both granted and waiting on a Sch-M lock on the MyTable object?

May 26, 2015 10:35 AM

Adam Machanic said:


This has to do with lock partitioning. One lock will be taken by the modifying request per partition, but only the specific partition with a competing lock will be blocked.

More information here:


May 26, 2015 2:07 PM

Mike McAllister said:

Adam - thanks, appreciate the info.

May 27, 2015 9:04 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement