Determine the server that sent a query

Question

For the distributed queries, is it possible to determine the server that sent a query?

For example, I have stats:

SELECT DB_NAME(st.dbid),
       text,
       request_time,
       query_plan,
       (requested_memory_kb / 1024),
       (granted_memory_kb / 1024),
       query_cost
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS ph
ORDER BY requested_memory_kb DESC

How can I get server that sent each query? What tables should I join or functions to apply?

Answer 1

The DMV sys.dm_exec_sessions has column host_name which is the computer at the client end of the connection. You can join that to your posted query through the session_id.

Answer 2

I have a query for this. It will provide you login,hostname(server), start time,session_id with query text.

select
r.session_id,
r.start_time,
s.login_name,
c.client_net_address,
s.host_name,
s.program_name,
st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st where r.session_id  > 50