我正在处理一个我似乎无法解决的问题。我得到的这个存储过程存在速度问题。大约需要 35 秒。当运行adhoc时,它会立即完成。
这让我觉得这与参数嗅探有关。我尝试重写它以使用局部变量,优化未知\变量\等,并重新编译。
进行扫描的表很大,但即使使用 更新统计数据fullscan
,估计值仍然相差很大。也许可以重写查询,但我想弄清楚这一点。
来自测试环境的所有数据,其名称已更改。sql 文本文件用于 sp 和临时查询 - 如果这不起作用,请告诉我
特别计划 - https://www.brentozar.com/pastetheplan/?id=Hkvg5Ge40
ad hoc的参数-
SP 计划 - https://www.brentozar.com/pastetheplan/?id=S1w49GxER
SP 参数 -
参数如下:
(sp name)
@ TransactionDescriptionId VARCHAR(32),
@ CategoryCode VARCHAR(4000)
AS
DECLARE
@ CategoryHash VARBINARY(16) = HashBytes('MD5', @CategoryCode);
SP 和ad hoc版本中传递的值相同。我认为这可能是由于哈希被创建为传递参数的局部变量,但在临时版本中它做了同样的事情(对我来说)。
呃
这里列出的前两种方法在大多数情况下对于提高性能来说是非常无用的。您可能每隔 5-10 年就会遇到一种情况,他们会更好地改进一个特定的查询,但我会将这些放在我要尝试的事情列表中的很低位置。
在慢速计划中,占用最多时间的部分如下所示:
显然,表上有一个
Catalog_Items
生成 MD5 的计算列,但它没有被持久化。您不必保留它,但最终在表达式上使用 Filter 运算符并且在扫描表时无法应用谓词的原因是因为它必须在运行时按行计算。在快速查询中,您已经
CategoryCodeHash
进入了索引[IX_Catalog_Items_IdRetailer_CategoryCodeHash]
,它确实保留了值并使其可查找。至于为什么你在慢速计划中得到如此糟糕的估计,目前尚不清楚该
@CategoryHash
参数在这种情况下是参数还是局部变量,但是......@TransactionDescriptionId
具有该ParameterCompiledValue
属性的事实@CategoryHash
似乎并不表明它不是一个嗅探参数。在快速计划中,两者都没有该ParameterCompiledValue
属性,但如果没有完整的重现,我不知道你到底做了什么不同的事情。反正
SQL Server 基于成本的优化器要么发现查找 + 查找比聚集索引扫描 + 过滤更昂贵,要么从未达到查找 + 查找计划。许多人会告诉您“进行索引覆盖”,以便您需要的所有列都可以在索引中使用
[IX_Catalog_Items_IdRetailer_CategoryCodeHash]
。当然,这是一个选项,但您也可以采取一些措施来欺骗优化器使用窄索引,例如使用 self-join。这个想法是通过主键值将表与其自身连接起来,一个对表的引用用于选择要显示的所有列,另一个引用负责连接和过滤以及任何其他关系活动。
上下文地
关于为什么该计划不同,可能值得关注的一件事是各种SET 选项。在优化期间评估索引视图、计算列和筛选索引需要一些:
这
DatabaseContextSettingsId
两个计划的不同之处在于:与
您可以在这里看到一些设置:
您还可以查看数据库是否有任何默认值违反了在没有规范的情况下创建的所有模块的要求:
根据您所看到的内容,您可能需要尝试使用以下命令重新创建存储过程:
其他一些东西
出于您的一般考虑,尽管对于此特定查询来说不一定有什么大不了的:
NOLOCK
很臭并且可能返回不正确/不一致的数据TOP
没有 anORDER BY
是一个不确定的命题@Table Variables
强制修改以单线程运行,并且当您开始将它们连接到其他表时通常不会那么热,因为即使建立索引它们也不会获得列级统计信息注意操作
CouldNotGenerateValidParallelPlan
符的属性INSERT
。但在这种情况下,两个计划成本都非常低,无论如何都不太可能获得并行执行计划。