Microsoft – SQL Server – Interpret the waitresource column

Found myself running a long, resource intensive query against MSSQLServer and wanted to determine whether it was taken so long ’cause I was getting blocked.

Turned out that I was doing the blocking.  But, that is another story.

Anyways running a  query against master.dbo.sysprocesses revealed entries in the waitresource column.  The entries were listed as n:n:n

Persuing the Internet revealed that the waitresource column contains the hbotid value.

The poorly formatted Stored Procedure listed below finds the Object name that is referenced by the middle value in the waitResource table.

IF Object_id('dbo.sp_FindObjectBasedOnHobtID') IS NOT NULL


      DROP PROC dbo.sp_findobjectbasedonhobtid



CREATE PROCEDURE dbo.Sp_findobjectbasedonhobtid (@hobt_id BIGINT)


    SELECT             AS objectname,

           p.object_id        AS objectid,

              AS indexname,

           p.index_id         AS indexid,

           p.partition_id     AS partitionid,

           p.partition_number AS partitionnumber,

           p.rows             AS numberofrecordsinpartition,

           i.data_space_id    AS dataspaceid

    FROM   sys.partitions p

           INNER JOIN sys.objects o

                   ON p.object_id = o.object_id

           INNER JOIN sys.indexes i

                   ON p.object_id = i.object_id

                      AND p.index_id = i.index_id

    WHERE  (( p.hobt_id = @hobt_id ))


    SELECT          AS objectname,

           o.object_id     AS objectid,

           AS indexname,

           i.index_id      AS indexid,

           NULL            AS partitionid,

           NULL            AS partitionnumber,

           NULL            AS numberofrecordsinpartition,

           i.data_space_id AS dataspaceid

    FROM   sys.objects o

           LEFT OUTER JOIN sys.indexes i

                        ON o.object_id = i.object_id

    WHERE  ( ( o.object_id = @hobt_id )

             AND ( i.index_id IN ( 0, 1 ) ) )


EXEC Sp_ms_marksystemobject



GRANT EXEC ON dbo.sp_findobjectbasedonhobtid TO [public]



  1. Decipher WaitResource
  2. What is hbotid in MS SQL Server v2005

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s