Learning More About Parallelism

CPU NeonIt’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:

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.

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]
,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
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?