主页 / dba / 问题 / 280998
Accepted
D-K
Asked: 2020-12-07 09:17:44 +0800 CST

SQL Server - 我可以手术删除一个糟糕的缓存查询计划还是我追求错误的想法？

  • 772

让我设置舞台：

  • SQL 服务器 > 2016
  • 具有相同架构的多个数据库
  • 数据相似但不相同
  • 存在相同的索引等，除一个以外的所有系统都按预期工作
  • 检查执行计划建议创建一个索引，其中所有列都作为 INCLUDE。没有包含的相同基列已经存在。

问题：

  • 在其中一个数据库上，查询引擎不利用现有索引并使用全表扫描
  • 手动执行查询（使用参数）——全表扫描！
  • 手动执行不带参数的查询（使用固定日期） - 使用索引。
  • 使用参数手动执行查询并添加WITH (INDEX(update_ts_INDEX))- 使用索引。查询时间从 2 分钟缩短到不到 1 秒。

假设：

  • 我相信查询缓存可能已损坏 - 但我不知道这是否可能。顺便说一句，我是一名 Java 开发人员，过去 3 年一直在研究 SQL Server 性能调优等。
  • 不带参数手动执行查询的事实——查询引擎选择正确的索引——让我大吃一惊。想法？

笔记：

  • 在运行相同代码和查询的其他服务器上，我找不到任何证据表明同一查询需要任何时间。他们必须使用索引。
  • 我只是在另一个带有参数的区域中手动执行了命令，它使用了索引，所以问题确实只存在于一个区域 - 一个数据库中。

查询示例：

DECLARE @P1 DATETIME = GETDATE() - .1;

SELECT value_1, update_ts, value_2 FROM PRODUCTION_TABLE 
WHERE (PRODUCTION_TABLE.update_ts > @P1);

如果我删除变量并手动编码日期，它将使用索引。

问题： 我可以通过外科手术删除此特定查询的错误缓存查询计划吗？

- - 更新 - -

UsingOPTION (RECOMPILE)导致查询引擎选择正确的索引。

sql-server query-performance

2 个回答

  1. 你可以！

    运行此命令以查找您的计划 ID：

    select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

    然后将该 ID 放入名为 DBCC FREEPROCCACHE 的存储过程中

    DBCC FREEPROCCACHE (plan_handle_id_goes_here)

    不过，您总是可以释放整个事物。它会在第一次运行每个查询时增加几秒钟，但不会超过服务器重置（或 Web 服务器中的 IISRESEt）无论如何都会做的事情。

    这是您清除所有内容的方式：

    DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

    在此处查看更多信息https://serverfault.com/questions/91285/how-do-i-remove-a-specific-bad-plan-from-the-sql-server-query-cache

    • 2
  2. Best Answer

    您的计划本身可能并不腐败。您的数据可能不喜欢该字段的数据分布均匀，并且该计划可能是使用异常值创建的（或者您的代码过于复杂，但根据您的示例，我猜不是。如果它很复杂，从重构开始）。除了查询存储，计划在服务器重新启动时被丢弃，存在内存压力等。然后它们被重新创建，通常基于您提供的参数，这可能是也可能不是异常值。

    根据具体情况，您有几个选择。

    如果您有查询存储，请使用它。放弃计划，使用代表值重新运行并将其固定。如上所述，删除针对异常值的计划的问题在于它有可能再次出现。固定计划可以完全避免这种情况。

    如果这是一个不经常运行的 SQL （每小时几次），并且运行了相当长的时间（超过几秒钟，似乎是这样），只需让它每次重新创建一个新计划并且代码是不太复杂；潜在的节省是值得的。 OPTION RECOMPIE

    但是，如果您的参数具有一般代表性值，则可以添加OPTIMIZE FOR @var = '2020-01-01'查询提示。这意味着当重新生成新计划时，它不会使用提供的值来确定计划，而是使用您提供的值。请记住，当您确实遇到异常值（例如，有时 NULLABLE 字段中的 NULL）时，您将无法获得理想的计划。 这是我在类似情况下最常走的路线，在你的情况下，我会简单地硬编码一个最近的日期（如果这有意义的话）。

    您还可以做其他事情，但根据我的经验，这些事情的价值会急剧下降。

    • 1

相关问题