It’s quite often at clients that I spend time working on issues revolving around parallelism. In fact, just this week it’s been the single most irritating performance problem that I’ve had to deal with.
If you aren’t up to snuff on parallelism there are a couple places I would start with getting up to speed:
- Case Study: Part 1: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Introduction to Using Wait Stats to Identify & Remediate Query Parallelism Bottlenecks
- Case Study: Part 2: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Suppressing Query Parallelism Eliminated CXPACKET Waits & Liberated 30% of CPU
Both articles are from Jimmy May (@aspiringgeek), who writes some great articles on SQL Server performance and scalability.
Two days ago, I was working on trying to determine which queries were causing the most trouble when it came to parallelism. That was when I modified the current queries executing with parallelism query to make it more useful. And a few days before I posted some queries that I also use to determine plans that may utilize parallelism.
While working on these parallelism issues, I started wondering if identifying query plans utilizing parallelism by CPU was the correct approach. Just because an execution plan has high CPU doesn’t necessarily mean that it is the biggest contributor or source of parallelism on the server.
Instead, I started looking at execution counts of the plans. The query that I’ll be listing below provided a stark indication of the highest contributor to parallelism in the issues I was working on by identify a single query plan that was utilizing parallelism and had been executed nearly a 100 thousand times since I’d cleared the procedure cache that morning.
SELECT TOP 50
CASE WHEN p.dbid = 32767 THEN '{RESOURCE}' ELSE DB_NAME(p.dbid) END as database_name
,OBJECT_NAME(p.objectid, p.dbid) as [object_name]
,qs.execution_count
,qs.total_worker_time
,qs.total_logical_reads
,CONVERT(decimal(12,3),qs.total_elapsed_time/1000000./qs.execution_count) as avg_elapsed_time_sec
,CASE statement_end_offset WHEN -1 THEN q.text
ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement
,p.query_plan
,q.text
,cp.plan_handle
FROM sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
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
ORDER BY qs.execution_count DESC
Now that I’ve shared this, what do you the reader use to identify queries that are utilizing parallelism in your environments?
Strate SQL 