Dynamic query

• Satya_0000

  Mr or Mrs. 500

  Points: 544

  More actions

  March 2, 2012 at 8:02 am

  #391708

  Hi , I want to trace the query which are running in parallely (thread) .

  Thanks in advance.

• manukumar

  SSC-Addicted

  Points: 489

  More actions

  March 2, 2012 at 8:04 am

  #1454193

  This query will help to you.

  select

  r.session_id,

  r.request_id,

  max(isnull(exec_context_id, 0)) as number_of_workers,

  r.sql_handle,

  r.statement_start_offset,

  r.statement_end_offset,

  r.plan_handle

  from

  sys.dm_exec_requests r

  join sys.dm_os_tasks t on r.session_id = t.session_id

  join sys.dm_exec_sessions s on r.session_id = s.session_id

  where

  s.is_user_process = 0x1

  group by

  r.session_id, r.request_id,

  r.sql_handle, r.plan_handle,

  r.statement_start_offset, r.statement_end_offset

  having max(isnull(exec_context_id, 0)) > 0

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 2, 2012 at 8:06 am

  #1454195

  SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

  AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

  CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

  CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

  CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

  CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

  CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

  FROM sys.dm_exec_sql_text(sql_handle)))

  FROM sys.dm_exec_requests r

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]