为什么我的查询突然比昨天慢？

亲爱的[你的名字在这里]！

哦不，我很遗憾听到这个消息！让我们从一些基础知识开始，让您快速搞定。

您遇到的事情称为参数嗅探

这是一个奇怪的奇怪问题的出路。这个名字从舌头上滚下来。就像德语中的松鼠一样。

它通常是你的朋友。

当查询到达您的服务器时，必须编译计划。为了以后节省时间和资源，执行计划会根据估计的行进行缓存，该参数将导致您的代码处理和返回。

描述这种情况的最简单方法是想象一个需要从两个不平衡的群体中计算事物的存储过程。

例如：

• 未受伤的穿着 CrossFit 衬衫的人：零

• 穿着 CrossFit 衬衫的人在畏缩时畏缩：所有

显然，该代码的一次执行将比另一次执行更多的工作，并且您想要执行完全不同数量的工作的查询计划看起来完全不同。

我反对什么？

这是一个真正难以发现、测试和修复的问题。

• 很难找到，因为它不会始终如一地发生
• 很难测试，因为您需要知道哪些参数会导致不同的计划
• 很难修复，因为有时它需要查询和索引调整
• 很难修复，因为您可能无法更改查询或索引
• 这很难修复，因为即使您更改查询或索引，它仍然可能会回来

快速修复

有时，您所需要的只是一点清晰。或者更确切地说，您的计划缓存确实如此。

如果是存储过程

尝试运行EXEC sys.sp_recompile @objname = N'schema.procname'。这将导致该过程在下次运行时重新编译一个新计划。

这不能解决的问题：

• 当前运行它的进程。

这不能保证：

• 重新编译后运行的下一个进程将使用一个参数，该参数可以为您提供一个好的计划。

您也可以指向sp_recompile一个表或视图，但请注意所有触及该表或视图的代码都将重新编译。这可能会使问题变得更加困难。

如果是参数化查询

你的工作有点困难。您需要跟踪 SQL 句柄。你不想释放整个计划缓存——就像sp_recompile对表或视图使用一样，你可能会触发（哈哈哈）一大堆意想不到的后果。

找出该命令的最简单方法是运行sp_BlitzWho *! 有一个名为“修复参数嗅探”的列，其中包含从缓存中删除单个计划的命令。但是，这与重新编译具有相同的缺点。

这不能解决的问题：

• 当前运行它的进程。

这不能保证：

• 重新编译后运行的下一个进程将使用一个参数，该参数可以为您提供一个好的计划。

我还需要帮助！

我们将需要以下东西：

• 好的查询计划，如果可能的话
• 糟糕的查询计划
• 使用的参数
• 有问题的查询
• 表和索引定义

获取查询计划和查询

如果查询正在运行，您可以使用sp_BlitzWho * 或sp_WhoIsActive来捕获当前正在执行的查询。

EXEC sp_BlitzWho;

EXEC sp_WhoIsActive @get_plans = 1;

如果查询当前没有执行，您可以使用sp_BlitzCache * 在计划缓存中检查它。

如果您使用的是 SQL Server 2016+，并且打开了查询存储，则可以使用sp_BlitzQueryStore *。

EXEC dbo.sp_BlitzCache @StoredProcName = 'Your Mom';

EXEC dbo.sp_BlitzQueryStore @StoredProcName = 'Your Mom';

这些将帮助您跟踪存储过程的缓存版本。如果只是参数化代码，您的搜索会有点困难。不过，这可能会有所帮助：

EXEC dbo.sp_BlitzCache @QueryFilter = 'statement';

您应该从其中任何一个中看到相当相似的输出。同样，邀请酷蓝色点击列的查询计划是您的朋友。

共享计划的最简单方法是使用Paste The Plan *，或将 XML 转储到 pastebin。要做到这一点，请单击其中任何一个吸引人的蓝色点击列。您的查询计划应出现在新的 SSMS 选项卡中。

如果您对共享公司的代码和查询很敏感，您可以使用Sentry One 的免费计划资源管理器工具来匿名化您的计划。请记住，这使得获得帮助变得更加困难——匿名代码更难阅读和理解。

我们讨论的所有这些工具都应该返回查询文本。你不需要在这里做任何其他事情。

获取参数有点困难。如果您使用的是Plan Explorer，底部有一个选项卡，可以为您列出所有内容。

如果您使用的是sp_BlitzCache *，则有一个可单击的列，它为您提供存储过程的执行语句。

获取表和索引定义

您可以轻松地右键单击 SSMS 来编写脚本。

如果您想一次性获得所有内容，则 sp_BlitzIndex * 如果您将其直接指向一张桌子会有所帮助。

EXEC dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow2010',
                       @SchemaName = 'dbo',
                       @TableName = 'Users';

这将为您提供表定义（尽管不是作为创建语句），并为所有索引创建语句。

收集这些信息并将其添加到您的问题中应该可以让人们获得足够的信息来提供帮助，或者为您指明正确的方向。

我想自己做！

嗯，很酷。我为你感到高兴。你这个疯子。

人们认为他们“修复”参数嗅探的方式有很多：

• 重新编译提示
• 针对未知进行优化
• 优化价值

但是这些实际上只是以不同的方式禁用参数嗅探。这并不是说他们不能解决问题，他们只是没有真正找到根本原因。

那是因为找到根本原因通常有点困难。您必须寻找那些讨厌的“计划质量问题”。

从快速与慢速计划开始，寻找不同之处，例如：

• 使用的索引
• 加入订单
• 串行与并行

还要寻找使您的代码对参数嗅探敏感的不同运算符：

• 查找
• 排序
• 加盟类型
• 内存授予（以及扩展，溢出）
• 线轴

不要太专注于搜索与扫描、索引碎片或任何人们对货物的狂热追求。

通常，有一个非常基本的索引问题。有时代码需要稍微重写。

如果您想了解有关参数嗅探的更多信息：

• 应用程序慢，SSMS 快？——厄兰·索马斯科格

• 参数嗅探1 , 2 , 3故障排除- Tara Kizer

• 为什么你调错存储过程（局部变量的问题） - Kendra Little

• 如何像专业人士一样使用参数并提高性能- Guy Glantser

• 参数嗅探、嵌入和重新编译选项- Paul White

如果您正在阅读本文，并且您认为我错过了链接或有用的工具，请发表评论。我会尽力保持最新。

参数嗅探并不是查询性能变化的唯一可能原因。以下任何常见原因都可能表现出相同的症状：

1. 数据分布/数量发生变化，跨越优化器搜索树决策临界点
2. 索引/文件碎片化
3. 由于数据更改，统计数据已更新/添加/删除或变得陈旧和误导
4. Windows 内存利用率已更改
5. 事务日志已满且未截断，导致重复物理文件扩展
6. 架构更改 - 添加、修改或删除索引/索引视图/列/约束，更改数据类型等。
7. 跟踪标志设置已更改
8. 已应用 Windows 更新
9. 数据库或服务器设置已更改
10. 服务器 CU 级别已更改
11. 客户端应用程序会话设置已更改

此列表中的第 6 - 11 项只能在采取某些明确行动后发生。我猜你的意思是排除那些，但很多时候遇到挑战的人不知道其他人做了更改，在你开始清除计划缓存条目的路径之前，这是值得检查的。

只是添加到现有答案以防万一他们没有帮助，当“突然”您的查询在第二天表现不同时，请检查：

• 自上次以来，已使用表的方案是否发生了变化？如果是 SSMS，您可以在对象资源管理器中右键单击服务器并选择Reports → Standard Reports → Schema Changes History.
• 项目数量是否急剧增加？当使用的表中有大量数据时，您的查询可能会慢得多。
• 其他人是否与您同时使用数据库？也许选择不干扰彼此工作的时间段。
• 系统统计数据如何？也许服务器运行很热并且正在限制 CPU 或硬盘驱动器空间不足或交换空间不足。可能还有其他硬件问题，例如服务器机房发生火灾或洪水。

另一种可能性是，您的基础架构团队正在使用 VMware 上的 vMotion 等工具，并且支持您的 SQL 实例的 VM 在 DBA 不知情的情况下从主机无缝移动到主机。

当您的基础架构外包时，这是一个真正的问题......我正在做一个真正的噩梦。