Get Long Running Queries For Users Or Applications Queries

Viewed 1030
3 Answers

You are 1/2 way there: You need to capture historical information about what was running. What you are querying in your question, sys.dm_exec_query_stats, is cumulative and aggregated statistics that aren't by a session/connection that is guaranteed to still be connected to the server for you to identify where it came from. To overcome this you need to capture historical information. There is many ways to do this. I recommend using a SQL Server Agent job with tSQL steps. You need to run something like the below query on a schedule and log the data to a history table. Then you can cross apply or join your original query on sql_handle to the history table you keep up to date from the SQL Server Agent job

select
    r.session_id,
    s.login_name,
    c.client_net_address,
    s.host_name,
    s.program_name,
    r.sql_handle,
    r.start_time
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

So if you had a SQL Server Agent job running on some schedule putting the above query results to DBA.dbo.QueryHistory you could change the original query from sqlauthority to something like this to get the Username tied to the query:

SELECT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
,query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
outer apply (
select top 1 login_name
from DBA.dbo.QueryHistory  QH
where QH.sql_handle = s.sql_handle
)
ORDER BY MaxElapsedTime DESC

I have modified the query to get users/application calling queries. You can get client specific information from sys.dm_exec_sessions

SELECT TOP 10
ss.program_name, --Name of the client program
ss.host_name, -- Workstation of client session
ss.client_interface_name, -- Driver used by client to communicate
ss.login_name, -- Client login name
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
,query_plan

FROM sys.dm_exec_query_stats s
INNER JOIN sys.dm_exec_requests as r
on r.plan_handle = s.plan_handle
INNER JOIN sys.dm_exec_sessions AS ss
ON ss.session_id = r.session_id
CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
WHERE ss.program_name is not null -- not internal session
ORDER BY MaxElapsedTime DESC

This is not possible , there is no DMV saving this information now.

BTW what is "application calling queries" ? Do you refer to jobs ?

The only option you have, as others wrote already, is to save this information with triggers , jobs or external applications.

Related