Troubleshooting Parallel Queries on SQL Server 2005

Parallel queries (that is, queries that are split out to run concurrently on multiple CPUs) can offer a big performance gain in certain circumstances.  Of course, you need an SMP server to properly take advantage of this opportunity.  SQL Server 2005 enables you to simulate multiple CPUs by using the -Px switch, where x equals the desired number of CPUs. (Check Brian Moran's column for more details or take a look at this Microsoft Flash Tip.)

There's also a great TechNet Webcast called "Troubleshooting Performance Problems in Microsoft SQL Server 2005" (Level 200) with Boris Baryshnikov that you should look at.  The webcast includes some very useful scripts for parallel queries, some of which are repeated here:

-- parallel queries-- sys.dm_exec_requests, sys.dm_os_tasks, sys.dm_exec_sessions-- for currently executing requestsselect 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_handlefrom    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

-- candidates for parallel execution, naive query-- sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_exec_sql_textselect p.*, q.*, cp.plan_handlefrom    sys.dm_exec_cached_plans cp      cross apply sys.dm_exec_query_plan(cp.plan_handle) p      cross apply sys.dm_exec_sql_text(cp.plan_handle) as qwhere cp.cacheobjtype = 'Compiled Plan'    and cast(p.query_plan as nvarchar(max)) like '%Parallel="1"%'

--sp_configure 'cost threshold for parallelism', 0 --reconfigure-- CPU consumption is bigger than duration-- not very reliableselect    qs.sql_handle, qs.statement_start_offset, qs.statement_end_offset, q.dbid,   q.objectid, q.number, q.encrypted, q.textfrom sys.dm_exec_query_stats qs    cross apply sys.dm_exec_sql_text(qs.plan_handle) as qwhere qs.total_worker_time > qs.total_elapsed_time

Thanks to Boris and Brian for such great information!

Cheers,

-Kevin