Moved from: bobsql.com
I ran into a scenario showing my session as suspended, awaiting command and blocked! My first thought was that this had to a bug.
As many of us have documented, for years, a sleeping, awaiting command, session is a session waiting on the client to submit a TSQL command to the SQL Server. The client must send a command to SQL Server or the SQL Server status is ‘awaiting command’ for the session.
The difference in my scenario was the ‘SUSPENDED’ and ‘BLOCKED’ indicators. A blocked session must have a worker. The worker is not released until the command completes. So the session has a worker and is waiting on a resource and that should mean that the command type can’t be ‘awaiting command.’
My situation is the bookends of command execution (starting or stopping specifically.) In this specific case all results of the query were sent to the client, including the SRV_DONE, TDS status indicating no more results (end-of-results) have been reached. At this point the command is no longer SELECT, UPDATE, DELETE, … but is set to AWAITING COMMAND. The key is that during query cleanup blocking occurred, leaving the state of the session, suspended (waiting for a resource), blocked and AWAITING COMMAND.
Looking at the session call stacks helps clarify the issue. The workers are truncating, dropping and creating temp tables. This aligns well with the fact that I am running a TVP and temp table stress scenario and which has managed to create a hotspot on the first PFS in TEMPDB. The hotspot causes a convoy/train to occur on the page latch (UP) for the PFS page.
Note: Adding multiple TEMPDB files resolves the train/convoy.
06 sqlmin!LatchBase::Suspend
07 sqlmin!LatchBase::AcquireInternal
08 sqlmin!LatchBase::Acquire
09 sqlmin!BUF::AcquireLatch
0a sqlmin!BPool::Get
0b sqlmin!PageRef::Fix
0c sqlmin!PFSPageRef::Fix
0d sqlmin!ChangeFreeSpaceValue
0e sqlmin!SetFreeSpaceState
0f sqlmin!PageRef::UpdateFreeSpaceStateInPFS
10 sqlmin!PageRef::DeleteRows
11 sqlmin!PageRef::DeleteRow
12 sqlmin!DeAllocateIndexNew
13 sqlmin!SETruncateRowset
When a query is starting (before SQL Server can reach compile/execution state and set the command type) or completing (after sending the SRV_DONE token, the command type is cleared, I.E. AWAINTING COMMAND) a query can achieve the suspended, AWAITING COMMAND and blocked state. From the call stack you can see we are truncating (cleaning up) the temp table. SQL Server has sent the results to the client application and is performing final cleanup from the pervious query execution. Technically the session is waiting for its next command to arrive and yet achieves the blocked state. The cleanup activity created a convey/train of latch activity on the PFS page in TEMPDB, leading to blocking.
Mystery Solved: Some edge scenarios can lead a suspended, AWAITING COMMAND, blocked session.
Posted at https://sl.advdat.com/352Fx9ohttps://sl.advdat.com/352Fx9o