Microsoft Technet 文章建议创建一个辅助文件组作为默认文件组（请参阅下面的参考资料）。辅助文件组应该有许多文件，比如说四个，每个文件都放在不同的磁盘上。作为同事的补充经验法则，文件数应等于 CPU 内核数。
我的理解是，此设置非常适合机械旋转磁盘驱动器，因为旋转磁盘驱动器比固态驱动器慢得多，因此可以通过从多个磁头流式传输数据来提高性能。这种理解是否正确？
如果是，那么我的问题是基于成本的优化器是否考虑了较新的固态驱动器？切换到新的固态驱动器后，旋转硬盘驱动器的性能瓶颈似乎消失了。我们的 IT 运营团队告诉我，虽然我目前分配了一个虚拟驱动器，但数据实际上存储在一个 ISCSI SAN 上，它有多个固态驱动器。
这个问题旨在尝试回答这种规模的大型数据库的最佳设置是什么：
- 我应该有一个只有一个文件的默认辅助文件组吗？
- 我是否应该有一个默认的辅助文件组，其中包含的文件数等于 CPU 上的内核数？
- 使用固态驱动器时对数据库表进行分区是否可以提高性能？
我目前正在进行的项目需要一个扩展的数据库，该数据库的大小将达到几 TB，用于存储大量日志数据。一个一周的样本大约有 1.5 亿条记录，我们需要存储滚动的 3 年日志。因此，我现在正在查看长时间运行的查询以查找数据。我已经将索引调整到几乎所有工作都归因于非聚集索引查找的程度；优化器不建议添加缺失的索引。
笔记
Microsoft SQL Server 上的许可目前是按 CPU 内核进行的。因此，在这个问题上投入更多核心是敏感的，特别是如果这不会提高性能。
此外，我目前正在 SQL Server 2014 上进行开发，但将迁移到 SQL Server 2017 以进行开发和生产。
更新 1
该项目将每晚加载日志，我预计很少（可能没有）更新或删除，因为日志根本不会改变 - 所以它们不会被重新加载。出于分析目的，将读取其他所有内容。
系统表的 PRIMARY 文件组，其中 SECONDARY 默认文件组用于其他所有内容。这样做的原因由这个问题底部引用的链接解释。
将为表分区创建单独的文件组。数据库中还有其他足够小的表，它们将驻留在 SECONDARY 文件组中 - 我只对两个表进行分区，其中一个超过 1 亿条记录（按 IDENTITY 行号分区），另一个将进入数十亿条记录（按时间 [每月] 划分）。
我计划在 3 年内按月进行分区。因此，将有 36 个分区。我会为每年创建文件组，然后将12个文件放入相应的年度文件组中。分区策略是为了减少读取时间，因为出于分析目的将进行大量数据扫描。年度文件组策略严格来说是为了便于 DBA 维护，他们可以通过删除单个文件组来删除一年的数据。
参考：
基于成本的优化器不会（当前）根据您的硬件更改其估计的 IO 成本。
您说这个问题旨在回答哪种文件组和分区策略最适合您的环境。在那种情况下，谁在乎优化器做了什么？问题是什么在您的环境中运行良好，真正的答案是对其进行测试。
根据经验，添加文件组以改进维护或可管理性，添加文件以提高性能。当然，并非所有工作负载或场景都会受益于额外的文件或文件组。举几个例子：
考虑一个有许多 CPU 的 Windows 服务器，只有一个数据文件用于用户数据库。由于只有一个文件而导致的逻辑争用可能会导致工作负载成为瓶颈。例如，某些类型的更改数据的查询都需要同时修改同一个 PFS 页面。如果工作负载因内存中的逻辑争用而成为瓶颈，则可以通过添加更多文件来提高性能，即使这些文件与以前位于同一 LUN 上也是如此。拥有额外的文件意味着有更多“活动的”PFS 页面，从而减少争用。有关更多详细信息，请参阅本文。
您可以将表或分区分配给特定的文件组。您不能对文件执行此操作。因此，如果您有想要清除的旧数据并且想要回收所有存储空间，那么文件组可能是您的不错选择。将所有用户数据放在辅助文件组中有一些维护方面的好处，一些人对此深信不疑。重要的一点是文件组应该被认为有助于管理而不是性能。