SQL Server 2008中的查询性能分区

Question

我有一个场景，其中有大量与项目有关的状态数据。项目的状态每分钟更新一次，并且在不久的将来将有约50,000个项目。因此，在一个月内，将有大约2,232,000,000行数据。在存档旧数据之前，必须在主表中至少保存3个月的数据。

我必须计划实现基于特定项目（其ID）和数据范围（通常为一个月范围）的快速查询-例如，从Table中选择A、B、C，其中ItemID = 3000并且日期介于'2010-10-01'和'2010-10-31 23:59:59.999'之间。

那么我的问题是如何设计分区结构以实现这一点？

目前，我是基于“项目的唯一标识符”（int）mod“分区数”进行分区，以便所有分区均等分配。但这样做的缺点是需要在表上保留一个额外的列作为分区列传递给分区函数，从而将行映射到其分区。所有这些都增加了一些额外的存储开销。此外，每个分区都映射到不同的文件组。

- gsb

---

这是一些负载。在这里阅读有关高容量写入的信息（您每秒有50k行*输入）。我很好奇您将如何解决这个问题：我完全没有处理那种容量/增长速度的经验。 - gbn

你是在设计写入查询效率还是读取查询效率？你有什么样的读取负载？ - Roopesh Shenoy

你能否提供一些关于表中有哪些列以及在查询中返回的列大小（宽度）的更多信息？ - RC_Cleland

Answer 1

分区永远不是为了查询性能而做的。通过分区，性能将总是更差，最好的情况是没有太大的退化，但永远不会有改善。

对于查询性能，任何分区可以做的事情，索引都可以做得更好，这应该是你的答案：适当地建立索引。

分区在IO路径控制情况（在归档/当前卷上分配）或ETL负载中快速切换场景下非常有用。因此，如果您有一个滑动窗口并按日期进行分区，以便可以快速切换掉不再需要保留的数据，则我会理解。

另一个仅限于分区的情况是最后一页插入闩锁争用，就像Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads中所述。

您的分区方案和用例似乎不符合任何受益的情况（可能是最后一种情况，但从描述中不清楚），因此很可能会影响性能。

- Remus Rusanu

---

我将这个分区表解决方案与另一个未分区的表进行了比较，结果在分区解决方案上略微劣于未分区的表（98ms vs 99ms）。 我现在使用了8个分区，接下来我将尝试使用250个分区，分布在2个驱动器中，并观察事情的发展。 - gsb

Poco - 两个驱动器，生产系统中只会有两个驱动器吗？ - RC_Cleland

Answer 2

我不完全同意Remus Rusanu的观点。我认为，如果有逻辑上的理由（与您的使用情况相关），分区可能会提高性能。我的猜测是，您可以仅在itemID上进行分区。另一种选择是也使用日期进行分区，但如果无法预测日期范围是否会跨越给定分区的边界（没有查询一定只包含一个月），那么我会坚持itemId分区。

如果您只需要计算少量项目，则另一种选择是使用覆盖索引：在主要区分字段（itemId）上定义一个INDEX，其中包括您需要计算的字段。

CREATE INDEX idxTest ON itemId INCLUDE quantity;

- iDevlop

Answer 3

应用分区实际上可以有助于查询性能。在您的情况下，您有50K个项目和2G行。例如，您可以创建500个表，每个表名为status_nnn，其中nnn介于001和500之间，并将您的项目状态平均分配到这些表中，其中nnn是项目ID的函数。这样，给定一个项目ID，您可以事先将搜索限制在整个数据的0.2％（约4M行）。

这种方法有很多缺点，因为您可能需要处理动态SQL和其他不愉快的问题，特别是如果您需要从不同的表聚合数据。但是，对于某些查询（如您提到的查询），它肯定会提高性能。

本质上，应用程序分区类似于创建非常宽而平坦的索引，针对非常特定的查询进行优化，而不重复数据。

应用分区的另一个好处是，您可以根据理论（取决于您的用例）将数据分布在不同的数据库甚至不同的服务器上。同样，这非常取决于您的具体要求，但我已经看到并使用了大型数据集（数十亿行），其中应用分区效果非常好。

- Manu