I was watching a server this morning, making sure that there was nothing untoward going on, and I caught sight of a LCK_M_S waittype with a very long number in the Resource column:
KEY: 26:72057594819903488 (6b0068a71fb9).
I watched a bit longer, and every time that LCK_M_S waittype came up, it was associated with the same KEY value. But what does that value represent?
Well, the 26 is the database ID. But what about that big number? According to the MS KB article (KB 224453) on “Understanding and resolving SQL Server blocking problems“, that is something called a hobt_id (or “hash value for index key”). The good news is that it’s simple to find out what that refers to. Simply run the following query:
SELECT * FROM sys.partitions WHERE hobt_id = <<long number>>
Here we have the object ID and index ID that are causing issues.
DBCC doesn’t throw up any issues; the indexes are OK; the table itself is small(ish) – approx 30k rows. I think all that was happening was that that particular table was getting hammered during a bulk upload.
