5

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?

5

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.

0

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

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.