When you run a long running query, sometimes you might get lucky enough to capture hobit_id pop up in the Activity Monitor. This is interesting.

Run a long running query, when it is waiting for the data pages to be pulled into memory you see the wait like PAGELATCHIO or LCK_M_X in the ‘Wait Type’ column and sometimes you can also see hobt_it=232134654 in the ‘Wait Resource’ column. I’ve wondered what this hobt meant.

HOBT_ID (pronounced as ‘hobbit’), refers to Heap or B-Tree ID. As you know, data in tables is stored as HEAP or a B-Tree (Balanced tree); If there is a clustered index on the table, it is stored as a B-Tree, if not it’s a HEAP. Either way, they are stored in data pages that are all put together and referred to as one logical location. This logical location of data pages (or index pages) are referred using an ID called hobt_id. In essence, its just an ID used to refer to that logical storage.

When a table is partitioned, each partition will get a partition_ID and HOBT_ID, but when a table is not partition, it still has partition ID and HOBT_ID as it is referred to as a single partition table.

Thanks Chad

Hope this helps,
_Sqltimes