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.