SQL Middle Earth - putting that hobbit id to pragmatic use

What's a hobbit in SQL Server?


A 'hobbit' is a HOBT (heap or btrieve) value.
There, doesn't that clear things up?


For more about what a HOBT is and why it was named thus, refer to 
http://thehobt.blogspot.com/2009/02/what-heck-is-sql-server-hobt-anyway.html.


I will hold forth on the usefulness I found for understanding what a hobbit is as follows, if you use
a commercial monitoring tool for SQL Server, (such as SQL Monitor from Red Gate Software), when your monitoring encounters a deadlock and you start to drill in you may notice a line such as the one below:




SPID Victim Lock details                         Statement type
138                      dbid: 5, object id: 72057594727628800, index id: (0100e420fa5a) UPDATE Line #: 1
169 Victim process dbid: 5, object id: 72057594727628800, index id: (0100e420fa5a) UPDATE Line #: 18


Now that object id, 72057594727628800, is not a real object id, you won't find it in the sysobjects table.  It is a hobbit object id.  When I click on the line for SPID 138, I find that the HOBT id is associated with object id 440335512, and when I click on the other line, I get 1644479766.


But I don't want to take a lot of time poking all the links and buttons in SQL Monitor (Oh, sometimes I do because I need to know the details).  But if your company is like the company I'm currently employed at, the applications running on SQL Server are most likely 3rd party software.  In other words, a) you don't have the source code; b) even if you did the vendor would take a dim view of you rummaging around in and worse, changing their code; c) you are too busy doing what your employer pays you to do, why should you work on the vendor's code for them?


So the most urgent thing for me is simply to find out as much as I can, as quickly as I can for the deadlock so  that I can open an issue on the Vendor's trouble ticket system and then they can go figure out why the code is prone to deadlocking.


Here's a nifty little bit of code that has helped me out in doing this:


USE [SERVICE] -- <-- your database name goes here


DECLARE @hobbit BIGINT
DECLARE @hobbitobj NVARCHAR(255)
DECLARE @hobbitidx INT


DECLARE @victimhobbit BIGINT
DECLARE @victimhobbitobj NVARCHAR(255)
DECLARE @victimhobbitidx INT


DECLARE @victimobject BIGINT
DECLARE @object BIGINT




SET @victimhobbit = 72057594727628800 
SET @victimobject = 1644479766
SET @hobbit = 72057594727628800 
SET @object = 440335512


SET @hobbitobj = (SELECT [object_id] FROM sys.partitions WHERE hobt_id = @hobbit)
SET @hobbitidx = (SELECT index_id FROM sys.[partitions] WHERE  hobt_id = @hobbit)
SET @victimhobbitobj = (SELECT [object_id] FROM sys.partitions WHERE hobt_id = @victimhobbit)
SET @victimhobbitidx = (SELECT index_id FROM sys.[partitions] WHERE  hobt_id = @victimhobbit)




SELECT 'victim'msg, hobt_id, object_name(p.[object_id]) [object], p.[object_id], index_id FROM sys.partitions p WHERE hobt_id = @victimhobbit
SELECT hobt_id, object_name(p.[object_id]) [object], p.[object_id], index_id FROM sys.partitions p WHERE hobt_id = @hobbit
SELECT 'victim'msg,[object_id], [name], [index_id], [type_desc] FROM sys.[indexes] AS i WHERE [object_id] = @hobbitobj AND [index_id]=@victimhobbitidx
SELECT [object_id], [name], [index_id], [type_desc] FROM sys.[indexes] AS i WHERE [object_id] = @hobbitobj AND [index_id]=@hobbitidx
SELECT 'victim'msg, name, id FROM sysobjects WHERE [id] = @victimobject
SELECT name, id FROM sysobjects WHERE [id] = @object


The results tell me about the name of the object associated with both 'hobbits', in this instance 2 SPIDS wanted access to the exact same object at the exact same time; moreover they were attempting to access the same object on the same index, and finally I have the name of the 2 stored procedures that were running.


So I can quickly copy this information into a ticket, the most important and useful part is that I can provide the vendor with the names of the stored procedures that deadlocked.


This has helped me by equipping the vendor with the information they need to start figuring out what they can do to reduce or eliminate deadlocks in their code.


Have fun!