Sql-server – Blocking SPID has Zero Disk IO and Zero CPU Time

blockingsql server

Running sp_who2 and following the BlkBy trail up to the root cause of blocking returns a SPID which has CPUTime and DiskIO values of 0; yet it's blocking 4 other SPIDs.

I'm confused by how this is possible; the CPUTime in particular seems odd as in order to obtain a lock you'd have had to have spent some time requesting resources / requesting the lock itself.
CPUTime is in milliseconds, so whilst it's possible that the requesting and locking of resources occurs fast enough to have a rounded down value here, that is a little surprising.

Also, these SPIDS are sometimes a few minutes old; yet appear to have done nothing beyond cause blocking.

Question

How is it possible for a SPID to cause blocking whilst having zero CPU Time?

I'm asking as I suspect something's lacking in my understanding of the CPU Time stat. If anyone can advise on sensible steps to aid in investigating such issues though that would also be useful.

Best Answer

This can sometimes happen when applications are using implicit transactions

It doesn't help that sp_who2 is really confusing -- Aaron is right that you're better off with sp_WhoIsActive or sp_BlitzWho to find running queries.

One dead giveaway of implicit transactions is seeing the query text like begin tran or IF @@TRANCOUNT > 0.

This can also happen when queries are using explicit transactions, of course. It's just far easier to catch those in code.

As to why CPU time is very low -- well it doesn't take much CPU to do an index seek to one row and lock it, then cause problems for any other queries that also want that row.

Related Solutions

Alter table add column query blocking a table or taking so much time

The table will need to be physically rewritten on disc to include the extra column. For this to happen the table has to be exclusively locked while this is happening. If the applications running on this database are not shutdown then you may be waiting sometime before this exclusive lock on the table can be given.

Given you mentioned that this table has several million rows the alter table statement is going to take quite some time (this also depends how much data is stored in each record. You will in all likelyhood need to stop all access to this table during this time (well it will be locked anyway).

Sorry if the answer is a little vague you did not really give many details about the size of the table and how much data is currently stored there.

Sql-server – SQL Blocking, SPID of -2

I see "-2" for two of the rows. What does this mean?

It means you have an open DTC transaction managed by an external ITransaction interface but no Sessions currently using it.

you have to use KILL UOW where UOW = UnitOfWork associated with that transaction instead of the session id.

---- in text mode run below query

select 'kill ' , req_transactionUOW
from master..syslockinfo
where req_spid = -2

Best Answer

Related Solutions

Alter table add column query blocking a table or taking so much time

Sql-server – SQL Blocking, SPID of -2

Related Question