Finding the cause of blocking between processes

  • julian.fletcher

    SSCrazy

    Points: 2614

    We are using the following code
    Select
    ER.Session_ID SPID,
    ER.Blocking_Session_ID BlockedBySPID,
    ER.Start_Time 'Start',
    ER.Status,
    ER.Command,
    Object_Name (TXT.ObjectID, ER.Database_ID) 'Object',
    dbo.SQLStatement (TXT.Text, ER.statement_start_offset, ER.statement_end_offset) 'Statement',
    ER.Wait_Type 'Wait Type',
    ER.Wait_Time / 1000 'Wait Time in s',
    ER.Last_Wait_Type 'Last Wait Type',
    ER.Wait_Resource 'Wait Resource',
    ER.Open_Transaction_Count 'Open Transactions',
    ER.CPU_Time / 1000 'CPU Time in s',
    ER.Total_Elapsed_Time / 1000 'Elapsed Time in s',
    ER.Reads,
    ER.Writes,
    ER.Logical_Reads 'Logical Reads',
    DB_Name (ER.Database_ID) 'DB'
    From sys.dm_exec_requests ER
    Cross Apply sys.dm_exec_sql_text (ER.SQL_Handle) TXT
    -- Don't bother showing this process.
    Where (ER.Session_ID <> @@SPID)

    to look at processes running in a database and, in particular, see if any are blocking any other ones.

    Often, there appears to be no obvious link between the blocking statement and the blocked one. Deciphering the value in the "Wait Resource" doesn't usually help either.

    I can see why a PAGE resource might link otherwise unrelated database objects, and possibly RID ones too, but other than that I'm a bit confused!

    The database is full of foreign keys, unique indexes, check constraints, triggers, etc. Could they be the cause?

    Any general guidance would be much appreciated.

    Thanks.

  • Chris Harshman

    SSC-Forever

    Points: 41755

    you might be able to troubleshoot this with some other sys views:

    SELECT tl.request_session_id, tl.resource_type, tl.request_mode, h1.TEXT AS request_text,
        wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) blocked_object_name, h2.TEXT AS blocking_text
      FROM sys.dm_tran_locks AS tl
        INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
        INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
        INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
        INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
        OUTER APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
        OUTER APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Post some examples of what you see and can't interpret, so that we can walk you through the process?

    A page contains only rows from a single table, so a PAGE lock can't 'link' unrelated tables.

    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
  • julian.fletcher

    SSCrazy

    Points: 2614

    Sorry - I should have done that at the start.

    Here's an example. A process with these details

    • SP = Controcc_Payments_OvernightJob_RefreshCharges
    • Statement = Delete CCC From dbo.TTemp_ChargingCalculationClients CCC With(Index(IX_TTemp_ChargingCalculationClients_SPIDClientID)) Where CCC.SPID = @@SPID
    • Wait resource = TTemp_ChargingCalculationClients
    • Wait type = LCK_M_IX

    is being blocked by a second process with these details

    • SP = Controcc_Charging_Calculate_Accounts_CreateVirtualActuals
    • Statement =

    Insert Into dbo.TTemp_ActualCreateCPLI
    (
     CarePackageLineItemID,
     ActualStartDateMin,
     ActualStartDateMax
    )
    Select CPLI.CarePackageLineItemID,
     dbo.MaxDateOf(dbo.MaxDateOf(dbo.MaxDateOf(CPLI_I.LastGenerateActualDate, CPLI_I.LastVirtualActualDate, 1) + 7, dbo.ActualStartDateBefore(CPLI.StartDate, @ActualsDay), 1), @ActualsCutoffDate, 1),
     dbo.ActualStartDateBefore(dbo.MinDateOf(C.EndDate, CPLI.EndDate, 1), @ActualsDay)
      From #Clients C
      Join dbo.T_CarePackageLineItem CPLI On CPLI.ClientID = C.ClientID And CPLI.CarePackageLineItemAuthorisationStatusID = 2 And CPLI.Released = 1 And CPLI.Deleted = 0 And CPLI.IsUnplanned = 0 And CPLI.StartDate <= C.EndDate And IsNull(CPLI.EndDate, '99990101') >= C.StartDate
      Join dbo.T_CarePackageLineItem_Internal CPLI_I On CPLI_I.CarePackageLineItemID = CPLI.CarePackageLineItemID
      Join dbo.T_ServiceServiceLevel SSL On SSL.ServiceServiceLevelID = CPLI.ServiceServiceLevelID
      Join dbo.TRef_ServiceLevel SL On SL.ServiceLevelID = SSL.ServiceLevelID
      Join dbo.TRefSys_Frequency F On F.FrequencyID = SL.FrequencyID And F.IsIntermittent = 0
      Join dbo.TRef_ServiceGroup SG On SG.ServiceGroupID = SL.ServiceGroupID And SG.GoodsCollection = 0
     Where dbo.MaxDateOf(dbo.MaxDateOf(dbo.MaxDateOf(CPLI_I.LastGenerateActualDate, CPLI_I.LastVirtualActualDate, 1) + 7, dbo.ActualStartDateBefore(CPLI.StartDate, @ActualsDay), 1), @ActualsCutoffDate, 1) <=
      dbo.ActualStartDateBefore(dbo.MinDateOf(C.EndDate, CPLI.EndDate, 1), @ActualsDay)

    • Wait resource = T_ServiceServiceLevel - IX_T_ServiceServiceLevel_ServiceLevelID
    • Wait type = PAGEIOLATCH_SH

    I know this contains a few coding horrors (which can be dealt with relatively easily) - my question is how could deleting from TTemp_ChargingCalculationClients be blocked by a process filling an entirely different table (TTemp_ActualCreateCPLI) from records in other tables, none of which are TTemp_ChargingCalculationClients? (There are no foreign keys to or from either of the TTemp tables.)

    If anyone can explain that, I would be mightily impressed!

  • Chris Harshman

    SSC-Forever

    Points: 41755

    as part of a DELETE operation, the database must check that there are no records in tables that are foreign keyed to it that have a value that would be removed by the DELETE.  So while you aren't explicitly referencing TTemp_ChargingCalculationClients in your INSERT or SELECT, if any of these tables:
    TTemp_ActualCreateCPLI, #Clients, T_CarePackageLineItemm, T_CarePackageLineItem_Internal, T_ServiceServiceLevel, TRef_ServiceLevel, TRefSys_Frequency, TRef_ServiceGroup
    have a foreign key to TTemp_ChargingCalculationClients then that table must be checked.  It gets even more complicated if the foreign key has ON DELETE CASCADE turned on.

  • julian.fletcher

    SSCrazy

    Points: 2614

    Sure, but there are no foreign keys to or from either of the TTemp tables.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    What happens in the explicit transaction before that Insert Into dbo.TTemp_ActualCreateCPLI?
    Because the only way that is holding a lock on TTemp_ChargingCalculationClients, is if there's an explicit transaction and something in that explicit transaction operated (probably insert/update/delete) on TTemp_ChargingCalculationClients.

    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
  • julian.fletcher

    SSCrazy

    Points: 2614

    Thanks for your reply.

    What happens in the explicit transaction before that Insert Into dbo.TTemp_ActualCreateCPLI?

    Probably all manner of things! So what you're saying is that all we can infer from the results of the query is that one process is being blocked by some statement which has already run (or may still be running) in the transaction of another process. That statement may have already completed - it's not necessarily the one currently running.

    That would make perfect sense from what we're seeing.

    Presumably there's no easy way of immediately determining what that other statement is - we would have to look at the blocking SP and what might have been called before it (in the same transaction) and generally have a bit of a prod around.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    julian.fletcher - Thursday, May 25, 2017 12:26 PM

    Presumably there's no easy way of immediately determining what that other statement is - we would have to look at the blocking SP and what might have been called before it (in the same transaction) and generally have a bit of a prod around.

    Correct.
    When you have explicit transactions, locks taken for data modifications are held until the end of the transaction

    Long-running transactions are not recommended, because of the effect they have on concurrency. In a transaction should be the bare minimum that has to be in it. I'm not fond of transactions that cross procedure boundaries, because it's really tricky to debug them

    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
  • julian.fletcher

    SSCrazy

    Points: 2614

    Thanks very much for that. Obvious (in retrospect!)

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

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