下面的语句可以在TShootPerfProbs2008白皮书找到
---查询Task 会有多个Task存在 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 = 1 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 ---从执行计划中查询Find query plans that can run in parallel -- 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 p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//p:RelOp/@Parallel)', 'float') > 0 ---并行计划CPU高于执行时间 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 --Trace CPU高于执行时间 select EventClass, TextData from ::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default) where EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted and CPU > Duration/1000 -- CPU is in milliseconds, Duration in