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

  BEGIN

      DROP PROC dbo.sp_findobjectbasedonhobtid

  END

go

CREATE PROCEDURE dbo.Sp_findobjectbasedonhobtid (@hobt_id BIGINT)

AS

    SELECT o.name             AS objectname,

           p.object_id        AS objectid,

           i.name             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 ))

    UNION

    SELECT o.name          AS objectname,

           o.object_id     AS objectid,

           i.name          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 ) ) )

go

EXEC Sp_ms_marksystemobject

  'dbo.sp_FindObjectBasedOnHobtID'

go

GRANT EXEC ON dbo.sp_findobjectbasedonhobtid TO [public]

go 


References

  1. Decipher WaitResource
    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/044d6a4b-da11-4c10-9a99-02fc86b67299/
  2. What is hbotid in MS SQL Server v2005 http://blogs.msdn.com/b/chadboyd/archive/2007/02/24/what-is-a-hobt-id-in-sql-2005.aspx

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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