Locking and SQL Handle

• suri.yalamanchili

  SSCommitted

  Points: 1645

  More actions

  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

• dant12

  SSCrazy

  Points: 2712

  More actions

  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