Início / dba / Perguntas / 214601
Accepted
user9516827
Asked: 2018-08-11 05:12:11 +0800 CST

Encontrando o plano serial do cache do plano

  • 772

Meu aplicativo fica lento às vezes e quando eu verifico o plano do plano de consulta para uma operação específica, vejo que os pans seriais foram gerados e armazenados no cache do plano e isso está sendo levado para execução posterior. Então, toda vez que eu consulto o cache do plano e vejo mais de 50 planos (eu procuro uma visualização específica no texto da consulta para descobrir as consultas) e para encontrar o plano serial eu tenho que abrir o plano de consulta e ver se é paralelo ou não.

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

Ele tentou usar query_plan.exist('//PhysicalOp="Parallelism"') = 0para encontrar o plano serial e não está funcionando. Como posso conseguir isso.? Alguma sugestão.

sql-server execution-plan

1 respostas

  1. Best Answer

    Experimente isso

    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;

    Se você quiser encontrar planos onde todas as operações sejam seriais, você teria que fazer algo assim:

    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

relate perguntas