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 SELECT.

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 ASSIGN mean??

CREATE FUNCTION [dbo].[fn_CpuLoad] (@LoopCount BigInt)
    WHILE @LoopCount > 0
        SET @LoopCount -= 1

    RETURN 0

DECLARE @Something Int
SET @Something = dbo.fn_CpuLoad(100000000)

DROP FUNCTION [dbo].[fn_CpuLoad]


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:

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.

  • Run DBCC INPUTBUFFER (SPID) on an other connection while a transaction is running. This will Display you the last statement sent from client against SPID
    – AA.SC
    Oct 16, 2014 at 11:42
  • thanks, but we know the statement sent by the client - that is the stored procedure name, reported by dm_exec_sql_text, right? Or are you saying that DBCC INPUTBUFFER would additionally show us the stored procedure arguments? Or show us actual statements executing WITHIN the stored procedure? To the best of my knowledge neither of those is true, but if it were that would be great. My question about ASSIGN is really to help us track down what, within the sproc, is taking so long. We have a hard time getting permission for profiler on production...
    – Tao
    Oct 16, 2014 at 11:46
  • Tao, yes DBCC INPUTBUFFER will show you procedure with arguments in Event Info. First get SP with exact augments then you can figure out exact expensive statement of that SP thorough execution Plan.
    – AA.SC
    Oct 16, 2014 at 11:54
  • Sp_whoisactive to the rescue. Run it and save the output to table for further analysis. Alternatively, you can use event notification with blocked process report as well.
    – Kin Shah
    Oct 16, 2014 at 12:27
  • 1
    You say you're looking at sysprocesses. This is a backward compatibility view that is deprecated. What does sys.dm_exec_requests say? Oct 16, 2014 at 20:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.