1

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.

4

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
  • Can i count(Parallel attribute) = 0 ,then that means serial ryt?I was thinking of a way to count all parallel attribute to tackle the issue. – user9516827 Aug 10 '18 at 17:13
  • @user9516827 I don't think that's a very good approach. Many parallel plans will have some serial operations at the very end. But if that suits your needs, go for it. – Erik Darling Aug 10 '18 at 18:43
  • I understand,but i as a temporary solution,i have to filter out serial plans from the huge list of plans.Going through each plan to identify serial plan is a big task,that is why i am trying to find out ways to filter pure serial plans. Plans with zero parallel elements will give me the required result,but i am unable to put that logic in the query. – user9516827 Aug 13 '18 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.