none
Extrapolate table from RID lock?

    Question

  • I have a blocked process report with the attached blocked process.  How do I evaluate the RID lock (RID: 8:1:4183:5) back to the actual table where the RID lock occurred?

    <blocked-process>
      <process id="process525b288" taskpriority="0" logused="0" resource="RID: 8:1:4183:5" waittime="29281" ownerId="5565538176" transactionname="SELECT" lasttranstarted="2013-09-19T06:29:58.990" XDES="0x1070a9780" lockMode="S" schedulerid="15" kpid="33500" status="suspended" spid="145" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2013-09-19T06:29:58.990" lastbatchcompleted="2013-09-19T06:29:58.990" clientapp="AppServer" hostname="WEB2" hostpid="4260" loginname="foouser" isolationlevel="read committed (2)" xactid="5565538176" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
       <executionStack>
        <frame line="52" stmtstart="3272" stmtend="3464" sqlhandle="0x03000800de2f7f458a234201bca000000100000000000000"/>
       </executionStack>
       <inputbuf>
    Proc [Database Id = 8 Object Id = 1165963230]   </inputbuf>
      </process>


    • Edited by scott_m Sunday, September 22, 2013 3:20 PM
    Friday, September 20, 2013 3:57 AM

Answers

  • Hi Scott,

    Try this:

    DBCC TRACEON(3604)
    
    DBCC PAGE (8, 1, 4183, 3)
    DBCC PAGE will in header information give you AllocationID which you can query to get allocation unit. From there you can get partition where you have object_id. Object_name (object_id) gives you the name of the table where the lock happened.


    Regards, Dean Savović

    • Marked as answer by scott_m Sunday, September 22, 2013 3:21 PM
    Friday, September 20, 2013 7:53 PM
  • select * from  sys.partitions p
    WHERE PartitionId = 115665624039424

    SELECT OBJECT_NAME(p.OBJECT_ID) AS 'tableName'
        , i.name AS 'indexName'
        , p.partition_number
        , au.type_desc
        , CONVERT (VARCHAR(6),
          CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
             SUBSTRING (au.first_page, 5, 1))) +
       ':' + CONVERT (VARCHAR(20),
          CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
             SUBSTRING (au.first_page, 3, 1) +
             SUBSTRING (au.first_page, 2, 1) +
             SUBSTRING (au.first_page, 1, 1))) AS 'firstPage'
        , CONVERT (VARCHAR(6),
          CONVERT (INT, SUBSTRING (au.root_page, 6, 1) +
             SUBSTRING (au.root_page, 5, 1))) +
       ':' + CONVERT (VARCHAR(20),
          CONVERT (INT, SUBSTRING (au.root_page, 4, 1) +
             SUBSTRING (au.root_page, 3, 1) +
             SUBSTRING (au.root_page, 2, 1) +
             SUBSTRING (au.root_page, 1, 1))) AS 'rootPage'
        , CONVERT (VARCHAR(6),
          CONVERT (INT, SUBSTRING (au.first_iam_page, 6, 1) +
             SUBSTRING (au.first_iam_page, 5, 1))) +
       ':' + CONVERT (VARCHAR(20),
          CONVERT (INT, SUBSTRING (au.first_iam_page, 4, 1) +
             SUBSTRING (au.first_iam_page, 3, 1) +
             SUBSTRING (au.first_iam_page, 2, 1) +
             SUBSTRING (au.first_iam_page, 1, 1))) AS 'firstIAM_page'
    FROM sys.indexes AS i
    Join sys.partitions AS p
        ON i.OBJECT_ID = p.OBJECT_ID
        And i.index_id = p.index_id
    Join sys.system_internals_allocation_units AS au
        ON p.hobt_id = au.container_id
    WHERE OBJECT_NAME(p.OBJECT_ID) = 'yourtable'
    ORDER BY tableName;

    Source http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by scott_m Sunday, September 22, 2013 3:21 PM
    Sunday, September 22, 2013 3:34 AM

All replies

  • You can identify a table by object id

    SELECT * FROM sys.objects

    What statement is sqlhandle

    SELECT *
    FROM sys.dm_exec_requests AS R
             CROSS APPLY sys.dm_exec_sql_text(0x03000800de2f7f458a234201bca000000100000000000000) AS ST
    WHERE plan_handle IS NOT NULL;

    RID -- Lock on a single row in a table identified by a row identifier

    USE master;
    GO
    EXEC sp_lock 145;
    GO

    >>RID: 8:1:4183:5

    database 8, fileid 1, page 41835

    Take a loom at DBCC PAGE command


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, September 20, 2013 4:15 AM
  • It looks the graph is incomplete.

    Can you just post the <resource-list> info from your graph atleast?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, September 20, 2013 4:24 AM
  • It looks the graph is incomplete.

    Can you just post the <resource-list> info from your graph atleast?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Here is the full blocked process report:

    <blocked-process-report>
     <blocked-process>
      <process id="process525b288" taskpriority="0" logused="0" resource="RID: 8:1:4183:5" waittime="29281" ownerId="5565538176" transactionname="SELECT" lasttranstarted="2013-09-19T06:29:58.990" XDES="0x1070a9780" lockMode="S" schedulerid="15" kpid="33500" status="suspended" spid="145" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2013-09-19T06:29:58.990" lastbatchcompleted="2013-09-19T06:29:58.990" clientapp="AppServer" hostname="WEB2" hostpid="4260" loginname="foouser" isolationlevel="read committed (2)" xactid="5565538176" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> 
       <executionStack>
        <frame line="52" stmtstart="3272" stmtend="3464" sqlhandle="0x03000800de2f7f458a234201bca000000100000000000000"/>
       </executionStack>
       <inputbuf>
    Proc [Database Id = 8 Object Id = 1165963230]   </inputbuf>
      </process>
     </blocked-process>
     <blocking-process>
      <process status="suspended" waittime="54395" spid="342" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-09-19T06:29:33.877" lastbatchcompleted="2013-09-19T06:29:33.877" clientapp="AppServer" hostname="WEB2" hostpid="11296" loginname="foouser" isolationlevel="read committed (2)" xactid="5565532343" currentdb="8" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128024">
       <executionStack>
        <frame line="107" stmtstart="7770" stmtend="8050" sqlhandle="0x03000800a7d0d949217e4801249e00000100000000000000"/>
       </executionStack>
       <inputbuf>
    Proc [Database Id = 8 Object Id = 1239011495]   </inputbuf>
      </process>
     </blocking-process>
    </blocked-process-report>

    Both object id's point to sp's.  However the sp's reference multiple tables.  Need to figure out which table the RID lock is for.

    • Edited by scott_m Friday, September 20, 2013 3:22 PM
    Friday, September 20, 2013 3:20 PM
  • Hi Scott,

    Try this:

    DBCC TRACEON(3604)
    
    DBCC PAGE (8, 1, 4183, 3)
    DBCC PAGE will in header information give you AllocationID which you can query to get allocation unit. From there you can get partition where you have object_id. Object_name (object_id) gives you the name of the table where the lock happened.


    Regards, Dean Savović

    • Marked as answer by scott_m Sunday, September 22, 2013 3:21 PM
    Friday, September 20, 2013 7:53 PM
  • Hi Scott,

    Try this:

    DBCC TRACEON(3604)
    
    DBCC PAGE (8, 1, 4183, 3)
    DBCC PAGE will in header information give you AllocationID which you can query to get allocation unit. From there you can get partition where you have object_id. Object_name (object_id) gives you the name of the table where the lock happened.


    Regards, Dean Savović

    Hi Dean, Here are the dbcc page results. I see multiple AllocUnitId instances.  Which one do I use and which dmv to a query for it?

    PAGE: (1:4183)


    BUFFER:


    BUF @0x00000000DEFD8300

    bpage = 0x00000000DE60C000           bhash = 0x0000000000000000           bpageno = (1:4183)
    bdbid = 8                            breferences = 0                      bUse1 = 22596
    bstat = 0xec0010b                    blog = 0x79797979                    bnext = 0x0000000000000000

    PAGE HEADER:


    Page @0x00000000DE60C000

    m_pageId = (1:4183)                  m_headerVersion = 1                  m_type = 1
    m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8
    m_objId (AllocUnitId.idObj) = 1764917359                                  m_indexId (AllocUnitId.idInd) = 0
    Metadata: AllocUnitId = 115665624039424                                   Metadata: PartitionId = 115665624039424
    Metadata: IndexId = 0                Metadata: ObjectId = 1764917359      m_prevPage = (0:0)
    m_nextPage = (0:0)                   pminlen = 42                         m_slotCnt = 30
    m_freeCnt = 151                      m_freeData = 7981                    m_reservedCnt = 0
    m_lsn = (794944:4293:2)              m_xactReserved = 0                   m_xdesId = (0:0)
    m_ghostRecCnt = 0                    m_tornBits = -572558714 

    thanks

    scott

    Friday, September 20, 2013 10:18 PM
  • Maybe I am missing it completely, but isn't the Object Id supposed to be the actual ID of the table?

    In other words:

    select *
    from sysobjects
    where id in (1165963230, 1239011459)
    


    Gert-Jan

    Saturday, September 21, 2013 9:54 AM
  • select * from  sys.partitions p
    WHERE PartitionId = 115665624039424

    SELECT OBJECT_NAME(p.OBJECT_ID) AS 'tableName'
        , i.name AS 'indexName'
        , p.partition_number
        , au.type_desc
        , CONVERT (VARCHAR(6),
          CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
             SUBSTRING (au.first_page, 5, 1))) +
       ':' + CONVERT (VARCHAR(20),
          CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
             SUBSTRING (au.first_page, 3, 1) +
             SUBSTRING (au.first_page, 2, 1) +
             SUBSTRING (au.first_page, 1, 1))) AS 'firstPage'
        , CONVERT (VARCHAR(6),
          CONVERT (INT, SUBSTRING (au.root_page, 6, 1) +
             SUBSTRING (au.root_page, 5, 1))) +
       ':' + CONVERT (VARCHAR(20),
          CONVERT (INT, SUBSTRING (au.root_page, 4, 1) +
             SUBSTRING (au.root_page, 3, 1) +
             SUBSTRING (au.root_page, 2, 1) +
             SUBSTRING (au.root_page, 1, 1))) AS 'rootPage'
        , CONVERT (VARCHAR(6),
          CONVERT (INT, SUBSTRING (au.first_iam_page, 6, 1) +
             SUBSTRING (au.first_iam_page, 5, 1))) +
       ':' + CONVERT (VARCHAR(20),
          CONVERT (INT, SUBSTRING (au.first_iam_page, 4, 1) +
             SUBSTRING (au.first_iam_page, 3, 1) +
             SUBSTRING (au.first_iam_page, 2, 1) +
             SUBSTRING (au.first_iam_page, 1, 1))) AS 'firstIAM_page'
    FROM sys.indexes AS i
    Join sys.partitions AS p
        ON i.OBJECT_ID = p.OBJECT_ID
        And i.index_id = p.index_id
    Join sys.system_internals_allocation_units AS au
        ON p.hobt_id = au.container_id
    WHERE OBJECT_NAME(p.OBJECT_ID) = 'yourtable'
    ORDER BY tableName;

    Source http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by scott_m Sunday, September 22, 2013 3:21 PM
    Sunday, September 22, 2013 3:34 AM