List Currently-Executing Parallel Plans

Sample script that finds currently-executing parallel plans (indicated by an Exec_context_id greater than 0).This script, contributed by Microsoft's Tom Davidson, requires SQL Ser

 
 
 
 
 
(0)
Add to favorites
Databases
8/10/2009
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Wrong code
    1 Posts | Last post October 20, 2009
    • This query does not contain any reference to exec_context_id. I believe the proper query can be found in Tom's contribution to "Troubleshooting Performance Problems in SQL Server 2005":
      
      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