We're seeing some strange blocking behaviour in a production (SQL Server 2008 R2) system that we're investigating, and it seems to have one consistent and novel element: in the
sysprocesses table, the relevant blocking process always has
ASSIGN as the "cmd" column value.
The process that is actually running is a stored procedure, but
dm_exec_sql_text doesn't give us any more detail that the stored procedure name - it's quite a complex proc.
We know that this stored procedure does a non-negligible amount of Xml (XQuery) processing, so that seems the most likely source of the
ASSIGN command, but we're having a hard time understanding exactly what the command is, why it would take a long time, and what it might be "hiding":
For example, could it be that this is generally what happens when using a
SET statement, where the value being set is a subquery?
For example, is this a way to reproduce the issue?
DECLARE @Something Int SET @Something = ( SELECT Something FROM Somewhere WHERE DoSomethingReallySlowAndExpensive(Something) = 1 )
UPDATE: I can confirm that this is not a way to reproduce the issue - a long-running
ASSIGN command in sysprocesses must mean something else, something more specific.
The test code above simply shows up as
UPDATE 2: To Aaron Bertrand's point/question, the below is ALSO not a way to reproduce the issue. This code will alternate between being listed as cmd
CONDITIONAL and cmd
SELECT. So... what does
CREATE FUNCTION [dbo].[fn_CpuLoad] (@LoopCount BigInt) RETURNS Bit AS BEGIN WHILE @LoopCount > 0 BEGIN SET @LoopCount -= 1 END RETURN 0 END GO DECLARE @Something Int SET @Something = dbo.fn_CpuLoad(100000000) GO DROP FUNCTION [dbo].[fn_CpuLoad] GO
We eventually realized we were concentrating on the wrong aspect of the data we had available - the "cmd" value was usually
ASSIGN, but also sometimes
SELECT; what was completely consistent was the "waitresource" value, which was always the same value, "TAB: 9:547043060:0 [COMPILE]". When we noticed this, we rapidly found information on compile locks that helped us track down the causes:
- Microsoft article on compile locks, identifying the name-qualification issue that was mainly biting us
- Another misc article, about compile locks and encryption, also containing useful info
So we've now addressed the issues we were hunting, but we still have no idea what the
ASSIGN value for cmd is, so I'll leave this question open in case anyone with the answer stumbles across it.