主页 / dba / 问题 / 214601
Accepted
user9516827
Asked: 2018-08-11 05:12:11 +0800 CST

从计划缓存中查找序列计划

  • 772

我的应用程序有时会变慢，当我检查特定操作的查询计划计划时，我看到生成了串行平移并存储在计划缓存中，这将被用于进一步执行。因此，每次我查询计划缓存时，我都会看到 50 多个计划（我在查询文本中搜索特定视图以找出查询）并找到串行计划，我必须打开查询计划并查看它是并行的还是不是。

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

它尝试使用query_plan.exist('//PhysicalOp="Parallelism"') = 0查找串行计划，但没有用。我怎样才能做到这一点？任何建议。

sql-server execution-plan

1 个回答

  1. Best Answer

    试试这个

    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;

    如果你想找到所有操作都是串行的计划，你必须这样做：

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

相关问题