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 requests
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

-- candidates for parallel execution, naive query
-- sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_exec_sql_text
select p.*, q.*, cp.plan_handle
from 
   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 q
where 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 reliable
select 
   qs.sql_handle, qs.statement_start_offset, qs.statement_end_offset, q.dbid,
   q.objectid, q.number, q.encrypted, q.text
from sys.dm_exec_query_stats qs 
   cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time > qs.total_elapsed_time

Thanks to Boris and Brian for such great information!

Cheers,

-Kevin

Comments

Plain text