Post reply

Finding the wait resource from the sys.dm_exec_requests DMV

  • September 10, 2010 at 1:07 pm

    #146952

    Does anyone have a good way of finding the actual object name of the wait resource from the sys.dm_exec_requests dmv? I have read that you can run a DBCC page to find the object ID of the table on the page and then query the object_name function from their, but I was wondering if anyone was able to find an way that i would be able to display the object name in the query below.

    SELECT session_id,

    DB_NAME(database_id),

    wait_type,

    wait_time,

    wait_resource, ---Would like to display object name here

    blocking_session_id,

    st.text FROM sys.dm_exec_requests r

    CROSS APPLY

    sys.dm_exec_sql_text(sql_handle) AS st

    ORDER BY wait_time DESC

  • September 10, 2010 at 1:58 pm

    #1219474

    Depends what type the wait resource is. If it's a key, you could parse the value apart and join to the relevant system table, same if the resource is a table. For pages and extents you'd have to do DBCC Page, so no join possible there.

    Also note that it won't always refer to a table at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 10, 2010 at 3:07 pm

    #1219525

    Hi Rod,

    Please check if this helps

    http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!5494.entry

    Thank You,

    Best regards,

    SQLBuddy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply