Why does sp_server_diagnostics contradict sp_who?

## Context My AG's diagnostics use sp_server_diagnostics, so I decided to learn more about it. [The documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-server-diagnostics-transact-sql?view=sql-server-ver17) is light on detail about where it gets its information from. Today, I am particularly interested in its "query_processing" section. I noticed that this contains several references to ecids, which I only know of from sp_who. I therefore decided to compare the two. ## Problem When I have a blocked and blocking query running, sp_who reports sensible results. | spid | ecid | status | loginame | hostname | blk | dbname | cmd | request_id | |------|------|------------|------------|---------|-----|--------|--------|------------| | 54 | 0 | suspended | sa | MyBox | 64 | master | SELECT | 0 | | 64 | 0 | suspended | SuperAdmin | MyBox | 0 | master | SELECT | 0 | | 64 | 1 | runnable | | MyBox | 0 | master | SELECT | 0 | | 64 | 3 | runnable | | MyBox | 0 | master | SELECT | 0 | | 64 | 2 | runnable | | MyBox | 0 | master | SELECT | 0 | | 64 | 4 | runnable | | MyBox | 0 | master | SELECT | 0 | Yet the query_process details report something much less sensible. To save your eyes, I've asked an AI to convert the XML to a table and chopped off many columns. | spid | ecid | status | loginname | hostname | type | processid | schedulerid | trancount | query | |------|------|-----------|------------|----------|----------|----------------|--------------|-----------|------------------| | 64 | 0 | suspended | SuperAdmin | MyBox | Blocked | processf15fbd468 | 2 | 1 | MyFirstBadQuery | | 64 | 1 | suspended | | MyBox | Blocking | | | 0 | MyFirstBadQuery | | 64 | 1 | suspended | | MyBox | Blocked | processf15be6ca8 | 3 | 0 | MyFirstBadQuery | | 64 | 2 | running | | MyBox | Blocking | | | 0 | MyFirstBadQuery | | 64 | 2 | running | | MyBox | Blocked | processf17dd88c8 | 4 | 0 | MyFirstBadQuery | | 64 | 3 | running | | MyBox | Blocked | processf1ebc4ca8 | 1 | 0 | MyFirstBadQuery | | 64 | 4 | running | | MyBox | Blocked | processf1ebc48c8 | 2 | 0 | MyFirstBadQuery | I know that [thread-level blocking information is a bad idea](http://whoisactive.com/docs/14_blockers/) . However, I do not expect the contradictions that these two tables appear to show. ### My Questions Clearly, sp_server_diagnostics isn't copying from sp_who. So: 1. Where does sp_server_diagnostics pull its query_processing information from? 2. Can I call that myself? 3. Why does sp_server_diagnostics think that individual ecids (specifically, 1 and 2) are both blocking and blocked? sp_who does not make this mistake. ### Ugly XML The full XML is below.

MyFirstBadQuery
  
 
 
  
 


 
  
   
MyFirstBadQuery
  
 
 
  
   
MyFirstBadQuery
  
 


 
  
   
MyFirstBadQuery
  
 
 
  
   
MyFirstBadQuery
  
 


 
  
   
MyFirstBadQuery
  
 
 
  
 


 
  
   
MyFirstBadQuery