0

When running sp_who2 and BlkBy is populated, what kind of resources does it check? Is it just for locks being held? The documentation doesn't exist, and the closest equivalent for sp_who just mentions that it might be holding resources that another process needs, with an exclusive lock as an example:

A blocking process, which may have an exclusive lock, is one that is holding resources that another process needs.

Is it possible for it to identify "resources" as CPU, for example, rather than just a lock?

3
  • 4
    Instead of using sp_who or sp_who2, try using sp_whoIsActive by Adam Machanic. It will filter out a lot of noise and will show a lot more info about the waits, there is a ton of information you can get from it. Have a read of this sp_WhoIsActive
    – M.Ali
    Commented Jan 31 at 15:15
  • Is your question really about this deprecated procedure or are you asking in general about the kind of waits that can be associated with a specific blocking_session_id even in the non deprecated DMVs? Commented Jan 31 at 22:06
  • I was hoping for there to be something native so that it doesn't need to be deployed everywhere and configured for permissions and go through a whole approval process. The intent of the question is really just whether or not CPU can be a "blocking resource" identified by sp_who2. I think the answer seems to be no, however.
    – Lunyx
    Commented Feb 1 at 17:53

1 Answer 1

4

While you are far better off using sp_WhoIsActive, you can see the hot mess that is sp_who or sp_who2 by using sp_helptext.

EXEC sp_helptext 'sp_who2';

I'm not sure if I'm allowed to reproduce the full code here, but it starts off on a fairly sour note:

create procedure sys.sp_who2  --- 1995/11/03 10:16 

Part of the reason why it's undocumented (aside from embarrassment), is that it uses an internal view that can't be accessed normally: sys.sysprocesses_ex. One could surmise that "_ex" means extended.

You can access sys.sysprocesses easily, and you may be able to glean enough detail from that to get an idea of what sys.sysprocesses_ex contains:

SELECT s.* FROM sys.sysprocesses AS s;

And of course, if you use sp_helptext on sys.sysprocesses, you get a very unhelpful select statement that ends with:

FROM OpenRowSet(TABLE SYSPROCESSES)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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