Post reply

Locking and SQL Handle

  • February 21, 2011 at 8:57 am

    #234040

    Hi ALL,

    I have LCK_M_IX (Intent exclusive lock) with the highest wait time of all the waits. I understand I cannot completely eliminate locking and should optimize my queries/indexes.

    I am using the sys.dm_Tran_locks and trying to join it to something whereby I can obtain the SQL Handle and find the sql statements causing this. I see the application name as the request group from the dmv. I want to find out which exact sql statement is causing this.

    Thanks,

    Suri

  • February 23, 2011 at 7:28 am

    #1290393

    the way I eliminated(almost) locking problems on my former job was with data collector

    setup a MDW and 2 tsql generic collectors

    here are the queries i used

    1st one to get everything to the blocking queries, queries that were blocked or blocking

    SELECT spid,blocked,cpu,physical_io,last_batch,RTRIM(LASTWAITTYPE) LASTWAITTYPE,RTRIM(STATUS) [STATUS],

    RTRIM(PROGRAM_NAME) [PROGRAM_NAME],RTRIM(CMD) CMD,RTRIM(LOGINAME) LOGINAME,CAST(SUBSTRING(text, 1, 4000) AS VARCHAR(4000)) AS [TEXT]

    FROM MASTER.dbo.sysprocesses SP

    CROSS APPLY ::fn_get_sql(sql_handle) AS QUERY

    WHERE DB_NAME(SP.DBID) = 'YOUR DB'

    AND sql_handle != 0x0000000000000000000000000000000000000000

    AND

    (

    SP.SPID IN (SELECT DISTINCT SPID FROM master.sys.sysprocesses WHERE blocked != 0)

    OR

    SP.SPID IN (SELECT DISTINCT BLOCKED FROM master.sys.sysprocesses WHERE blocked != 0)

    )

    2nd one to find the actual resources blocked

    SELECT RESOURCE_TYPE,RESOURCE_SUBTYPE,DB_NAME(RESOURCE_DATABASE_ID) AS [DATABASE],RESOURCE_DESCRIPTION,

    RESOURCE_ASSOCIATED_ENTITY_ID,REQUEST_MODE,REQUEST_SESSION_ID

    FROM MASTER.SYS.DM_TRAN_LOCKS

    WHERE DB_NAME(RESOURCE_DATABASE_ID) = 'YOUR DB'

    AND

    (

    request_session_id IN (SELECT DISTINCT SPID FROM master.sys.sysprocesses WHERE blocked != 0)

    OR

    request_session_id IN (SELECT DISTINCT BLOCKED FROM master.sys.sysprocesses WHERE blocked != 0)

    )

    it is far from perfect but helps a quite bunch, i used data collector because i found it to be the most lightweight tool for this

    --
    Thiago Dantas
    @DantHimself

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

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