Get Long Running Queries For Users Or Applications Queries

Question

I recently read this perfect article:

How to Find Longest Running Query With Execution Plan – Interview Question of the Week #098

It returns system calling queries (for internal SQL Server works).

Is it possible to filter these queries and return just users or application calling queries?

Thanks.

Based on what criteria you want to filter exactly? What is "application calling query"? — Alex, Jun 22 '20 at 03:09

score 1 · Answer 1 · answered Jun 18 '20 at 22:06

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

Thanks but I can't run `Job` on the server. I can only query from `DMVs` — Arian, Jun 19 '20 at 07:50
@Arian the historical information you need is not available from any DMVs currently. You'd need to build some kind of process to capture it. If SQL Server Agent Jobs is not an approach you can use in your environment you could also do something with Extended Events, or even something as simple as triggers but you need some mechanism to capture historical data. — vvvv4d, Jun 19 '20 at 15:13
The original script return information I need but it include system queries — Arian, Jun 19 '20 at 19:28

score 0 · Answer 2 · edited Jun 18 '20 at 12:19

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

Thanks but this query doesn't return any thing for me. Orginal query does. I think this is because your script return live queries and doesn't consider queries history — Arian, Jun 18 '20 at 12:19
@Arian, yes. as I am putting join with sys.dm_exec_requests, sys.dm_exec_sessions, it is live data. — Venkataraman R, Jun 18 '20 at 12:48
Sorry but I want get long running queries since the system started — Arian, Jun 18 '20 at 16:54

score 0 · Answer 3 · answered Jun 25 '20 at 03:54

0

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.

answered Jun 25 '20 at 03:54

detzu

701
6
12

Thanks. I mean user queries that execute from applications – Arian Jun 25 '20 at 05:36

Get Long Running Queries For Users Or Applications Queries

3 Answers3