This morning when it failed there were only 7 SIPDs running on the server, we can subtract two... one was the base Trace and the second was database mail.

So that leaves just 5. My capturing of processes.. showing wait.. is a SQL statement. So how would I modify the following to capture the execution plan, without creating a trace to capture all execution plans? Note I have this running every 15min during the two hours that we have the most failures due to query timeout. Side note only the Jobs created parrallel processing.. 49 threads, 25 threads, and 49 threads. (Of course in this case I can get an estimated execution plan by selecting the job.)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

insert into zt_wait_Hist(

Capture_DT

, [Spid]

, ecid

, start_time

, [Age Seconds]

, nt_username

, [status]

, wait_type

, [Individual Query]

, [Parent Query]

, [program_name]

, Hostname

, nt_domain

)

SELECT

GETDATE() as Capture_DT

, er.session_Id AS [Spid]

, sp.ecid

, er.start_time

, DATEDIFF(SS,er.start_time,GETDATE()) as [Age Seconds]

, sp.nt_username

, er.status

, er.wait_type

, SUBSTRING (qt.text, (er.statement_start_offset/2) + 1,

((CASE WHEN er.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE er.statement_end_offset

END - er.statement_start_offset)/2) + 1) AS [Individual Query]

, qt.text AS [Parent Query]

, sp.program_name

, sp.Hostname

, sp.nt_domain

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

WHERE session_Id > 50

AND session_Id NOT IN (@@SPID)

ORDER BY session_Id, ecid