TSQL to obtain a list of current execution of Parallel Plans (MAXDOP)

PARALLELISM is a close friend of DBA where you get to see it as a common occurence in the multi-processor server environment and during such performance issues you can also investigate whether a parallel plan is in use. For instance If a particular query is slow when it is using a parallel plan, you can try forcing a non-parallel plan by using the OPTION (MAXDOP 1) hint asper the documentation.

CraigFreedman referring about parallel query execution and a discussion of query processing, query execution, and query plans in SQL Server.

To obtain list of current execution of Parallel Plans you can run following TSQL:

select
  qus.sql_handle,
  qus.statement_start_offset,
  qus.statement_end_offset,
  q.dbid,
  q.objectid,
  q.number,
  q.encrypted,
  q.text
from sys.dm_exec_query_stats qus
 cross apply sys.dm_exec_sql_text(qus.plan_handle) as q
where qus.total_worker_time > qus.total_elapsed_time

The default behaviour of query optimizer is typically selects the best execution plan for a query, in this case by having join hints, query hints within the table queires you could keep up the optimum performance. Go through the relevant blogs on this site such as:

http://sqlserver-qa.net/blogs/perftune/archive/2007/05/11/get-statistics-on-query-plan-optimizer-execution.aspx

http://sqlserver-qa.net/blogs/perftune/archive/2007/05/25/identify-and-troubleshoot-slow-running-queries-in-sql-server.aspx

http://sqlserver-qa.net/blogs/perftune/archive/2007/04/26/high-cpu-spikes-affecting-performance.aspx