Início / dba / Perguntas / 311740
Accepted
SEarle1986
Asked: 2022-05-06 13:09:48 +0800 CST

Encontre quais consultas serão afetadas pela alteração proposta ao CTFP

  • 772

O limite de custo para a configuração de paralelismo em um de nossos servidores é geralmente considerado muito baixo (15) e estamos considerando aumentar para 50 na esperança de reduzir a CPU, pois ela está se tornando alta.

Gostaria de saber quais consultas serão afetadas para que possamos fazer alguns testes e monitorá-las.

A maneira como fiz isso é consultar o cache do plano (e analisar o XML). Não obstante os problemas de usar o cache do plano (planos envelhecendo / descartados sob pressão de memória, reinicializações do servidor etc.), essa é a melhor maneira de fazer isso?

Minha consulta é

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') AS QueryCost ,
        t.text
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
WHERE   p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') > 15 AND
        p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') <= 50
performance sql-server-2016

2 respostas

  1. Best Answer

    Como você está usando o SQL Server 2016, recomendo habilitar o Repositório de Consultas em seus bancos de dados de usuário. Você pode usar a mesma consulta acima, mas apontar para o DMV sys.query_store_plan em vez do cache do plano para buscar e interrogar o XML do plano de consulta.

    O uso do Query Store garante que seus planos sejam mantidos e provavelmente serão mantidos por mais tempo do que o cache pode retê-los. O Repositório de Consultas também permite analisar outros elementos das consultas para ajudar a determinar possíveis impactos. Também facilita a identificação de planos paralelos, pois o DMV possui a coluna is_parallel_plan , que você pode usar para identificar apenas as consultas entre os limites de custo que são paralelos.

    Você também tem a capacidade de identificar facilmente as regressões de consulta depois que qualquer alteração no CTFP for feita. Isso significa que, se você identificar apenas algumas regressões após a alteração, poderá forçar o uso do plano paralelo antigo enquanto trabalha para otimizar a consulta para execução serial, e alterar o CTFP e revertê-lo não forçará esses planos para ser recompilado e potencialmente liberado do cache.

    É o mesmo princípio básico sugerido ao aumentar o Nível de Compatibilidade de um banco de dados que permite definir facilmente uma linha de base e identificar regressões após a alteração.

    • 2

  2. Os planos no cache contêm várias consultas. Você pode ver consultas individuais de cada plano dividindo o XML usando .nodes.

    Você também pode melhorar um pouco o desempenho dessa consulta, enviando o WHEREXQuery da seguinte maneira

    DECLARE @newCTFP int = 50;
DECLARE @oldCTFP int = (SELECT CAST(value AS int) FROM sys.configurations c WHERE c.name = 'cost threshold for parallelism');

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  x.stmt.query('.') AS StatementPlan,
        x.stmt.value('@StatementSubTreeCost','float') AS QueryCost,
        t.text
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
        CROSS APPLY p.query_plan.nodes('
            /ShowPlanXML/BatchSequence/Batch/Statements/*[@StatementSubTreeCost]
                [@StatementSubTreeCost > sql:variable("@oldCTFP") and @StatementSubTreeCost <= sql:variable("@oldCTFP")]
          ') x(stmt);

    Se você quiser usar o Query Store, conforme recomendado pela outra resposta, você pode alterá-lo um pouco

    DECLARE @newCTFP int = 50;
DECLARE @oldCTFP int = (SELECT CAST(value AS int) FROM sys.configurations c WHERE c.name = 'cost threshold for parallelism');

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  x.stmt.query('.') AS StatementPlan,
        x.stmt.value('@StatementSubTreeCost','float') AS QueryCost,
        t.query_sql_text
FROM    sys.query_store_plan p
        JOIN sys.query_store_query q ON q.query_id = p.query_id
        JOIN sys.query_store_query_text t ON t.query_text_id = q.query_text_id
        CROSS APPLY (SELECT CAST(p.query_plan AS xml) ) v(xmlPlan)
        CROSS APPLY v.xmlPlan.nodes('
            /ShowPlanXML/BatchSequence/Batch/Statements/*[@StatementSubTreeCost]
                [@StatementSubTreeCost > sql:variable("@oldCTFP") and @StatementSubTreeCost <= sql:variable("@oldCTFP")]
          ') x(stmt);

    Você ainda pode se juntar a sys.query_store_runtime_statse outros para obter ainda mais informações.

    • 0

relate perguntas