在执行ISCSI SAN固态驱动器时，对基于成本的优化器(CBO)有什么影响？

Question

Microsoft Technet的一篇文章建议创建一个辅助文件组作为默认文件组(请参阅下面的参考)。辅助文件组应该有多个文件，比如四个文件，每个文件放在单独的磁盘上。作为同事的经验法则，文件的数量应该等于CPU核心的数量。

我的理解是，这种设置是理想的机械旋转磁盘驱动器，在那里有一个性能提高流数据从多个头部，因为旋转磁盘驱动器要比固态驱动器慢得多。这种理解是正确的吗？

如果是，那么我的问题是，基于成本的优化器是否考虑到较新的固态驱动器？当切换到新的固态驱动器时，似乎旋转硬盘驱动器的性能瓶颈就消失了。我们的IT运营小组告诉我，虽然我目前被分配了一个虚拟驱动器，数据实际上存储在一个ISCSI SAN上，它有多个固态驱动器。

这个问题的目的是试图回答这样规模的大型数据库的最优设置：

1. 我是否应该有一个默认的辅助文件组，只有一个文件？
2. 我是否应该拥有一个默认的辅助文件组，其中包含的文件数量等于CPU上的核心数？
3. 在使用固态驱动器时，是否通过对数据库表进行分区来提高性能？

我正在进行的当前项目需要一个可缩放的数据库，其大小为几兆字节，可以存储大量的日志数据。一个星期的样本大约有1.5亿个记录，我们需要存储一个滚动的3年的日志。因此，我现在查看长期运行的查询来查找数据。我已经对索引进行了调优，几乎所有的工作都归因于非聚集索引查找；优化器不建议添加缺少的索引。

Note

Microsoft SQL Server上的许可目前是由CPU内核执行的。因此，在这个问题上抛出更多的核心是很敏感的，特别是如果这不会提高性能的话。

此外，我目前正在开发Server 2014，但将迁移到Server 2017进行开发和生产。

更新1

该项目将每晚加载日志，我希望很少(可能没有)更新或删除，因为日志根本不会改变，所以不会重新加载。为了分析的目的，其他的东西都会被读出来。

系统表的主文件组，其中次要默认文件组用于其他所有内容。这样做的原因是由这个问题底部引用的链接来解释的。

将为表分区创建单独的文件组。数据库中还有足够小的其他表，它们将驻留在辅助文件组中--我只划分两个表，其中一个超过1亿条记录(按标识行号进行分区)，另一个将进入数十亿条记录(由time 每月分区)。

我计划在三年内按月划分。因此，将有36个分区。我将为每年创建文件组，然后将12个文件放入相应的年度文件组中。分区策略是为了减少读取时间，因为为了分析目的，需要进行大量的数据扫描。年度文件组策略严格来说是为了便于DBA维护，在DBA中，它们可以通过删除单个文件组来删除一年的数据。

参考：

设置默认文件组的服务器最佳实践

Answer 1

基于成本的优化器不会(目前)根据硬件更改其估计的IO成本。

您说这个问题是针对回答什么文件组和分区策略最适合您的环境。在这种情况下，谁在乎优化器做什么呢？问题是什么在你的环境中有效，真正的答案是测试它。

Answer 2

根据经验，添加文件组以提高维护或可管理性，并添加文件以提高性能。当然，并非所有的工作负载或方案都将受益于其他文件或文件组。举几个例子：

1. 考虑一个windows服务器，它包含多个CPU和用户数据库的一个数据文件。只有一个数据文件意味着我只有一个LUN。可以创建一个需要比单个LUN所能提供的更多I/O的工作负载。如果工作负载受到I/O的阻碍，那么可以通过向数据库中添加更多的文件来提高性能，每个文件都位于自己的LUN上。您可以通过查看等待数据来判断您是否处于这种情况。
2. 考虑一个windows服务器，它包含多个CPU和用户数据库的一个数据文件。可以创建一个工作负载，该工作负载由只有一个文件引起的逻辑争用所造成的瓶颈。例如，某些类型的查询都需要同时修改相同的PFS页面。如果内存中的逻辑争用阻碍了工作负载，那么可以通过添加更多的文件来提高性能，即使这些文件与前面的LUN相同。拥有更多的文件意味着会有更多的“活动”PFS页面来减少争用。有关更多详细信息，请参阅这篇文章。
3. 可以将表或分区分配给特定的文件组。你不能对文件这么做。因此，如果您有要清除的旧数据，并且希望回收所有存储，那么文件组可能是一个很好的选择。将所有用户数据放在辅助文件组中有一些维护方面的好处，这是一些人发誓的。重要的一点是，文件组应该考虑帮助管理而不是绩效。