June 2, 2010 at 2:27 pm
Two days of searching and reading has not brought me closer to an answer.
I have several queries to display the worst performing queries in the system, but I would like to add a column identifying the source.
Is it possible to get the logon or application name for the source of the queries?
I know I can filter by that in Profiler, but I can't figure out a way to do it with DMVs.
14090 SW TENNESSEE LN
June 3, 2010 at 8:55 pm
Hi, you can start with this query and then add sys.dm_exec_query_stats:
select s.session_id,s.login_time, s.login_name, s.program_name, r.sql_handle,
( SELECT TOP 1 SUBSTRING(s2.text, statement_start_offset / 2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS sql_statement
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS s2
June 4, 2010 at 10:58 am
Thanks, but that looks like it just shows the current active sessions.
I need to correlate to the sessions when the queries were run.
I'm beginning to think it's only possible with Profiler or Diagnostic Manager or something that constantly monitors and saves all of that on the fly.
14090 SW TENNESSEE LN
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply