sp_WhoIsActive/spwho2 - get process id

Question

I am a developer helping the DBAs with a sporadic CPU 100% issue.

Please see the SQL query below:

Select spid,hostname,hostprocess,program_name,nt_username, blocked, waittime, waittype, loginame,cmd,spid,waittype,waittime,lastwaittype,cpu,physical_io,memusage,
login_time,last_batch,open_tran,status,net_address, t.text 
from sys.sysprocesses sp 
CROSS APPLY( select text from sys.dm_exec_sql_text(sp.sql_handle))t 
where hostname = 'MyOtherDatabaseServer'

It returns one record. I then execute the following on MyOtherDatabaseServer:

EXECUTE sp_WhoIsActive

Is there a way I can tie the result on server 1 to the result on server 2? I was hoping that sp_WhoIsActive would return a process id for each record (that would tie them because there is a HostProcess in the first query).

There is a linked server setup between server 1 and MyOtherDatabaseServer. I am trying to find, which database on 'server 1' is using the linked server and running a query on MyOtherDatabaseServer.

Answer 1

Whilst a Linked Server query is running, it will generate wait types of OLEDB and the wait resource will be listed as the linked server name and SPID in brackets.

This can be viewed using the sys.dm_exec_requests dynamic management view in the wait_type and wait_resource columns, or in the sys.sysprocesses table in the waitresource column.

The host process isn't really going to help you in this circumstance, as it refers to the PID displayed in Resource Monitor, and will just be the sqlservr instance itself in the case of a linked server query.