Can You Dig It? – Parallelism in the Plan Cache

Follow the rest of this series at the Can You Dig It? – Plan Cache series introduction post.

She can dig it!
D Sharon Pruitt

In the lead in to this post, I talked about how the plan cache can be a gold mine of information.  There are a number of places that one can start to start demonstrating this.

Why not start at the beginning and start with the query that opened up the idea of querying the plan cache to me.  This is querying the plan cache for parallelism.


A number of years ago, I read the white paper Troubleshooting Performance Problems in SQL Server 2005.  In the section on troubleshooting CPU bottlenecks, I happened on a few paragraphs that talked about parallelism.

In that section, there is a query that can be used to look through the entire plan cache to find incidents of parallelism.  That’s a bit amazing!  It meant that by just executing a query I could check the entire plan cache for a aspect of an execution plan and get all of those plans returned.

Wow!  The definition of awesome-sauce.

The Query

What is this great query?  Well, here’s the query from the white paper:

-- Find query plans that may run in parallel
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
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
max(//p:RelOp/@Parallel)', 'float') > 0

To test this query out, run the query above after running statement below:

USE AdventureWorks

DROP TABLE dbo.BigTable;

l0 AS (select 0 AS c union all select 0),
l1 AS (select 0 AS c from l0 as a cross join l0 as b),
l2 AS (select 0 AS c from l1 as a cross join l1 as b),
l3 AS (select 0 AS c from l2 as a cross join l2 as b),
l4 AS (select 0 AS c from l3 as a cross join l3 as b),
l5 AS (select 0 AS c from l4 as a cross join l4 as b),
nums as (select row_number() over(order by (select null)) as n from l5)
SELECT TOP (1000000) *, NEWID() AS ID, GETDATE() AS Today, n % 100000 AS SampleColumn
INTO BigTable
FROM nums

SELECT TOP 10 * FROM dbo.BigTable
WHERE SampleColumn = 1

The output from the parallelism seeking query will look like the following:

The fourth column is the execution plan that is utilizing parallelism for the SELECT statement. The plan should look similar to the image below.  A red arrow of pointing things out has been added to point out the evidence of parallelism.

Query Next Version

While the original query was useful, I did end up writing a version of this that was more helpful for my real-life uses.  That version of the query is the following:


COALESCE(DB_NAME(p.dbid), p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)')) AS DatabaseName --Works in a number of cases, but not perfect.
,DB_NAME(p.dbid) + '.' + OBJECT_SCHEMA_NAME(p.objectid, p.dbid) + '.' + OBJECT_NAME(p.objectid, p.dbid) AS ObjectName
,CAST('' AS xml) AS SQLText
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.exist('//RelOp[@Parallel = "1"]') = 1
ORDER BY COALESCE(DB_NAME(p.dbid), p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)')), UseCounts DESC

Obviously, I prefer this version.  With the addition of the database column, object name, and the SQL text column you can get a better idea where parallelism is occurring.  The output from the above query is below:

Next Steps

If you are wondering what to make of the XQuery that is in the queries above, take a look at my XQuery posts.  They should help you out.  If there are pieces of XQuery that aren’t quite flushed out, feel free to make some requests.

When you are dealing with parallelism there are a number of ways that it can be mitigated.  Also, just having parallelism isn’t necessarily a bad thing.  It can be quite helpful for processing larger result sets, especially in data warehousing scenarios.  The key to using this query is to apply it when there are issues in your environment related to parallelism.  Since this opens a whole cautery of directions to take this post, I’ll just say stay tuned for more later.

Stay tuned for the next post that will talk more about the procedure cache.

10 thoughts on “Can You Dig It? – Parallelism in the Plan Cache

    1. Sure. I've looked at it a couple times. I tended to avoid the CPU cost since I couldn't be certain that the costs were truly parallelism. With the parallel flag, that's a fairly hard and fast indicator that parallelism is being used.


  1. I used the "copy to clipboard" and tried to run this in 2008 and got this:

    Msg 102, Level 15, State 1, Line 6

    Incorrect syntax near '<'.


Comments are closed.