May 24, 2017 at 10:04 am
We are using the following codeSelect
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.
May 24, 2017 at 12:27 pm
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
May 24, 2017 at 3:45 pm
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
May 25, 2017 at 9:55 am
Sorry - I should have done that at the start.
Here's an example. A process with these details
is being blocked by a second process with these details
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)
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!
May 25, 2017 at 10:08 am
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.
May 25, 2017 at 10:11 am
Sure, but there are no foreign keys to or from either of the TTemp tables.
May 25, 2017 at 11:46 am
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
May 25, 2017 at 12:26 pm
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.
May 25, 2017 at 2:14 pm
julian.fletcher - Thursday, May 25, 2017 12:26 PMPresumably 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
May 26, 2017 at 2:35 am
Thanks very much for that. Obvious (in retrospect!)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply