主页 / dba / 问题 / 238897
Accepted
Irdis
Asked: 2019-05-24 05:20:59 +0800 CST

如何在 SQL Server 中控制执行计划

  • 772

我遇到了 SQL Server 生成不良执行计划的问题。我有 3 个具有相同结构和相同程序集的数据库。

我的查询如下所示：

select 
d.Id as DirectoryId,
d.MeetingId  as MeetingId,
--...
from dbo.MeetingsDirectories d
left join dbo.Meetings m ON m.Id = d.MeetingId
left join (select * from dbo.fnLatestDirectoryData()) dat on d.Id = dat.MeetingDirectoryId
...

不同的是

  1. 第一个数据库缓存了以下执行计划。这很糟糕，因为基本上它执行fnLatestDirectoryData的次数与我加入后的行dbo.Meetings数一样多dbo.MeetingsDirectories。我的证据是它的执行次数与进入左上角最终嵌套循环的实际行数相对应。但是执行fnLatestDirectoryData太昂贵了，如果行数约为 3k，它开始滞后（大约 32 秒）

    在此处输入图像描述

  2. 我只是通过更改它来重新编译程序。而不是进行聚集索引扫描，而是在进入嵌套循环之前插入表假脱机。我认为它只是将 fnLatestDirectoryData 的结果相乘并缓存了它们。但它的大小约为 1000x1000，这让我很担心。然而，执行时间下降到 8 秒。

    在此处输入图像描述

  3. 在第三种情况下，我向左连接添加了哈希提示，并观察到我的函数调用了一次，并且执行时间进一步下降。我也尝试添加合并提示，但它们的工作时间相对相同。

    left hash join (select * from dbo.fnLatestDirectoryData())

我的问题是：

  1. 我可以防止执行计划从第二阶段降级到第一阶段，所以我总是为每个存储过程制定固定的执行计划吗？我没有足够的证据，但我觉得有时 SQL Server 决定重新编译执行计划，如果它们不好，我的应用程序性能会显着下降。

  2. 第三种选择是否是我只会执行一次的受让人的正确方式fnLatestDirectoryData？

  3. 我可以可靠地修复连接顺序吗？我看到连接以不同的顺序发生，所以如果我做一些类似将它们组合成对（select (select from tableA join tableB) join tableC）的事情，它实际上会有所帮助。

sql-server execution-plan

1 个回答

  1. Best Answer

    1）我可以防止执行计划从第二阶段降级到第一阶段，所以我总是对每个存储过程都有固定的执行计划吗？我没有足够的证据，但我觉得有时 sql server 决定重新编译执行计划，如果它们不好，我的应用程序性能会显着下降。

    这里要注意的主要事情是统计数据变得陈旧和参数嗅探。无需过多深入，这里有一些方法可以解决这个问题。

    统计数据过期

    • 启用跟踪标志 2371（对于低于兼容级别的数据库130）以在更新统计信息时降低修改阈值。
    • 每天/每周更新您的统计数据
    • 更改统计更新的采样率（并可能 从 SQL Server 2016 SP1 CU4 / SQL Server 2017 CU1 开始保留这些速率）

    参数嗅探

    有时 SQL Server 需要创建一个新的计划由于统计更新，计划从缓存中删除等。

    有时这个计划对于其他参数不是最优的。

    一种解决方案是向OPTION(RECOMPILE)您的查询添加提示，以通过在每次执行时编译计划来增加 cpu 使用率，但为指定的参数获得更好的计划。这主要取决于执行查询的次数。

    2）第三种选择是否是授予受让人的正确方法，我只会执行一次fnLatestDirectoryData？

    处决

    执行对应于正常的nested loop join操作员行为。通过使用外部输入中的一行来查找匹配项，这些执行应该匹配外部输入中的行数（忽略嵌套循环预取等特殊情况）

    这并不意味着该函数被调用了 269 次，因为内联表值函数被扩展为查询计划，您也会在视图中看到行为。

    提示的可靠性

    该提示使优化器创建一个带有HASH JOIN, 的计划，这是唯一的保证。

    3）我可以可靠地修复连接顺序吗？我看到连接以不同的顺序发生，所以如果我做一些事情，比如将它们成对分组（选择（从 tableA 连接 tableB）连接 tableC）它实际上会有所帮助。

    括号

    用括号拆分连接不应该做任何事情，因为优化器会删除它们并以相同的方式优化查询。

    强制命令

    如果您真的想强制指定连接的顺序，OPTION(FORCE ORDER)可以使用 或SET FORCEPLAN ON。

    拆分查询

    另一种方法可能是将查询的一部分结果插入到临时表中，并将查询的其余部分连接到该临时表中。

    更多信息（截至撰写本文时）

    如果没有更多关于函数内部发生了什么以及查询计划的信息，就很难说。索引或查询调整可以很好地解决您的问题。

    • 4

相关问题