Got SQL Server plans forced to serial execution? --Updated for SQL Server 2016 Query Store

Originally published June 7, 2013
Scroll down from a 2017 update relevant to SQL Server 2016 Query Store

Paul White talks about items that can force a serial plan in this blog post.
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx 

I wanted to perform a quick check while the instance was fairly idle, to see if there were many plans that had been forced to serial execution.  (Monkeying around with the entire stored plan cache on a busy system is not something I recommend.)  I cobbled this together from some queries that Jason Strate has posted.  Turned up 42 rows.  Gave me something to think about... and address with the developers.

I'm sure there are more efficient ways of finding problematic plans... would probably be a good idea to periodically check the memory grant dmv for outstanding memory grants attached to a query with current DOP of 1, for example.  But this was all I was smart enough to do today.   


WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

   cp.plan_handle, 

   c.value 

     (N'@NonParallelPlanReason', N'NVARCHAR(4000)') AS [NonParallelPlanReason],

   qp.query_plan, 

   qp.dbid, 

   DB_NAME(qp.dbid) AS [DBASE_NAME]

FROM

   sys.dm_exec_cached_plans AS cp

CROSS APPLY

   sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY

   qp.query_plan.nodes

     (N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan') AS t(c)

WHERE

   cp.cacheobjtype = N'Compiled Plan'

   AND c.value(N'@NonParallelPlanReason', N'NVARCHAR(4000)') IS NOT NULL

ORDER BY

   DB_NAME(qp.dbid), cp.plan_handle

By the way... it took 3:09 to return the resultset.  Query cost of 125838, executed with DOP 1.  Worker time 153633351.  Lots of logical reads, lots of logical writes. 


*****
March 30, 2017
  
I wanted to update the post to make it SQL Server 2016 Query Store ready 😊

This version of the query grabs only Query Store plan_id and NonParallelPlanReason.  The CTE is to make sure that plan_XML with more than 128 levels of nesting don't result in errors.  (But means you wouldn't get back the full plan XML from that CTE.) 

This query is still kinda slow, even using the Exists() method rather than checking the value like I used to.  About 10 minutes to retrieve 3000 nonparallel plans from a Query Store of 40000 plans.

I can probably speed it up by checking for forced serial plans within each query_hash, which is what I really want.  That'll be for another day.
  

;WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
serial_plan_xml_CTE (plan_id, qplan)
AS
(SELECT plan_id,
        CASE WHEN PATINDEX('%<RelOp NodeId="0"%', query_plan) > 0 THEN
        CONVERT(XML, SUBSTRING(query_plan, 1, PATINDEX('%<RelOp NodeId="0"%', query_plan) - 1) +
                     SUBSTRING(query_plan, PATINDEX('%</QueryPlan>%', query_plan), LEN(query_plan) + 1) ) 
  ELSE CONVERT(XML, query_plan) 
  END AS qplan 
  FROM sys.query_store_plan
  WHERE is_parallel_plan = 0)
SELECT qplan.plan_id, 
       c.value 
     (N'@NonParallelPlanReason', 
      N'NVARCHAR(4000)'
      ) AS [NonParallelPlanReason]
FROM
   serial_plan_xml_CTE qplan
CROSS APPLY
   qplan.nodes
     (N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan') AS t(c)
WHERE
   c.exist('./@NonParallelPlanReason') = 1
ORDER BY
   qplan.plan_id
OPTION (MAXDOP 1);