如何微调一个巨大的问题堆表？

Question

问题：

在一个非常重要的生产数据库(MSSQLServer2016 SP1标准版)上，一个巨大的表导致了一个关键的性能问题。

关于问题表的事实:

• 没有索引的堆表。
• 450 GB大小
• 超过1.9亿条记录。
• 每次执行简单的SELECT查询时，超过900 MB/s物理I/O活动。如果查询包括WHERE子句，则最差。
• 在此表上执行的查询会导致应用程序UI延迟4到5分钟。

建议应用程序/系统制造商的解决方案：

• 将堆表转换为聚集索引表。

建议了与建议的解决方案有关的性能优化步骤：

• 截断db表(同意客户删除超过1年的记录)。
  • 不要删除记录。将过去一年中创建的记录复制到临时表中。截断原始表，然后插入从临时表复制到已清空的原始表的记录。

• 在db表上进行去碎片处理。
• 将db表从堆迁移到群集表。
• 创建一个聚集索引。
• 创建一个维护作业，并安排它定期重新构建聚集索引。

向社区

我的问题：

如何改进这样一个有问题的表的性能？您将在建议的性能优化步骤中添加或删除什么？

非常感谢和感谢在此之前。

从这里开始编辑：

好的，我不想一一回答所有的评论，所以我正在编辑这个问题并添加一些更多的信息。

第一:我不是DBA专家。我可以说是高级初学者。这就是我来这里尽可能多地向社区学习的原因。

第二:我们两周前才有了这个客户，而我自己还没有完全接触到环境。有关问题和表的信息(事实)直接来自客户的sys管理员。

第三:因为，我不知道为什么表是堆在第一手，没有索引，他们让它增长这么多，没有任何分区和任何情况下。

第四:建议的解决方案和建议的步骤不是我自己的，而是直接来自整个系统的制造商(这是一个众所周知的统一通信系统，而制造商是这方面最好的。我猜你们都知道是哪一个)。

第五:我希望下周能充分接触到环境，在此之前，我只想分享我手头的情况，并在陷入这场混乱之前尽可能多地征求意见/建议。

6:因为不幸的是，我将从这里开始处理这个烂摊子，并设法使它变得更好、更顺畅。

感谢每一个已经抽出时间和回答的人。我已经有洞察力了。我一定会分享更多的信息，只要我有更多的在手。

Answer 1

基于这些事实，1.9亿行，450 in，非索引堆，长时间运行的查询，您应该创建一个聚集列存储索引并以压缩的柱状格式存储数据。

但是，这可能不是最佳解决方案，因为您仍然有一个没有键和没有索引的表，这是很奇怪的。

Answer 2

我对你的“建议.步骤”有点困惑。

有些是相同的(从堆迁移到聚集表与创建聚集索引是一样的)。

现在还不清楚你是把它们作为恭维词，还是把它们作为彼此的替代物。

无论如何，你基本上有两个选择：

1. ALTER TABLE ... REBUILD，以摆脱所有的空空间，这可能是您的问题的原因。也就是说，把它当作一堆东西。
2. 通过创建聚集索引将表转换为群集表。

我们中的大多数人都喜欢在表上设置聚集索引，除非您有充分的理由不这样做。也就是说，我们不能说什么对你最好。你的桌子是堆的原因吗？若然，原因为何？与你发现的缺点相比，它有多强？等。

至于聚集索引的碎片整理:不确定这是否对您有好处。许多DBA的碎片“仅仅是因为”。如果您可以指出做碎片整理(减少“跳转磁盘”和潜在的更高的页面饱满)的优点，那么一定要这样做。

Answer 3

这就是我要采取的方法：

1. 您正在使用SQL Server 2016**，对吗？**启用查询存储，它将告诉您如何处理性能问题
2. 这是生产中的关键数据库，对吗？一定是在始终处于可用性组，对吧？将所有SELECT指向第二个副本，而UPDATE、ALTER和INSERT将发生在第一个副本上。这至少可以减少read问题。
3. 这张桌子太大了，对吧？这就是为什么表分区被发明了！. column =‘column 1’>按月，或按年，或根据您的ID列按100万行分组，等.你必须明白你的WHERE在寻找什么。你拿到了吗？所以用你最想要的列来划分那个表。
4. 那两个设置呢？我知道我可能听起来很平庸，但你设置了并行和MAXDOP的成本阈值吗？
5. 每个人都想要那张桌子，对吧？是啊，但有些问题比其他问题更重要，所以.使用资源调速器并对那些真正需要资源的查询进行排序
6. 1.9亿行和450 190数据库？没有人愿意承认这一点，但这种情况发生的频率超过了应有的水平。有很多公司都有一个表，占整个数据库的20-30%。这是因为当数据库创建的时候，没有人关心在5-10年内会发生什么。现在我们必须清理所有的乱局。将来你得自己坐下来做一个项目。1，3，5，10年后的数据库是什么？如何预防问题？您需要重新组织您的数据库体系结构，并使它成为未来的证明。因为这个数据库本身无法进入云端。

...and总有一天你必须去那里，不管你愿不愿意。