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.

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

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

USE AdventureWorks
GO

IF OBJECT_ID('dbo.BigTable') IS NOT NULL
DROP TABLE dbo.BigTable;
GO

WITH
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
ORDER BY n
GO

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:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
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
,cp.objtype
,p.query_plan
,cp.UseCounts
,cp.plan_handle
,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.