1

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.

  • 3
    Because it's waiting on something, not doing actual work. Stop using sp_who2 (it's undocumented, unsupported, and could disappear at any time) and grab sp_whoisactive from Adam Machanic or just look at sys.dm_exec_requests directly. It could be blocked or it could have a wait type that gives some clue about what it's waiting on... – Aaron Bertrand Sep 22 '15 at 15:59
2

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.