Three columns resource_0, resource_1, and resource_2, returned from Extended Events which monitors the locking behavior,lock_acquired and lock_released, are always puzzling people. Actually, information of it already exposed in page 264, book Microsoft SQL Server 2008 Internals, wirtten by Kalen Delaney. In my first post of this series, I’ve also talked little bit about it. I decided to write a function to help people translate them into a human readable form. This function now can recognize lock resource for Object, HOBT, Page, Extent, RID, and KEY. for others, such as DATABASE and FILE, the formats are very straight forward, I did not include them in the function.
I am going to start a series of posts to talk about Locking and blocking in SQL Server, which is one of my favorate topics. As a SQL Server developer, many professionals are more focusing on programming languages, database designs, features (like Service Broker), writting SQLs to effectively manipulate data and process designs. They’re all correct. But one more thing is quite frequently getting neglected is locking. Especially after SQL Server 2005, Snapshot isolatioin level is used more frequent than others. It seems like SQL Server will never locked. Actually, that’s not the fact. Nothing has been changed from locking perspective before and after version 2005. After having a fully understanding of locking and blocking mechanism, you will find out you can easily write a high concurrency application, prevent excessive blocking, and design more sophisticated concurrent SQL process controls.