none
Tracking down blocking....

    Question

  • Every so often we experience extensive blocking  ( one spid blocks 10 to 30 others). We have looked at sql profiler traces near the time of the blocking but can't tell from them what caused the issue.   What suggestions can you offer to track down the real culprits?

    Sql 2008

    TIA,

    Barkgindog

    mardi 1 mai 2012 01:55

Réponses

  • You will wan to look in the sys.dm_tran_locks DMV.  Check out this post by Glean Berry: http://www.sqlservercentral.com/blogs/glennberry/2010/04/27/a-dmv-a-day-_1320_-day-27/

    SELECT tl.resource_type, tl.resource_database_id,
           tl.resource_associated_entity_id, tl.request_mode,
           tl.request_session_id, wt.blocking_session_id, 
           wt.wait_type, wt.wait_duration_ms
    FROM sys.dm_tran_locks as tl
    INNER JOIN sys.dm_os_waiting_tasks as wt
    ON tl.lock_owner_address = wt.resource_address
    ORDER BY wait_duration_ms DESC;

    mardi 1 mai 2012 02:01
  • -- Detailed blocking information with query information
    SELECT
    owt.session_id AS waiting_session_id,
        owt.blocking_session_id,
    DB_NAME(tls.resource_database_id) AS database_name,
        (SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
    (CASE WHEN ers.statement_end_offset = -1
    THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
    ELSE ers.statement_end_offset
    END
    - ers.statement_start_offset
    ) / 2)
    FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
    CASE WHEN owt.blocking_session_id > 0 
    THEN (
    SELECT
    est.[text] FROM sys.sysprocesses AS sp
    CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
    WHERE sp.spid = owt.blocking_session_id)
    ELSE
    NULL
    END AS blocking_query_text,
        (CASE tls.resource_type
    WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
    WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
    ELSE (SELECT  OBJECT_NAME(pat.[object_id], tls.resource_database_id)
    FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)

    END
    ) AS object_name,
    owt.wait_duration_ms,
    owt.waiting_task_address,
    owt.wait_type,
    tls.resource_associated_entity_id,
    tls.resource_description AS local_resource_description,
    tls.resource_type,
    tls.request_mode,
    tls.request_type,
    tls.request_session_id,
    owt.resource_description AS blocking_resource_description,
    qp.query_plan AS waiting_query_plan
    FROM sys.dm_tran_locks AS tls
    INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
    INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
    OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
    GO

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

    mardi 1 mai 2012 14:55
    Auteur de réponse

Toutes les réponses

  • You will wan to look in the sys.dm_tran_locks DMV.  Check out this post by Glean Berry: http://www.sqlservercentral.com/blogs/glennberry/2010/04/27/a-dmv-a-day-_1320_-day-27/

    SELECT tl.resource_type, tl.resource_database_id,
           tl.resource_associated_entity_id, tl.request_mode,
           tl.request_session_id, wt.blocking_session_id, 
           wt.wait_type, wt.wait_duration_ms
    FROM sys.dm_tran_locks as tl
    INNER JOIN sys.dm_os_waiting_tasks as wt
    ON tl.lock_owner_address = wt.resource_address
    ORDER BY wait_duration_ms DESC;

    mardi 1 mai 2012 02:01
  • -- Detailed blocking information with query information
    SELECT
    owt.session_id AS waiting_session_id,
        owt.blocking_session_id,
    DB_NAME(tls.resource_database_id) AS database_name,
        (SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
    (CASE WHEN ers.statement_end_offset = -1
    THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
    ELSE ers.statement_end_offset
    END
    - ers.statement_start_offset
    ) / 2)
    FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
    CASE WHEN owt.blocking_session_id > 0 
    THEN (
    SELECT
    est.[text] FROM sys.sysprocesses AS sp
    CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
    WHERE sp.spid = owt.blocking_session_id)
    ELSE
    NULL
    END AS blocking_query_text,
        (CASE tls.resource_type
    WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
    WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
    ELSE (SELECT  OBJECT_NAME(pat.[object_id], tls.resource_database_id)
    FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)

    END
    ) AS object_name,
    owt.wait_duration_ms,
    owt.waiting_task_address,
    owt.wait_type,
    tls.resource_associated_entity_id,
    tls.resource_description AS local_resource_description,
    tls.resource_type,
    tls.request_mode,
    tls.request_type,
    tls.request_session_id,
    owt.resource_description AS blocking_resource_description,
    qp.query_plan AS waiting_query_plan
    FROM sys.dm_tran_locks AS tls
    INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
    INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
    OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
    GO

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

    mardi 1 mai 2012 14:55
    Auteur de réponse