Sql-server – Finding Serial plan from Plan cache

execution-planparallelismplan-cachesql server

My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for further execution.
So every time i query the plan cache and i see like 50+ plan(i search for particular view in the query text to find out the queries) and to find the serial plan i have to open Query plan and see whether it is parallel or not.

SELECT 
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.creation_time,
cp.execution_count AS ExecutionCount,cp.plan_handle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
where 
st.text like '%FormSearchModel%'
--AND qp.query_plan.exist('//PhysicalOp="Parallelism"') = 0
--AND n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
order by creation_time desc

It tried using query_plan.exist('//PhysicalOp="Parallelism"') = 0 to find the serial plan and it is not working.
How can i achieve this.?Any suggestion.

Best Answer

Try this out

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
            cp.execution_count AS ExecutionCount,
            cp.plan_handle,
            st.text AS QueryText,
            qp.query_plan AS QueryPlan
FROM        sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE       1 = 1
--AND st.text LIKE '%FormSearchModel%'
AND         qp.query_plan.exist('//p:RelOp/@Parallel[ .= 0]') = 1
ORDER BY    cp.creation_time DESC;

If you want to find plans where all operations are serial, you'd have to do something like this:

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ),
relop AS 
(
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
            cp.execution_count AS ExecutionCount,
            cp.plan_handle,
            st.text AS QueryText,
            qp.query_plan AS QueryPlan,
            qp.query_plan.value('count(//p:RelOp)', 'INT') AS total_relop,
            qp.query_plan.value('count(//p:RelOp/@Parallel[ .= 0])', 'INT') AS serial_relop
FROM        sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE  qp.query_plan.exist('//p:RelOp/@Parallel[ .= 0]') = 1
)
SELECT *
FROM relop
WHERE relop.total_relop = relop.serial_relop
Related Question