主页 / dba / 问题 / 311740
Accepted
SEarle1986
Asked: 2022-05-06 13:09:48 +0800 CST

找出哪些查询会受到对 CTFP 的拟议变更的影响

  • 772

我们其中一台服务器上的并行设置的成本阈值设置为通常被认为太低的值 (15)，我们正在考虑将其增加到 50，以希望在 CPU 变高时减少它。

我想知道哪些查询会受到影响，以便我们可以对它们进行一些测试和监控。

我解决这个问题的方法是查询计划缓存（并解析 XML）。尽管使用计划缓存存在问题（计划在内存压力下过期/抛出，服务器重新启动等）这是解决此问题的最佳方法吗？

我的查询是

;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 个回答

  1. Best Answer

    由于您使用的是 SQL Server 2016，因此我建议您在用户数据库上启用查询存储。您可以使用上面相同的查询，但指向sys.query_store_plan DMV 而不是计划缓存来获取和询问查询计划 XML。

    使用查询存储可确保您的计划被持久化，并且可能会比缓存保留它们的时间更长。查询存储还允许您分析查询的其他元素以帮助确定潜在影响。由于 DMV 具有is_parallel_plan列，因此它还可以更轻松地识别并行计划，您可以使用该列仅识别那些并行成本阈值之间的查询。

    然后，您还可以在对 CTFP 进行任何更改后轻松识别查询回归。这意味着如果您在更改后仅识别少数回归，您可能会在优化串行执行查询时强制使用旧的并行计划，并且更改 CTFP 并将其回滚不会强制执行这些计划重新编译并可能从缓存中刷新。

    它与提高数据库的兼容性级别时建议的基本原则相同，这使您可以轻松设置基线，然后在更改后识别回归。

    • 2

  2. 缓存中的计划包含多个查询。您可以通过使用.nodes.

    WHERE您还可以通过按如下方式推送到 XQuery 来稍微提高此查询的性能

    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);

    如果您想使用其他答案所推荐的查询存储，您可以稍微更改一下

    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);

    您可以进一步加入sys.query_store_runtime_stats和其他人以获取更多信息。

    • 0

相关问题