Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries:

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
  qs.execution_count,
  qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads,
  qs.max_logical_reads, qs.total_physical_reads, qs.last_physical_reads,
  qs.min_physical_reads, qs.max_physical_reads,
  qs.total_elapsed_time / 1000000 As total_elapsed_time, 
  qs.last_elapsed_time / 1000000 As last_elapsed_time,
  qs.min_elapsed_time / 1000000 As min_elapsed_time, 
  qs.max_elapsed_time / 1000000 As max_elapsed_time,
  qs.last_execution_time, qs.creation_time, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
AND last_execution_time >= DATEADD (MINUTE , -5 , CURRENT_TIMESTAMP )
ORDER BY qs.total_logical_reads DESC
--ORDER BY qs.total_physical_reads DESC

The issue is that I've found a query that appears to be thrown repeatedly from time to time and requires enhancement, but I'm not able to determine where it comes from (could be a user or a program, but in any case should have a login or username). How can I get this user or track this query so I can apply improvements to it?

You can use the below query to pull currently executing requests and the corresponding session/connection information:

select
    r.session_id,
    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 st.text like '%your query string to search for%';

What you are querying in your question, sys.dm_exec_query_stats, is cumulative and aggregated statistics that aren't by session/connection. In other words, that information isn't stored there, nor could it easily be retrieved.

You could put in place a SQL Trace or XE session to grab this data that you're looking for, basically monitoring for the occurrence of your offending query(ies).

There are a couple of ways to get the data you're looking for, but unfortunately with your given query there isn't an easy way to mold that into what you're looking for. Simply different types of data (you provide aggregated data, but you're looking for snapshot and granular data).

StackExchange Database Administrators Q#46009, answer score: 11