变量索引维护

Question

我们有一个SQL 2016数据库，其中有一个19亿行表，其中有一个var二进制(255)列，用于将nvarchar(2000)字段的HashBytes存储在同一个表中。

我们在var二进制字段上有一个非聚集索引，我们的索引维护脚本每2-3天对此执行一次重新组织。但这需要10+hours来完成。

有没有办法提高varbinary索引维护的速度？

Answer 1

如果您有一个有19亿行的表，我猜它的变化率很低。每天查看实际插入/更新的行数--很可能不到1%。

在这种情况下，每2-3天重新组织整个表是没有意义的(特别是考虑到这需要花费10+小时)。我开始只做索引维护每周，如果不是每月在一个表的大小。

从后退一步开始，问：“通过索引重组，我想要解决的问题是什么？”如果答案是慢速select语句，那么对19亿行表的索引维护将不是答案。

Answer 2

根据评论，我关于使用的问题是因为我认为这个索引的存在是为了检查现有的记录，即单例查找，没有扫描行为。考虑到这一点，我回应@BrentOzar的观点:频繁地重组组织不太可能产生任何实际的好处。关闭它，看看ETL的性能是否恶化，我怀疑没有。

如果有修改ETL进程的空间，那么我将考虑转储VARBINARY哈希并替换为BIGINT。我在datawarehouse过程中使用了类似的安排：

CAST(HASHBYTES('SHA1', longstring) AS BIGINT)

在这个用例中，截断导致的冲突不是一个问题。哈希用于通过包含8个字节键的索引(而不是255个字节的索引)或扫描包含4000字节列的表来检查一行是否已经存在。如果检查不产生任何行，则插入。如果散列上有匹配项，则对原始文本进行比较，以确定是否有现有行或需要插入。

Answer 3

什么是FILLFACTOR上的NonClustered索引？你在使用什么哈希算法？该索引是否将PAD_INDEX设置为ON？聚集索引(包括列数据类型)的定义是什么？

所有这些都将使我们更清楚地了解指数的物理组成。

还有什么其他的操作正在发生？意思是，更新NVARCHAR(2000)列了吗？你会删除很多行吗？应该增加碎片的唯一因素是:常规插入(因为哈希实际上是“随机”排序的)、更新NVARCHAR字段，因为这将改变哈希值(但不是大小)，以及大量删除。

这些问题的答案将使我们更清楚地了解分散现象是如何/为什么会增加的。

此外，除了达到Ola的脚本推荐解帧的默认限制的碎片级别之外，当您没有碎片整理时，您看到ETL性能有任何下降吗？

此外，我在将哈希值转换为BIGINT时也要小心，因为BIGINT只有8个字节，但是所有哈希算法--甚至MD5 --都大于8个字节(MD5 = 16字节，SHA1 = 20，SHA2_256 = 32，SHA2_512 = 64)。并且将大于8个字节的二进制值转换为BIGINT会悄悄地截断这些值，从而丢失准确性并增加误报的发生。以下查询显示此行为：

SELECT CONVERT(BIGINT, 0xFFFFFFFFFFFFFF),      --  7 bytes = 72057594037927935
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFF),    --  8 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFF),  --  9 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFFFF) -- 10 bytes = -1

当然，根据@Marks对用法的解释，这种截断可能只是增加了对NVARCHAR字段进行完全比较的频率。不过，我们应该注意到这种行为，因为它是一种无声的(即不明显的)截断。