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

[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,

[ ] query [ ] stored procedure [ ] database thing maybe  

[ ] yesterday [ ] in recent memory [ ] at some point 

[*Insert appropriate closing remarks*]

#1 楼

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

哦，不，我很抱歉听到这个消息！让我们从一些基础知识开始，让您精打细算。

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

，这是一种解决奇怪问题的方法。这个名字从舌头滚滚而来。就像德语中的松鼠一样。

通常是你的朋友。

当查询命中您的服务器时，必须编译一个计划。为了稍后节省时间和资源，将根据该参数将导致代码处理并返回的估计行来缓存执行计划。

描述这种情况的最简单方法是想象一个存储过程，该过程需要从两个不对称的总体中计数。

例如：


未受伤的穿着CrossFit衬衫的人们：零
在退缩的时候穿着CrossFit衬衫的人们：全部

很显然，必须执行该代码比其他工作要多得多，而您想做的工作量完全不同的查询计划看起来会完全不同。

我要面对的是什么？是发现，测试和修复的真正困难的问题。


很难找到，因为它不会一致地发生
很难测试，因为您需要知道哪些参数会导致不同的计划
很难修复，因为有时需要查询和索引调优
很难修复，因为您可能无法更改查询或索引
很难修复，因为即使您更改查询或索引，它也可能会回来
/>
快速修复

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

如果它是存储过程，请尝试运行EXEC sys.sp_recompile @objname = N'schema.procname'。这将导致该过程在下次运行时重新编译新计划。

无法解决的问题：


当前正在运行它的进程。

这不能保证什么：


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

您也可以将sp_recompile指向表或视图，但要注意，所有与该表或视图相关的代码都将指向该表或视图。重新编译。这可能会使问题变得更加棘手。

如果这是一个参数化查询，那么您的工作会更加困难。您需要跟踪SQL句柄。您不想释放整个计划缓存-就像对表或视图使用sp_recompile一样，您可能会触发（哈哈哈）一堆意外后果。

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

无法解决的问题：


当前正在运行的进程。

不能保证的内容：


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

我仍然需要帮助！重新需要以下内容：


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

获取查询计划和查询

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

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

您应该从任何这些中看到非常相似的输出。同样，邀请凉爽的蓝色clicky列的查询计划是您的朋友。 。要做到这一点，请单击那些邀请蓝色clicky列之一。您的查询计划应显示在新的SSMS选项卡中。



如果您对共享公司代码和查询不满意，可以使用Sentry One的免费Plan Explorer工具。匿名化您的计划。请记住，这会使获得帮助变得更加困难-匿名代码很难阅读和弄清楚。

我们讨论的所有这些工具都应返回查询文本。您无需在此处执行其他任何操作。

获取参数要困难一些。如果使用的是Plan Explorer，则底部的选项卡会为您列出所有选项。



如果使用的是sp_BlitzCache *，则可以单击专栏，为您提供存储过程的执行语句。



获取表和索引定义

您可以轻松地在SSMS中右键单击以编写脚本内容。



如果想一次性获得所有内容，sp_BlitzIndex *可以直接将其指向表格。

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

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

收集此信息并将其添加到您的问题中应该会给人们足够的信息以帮助，或者为您指明正确的方向。

我想亲自做！
好酷。我为你感到高兴。你这个疯子。

人们以多种方式“修正”参数嗅探：


重新编译提示
优化未知
/>优化值

但是这些实际上只是以不同的方式禁用了参数嗅探。这并不是说他们无法解决问题，只是他们并没有真正找到根本原因。
那是因为找到根本原因通常很困难。您必须寻找那些令人讨厌的“计划质量问题”。

从快速计划和慢速计划开始，寻找诸如以下的差异：


使用的索引
加入顺序
串行vs并行

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


查找
排序
联接类型
内存授予（并通过扩展名，溢出）
假脱机

不要在搜索与扫描，索引碎片或任何其他内容中太复杂人们四处乱逛的货物。

通常，存在一个非常基本的索引问题。有时，代码需要重新编写一些。

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


应用程序中的速度慢，SSMS中的速度快吗？ -Erland Sommarskog
对参数嗅探1、2、3进行故障排除-Tara Kizer
为什么调整存储过程错误（局部变量问题）-Kendra Little
如何像使用参数一样Pro and Boost Performance-Guy Glantser
参数嗅探，嵌入和RECOMPILE选项-Paul White

如果您正在阅读本文，并且认为我错过了链接或有用的工具，请离开一条评论。我会尽力使它保持最新状态。

#2 楼

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


数据分布/容量发生变化，越过了优化器搜索树决策临界点
索引/文件碎片化
由于数据更改，统计信息已被更新/添加/删除或变得过时和误导
Windows内存利用率已更改
事务日志已满且未被截断，从而导致重复的物理文件扩展
架构已更改-添加，修改或删除了索引/索引视图/列/约束，更改了数据类型等。
跟踪标记设置已更改
已应用Windows更新
数据库或服务器设置已更改
服务器CU级别已更改
客户端应用程序会话设置已更改

仅在采取一些明确的措施后，此列表中的项目6-11才会发生。我想您打算排除那些，但是很多经历挑战的人并不知道其他人进行了更改，因此在踏上清除计划缓存条目之路之前，值得检查一下。

#3 楼

只是为了增加现有答案，以防万一，第二天“突然”查询的行为有所不同时，请检查：


使用表的方案是否已更改自上次以来？如果是SSMS，则可以在“对象资源管理器”中右键单击服务器，然后选择Reports → Standard Reports → Schema Changes History。
项目数是否急剧增加？当使用的表中有大量数据时，您的查询可能会慢很多。
是否有人在与您同时使用数据库？也许选择不干扰彼此工作的时隙。
系统统计数据如何显示？也许服务器正在热运行并且正在节流CPU，或者硬盘空间不足或交换不足。也许还有另一个硬件问题，例如服务器机房中的大火或洪水。

#4 楼

另一种可能性是您的基础架构团队正在使用VMware上的vMotion之类的工具，而支持SQL实例的VM在主机之间无缝移动，而DBA却不知道。

当您的基础结构外包时，这是一个真正的问题...我正面临着一场噩梦。