SQL Server 2005 / 2008 - 多个文件组？
我本质上是一名开发人员 - 但时不时地，客户没有一个像样的 DBA 来处理这些问题，所以我被叫来决定......
当它发生时，你的策略/最佳实践是什么涉及处理大小合理的 SQL Server 数据库（任何大于 Northwind 或 AdventureWorks 的数据库） - 您是否使用多个文件组？ 如果有：有多少？ 为什么？
您决定何时放弃“一个文件组适用于所有内容”方法的标准是什么：
- 数据库大小？
- 数据库复杂度？
- 可用性/可靠性要求？
- 还有什么？
如果您使用多个文件组，您使用多少个？ 一份用于数据，一份用于索引，一份用于日志？ 几个（多少）数据？ 您选择的原因是什么 - 为什么您使用确切数量的文件组:-)
I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide....
What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server database (anything larger than Northwind or AdventureWorks) - do you use multiple filegroups? If so: how many? And why?
What are your criteria to decide when to move away from the "one filegroup for everything" approach:
- database size?
- database complexity?
- availability / reliability requirements?
- what else?
If you use multiple file groups, how many do you use? One for data, one for index, one for log? Several (how many) for data? What are your reasons for your choice - why do you use that exact number of filegroups :-)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
经过 Microsoft 培训的最佳实践方法如下：
请记住，MDF 在技术上与硬盘驱动器类似在存储数据时进行分区。 MDF是随机读取的文件，而LDF是顺序读取的文件。 因此，将它们分成单独的驱动器会带来巨大的性能增益，除非运行固态驱动器，在这种情况下增益仍然存在。
The Microsoft trained and best practice methodology is as follows:
Keep in mind that an MDF technically works similarly to a hard drive partition when it comes to storing data. The MDF is a randomly read file, whereas the LDF is a sequentially read file. Therefore splitting them into separate drives causes a huge performance gain, unless running solid state drives, in which case the gain is still there.
在 SQL Server 2008 中拥有多个（至少两个）文件组至少有一个充分的理由：如果您想使用 FILESTREAM 功能，则必须为您的 FILESTREAM 数据拥有一个专用的自定义文件组:-)
马克
There's at least ONE good reason for having multiple (at least two) file groups in SQL Server 2008 : if you want to use the FILESTREAM feature, you have to have a dedicated and custom filegroup for your FILESTREAM data :-)
Marc
维护多个文件组有助于减轻 I/O 负担。 它还为您提供了存储灵活性，您可以轻松备份文件组而不是单个文件，并将它们分成每个文件组的单独磁盘驱动器。
Maintaining multiple filegroups helps you reduce the I/O burden. It also allows you storage flexibility where you can back up a filegroup easily rather than a single file and separate them into an individual disk drive per file group.
一般来说，您应该只有一个主文件组和一个针对该文件组的日志文件。
有时，当您拥有非常静态的数据时，您可以创建一个包含此静态数据的SECOND文件组。 然后，您可以将文件组设置为只读，从而提高性能。 毕竟，这是相当静态的数据。 如果只读行数较少（例如查找表值），那么这是不值得的。 但对于某些内容（例如仍然可以读入的存档内容），这可能是一个不错的选择。
我从 这个得到了这个想法博客文章。
HTH。
Generally you should just have one Primary Filegroup and one log file against that.
Sometimes when you have very static data, you can create a SECOND filegroup that contains this static data. You can then make the filegroup READONLY which improves your performance. After all, this is pretty static data. It's not worth it if you have a low number of readonly rows (eg. lookup table values). But for some stuff (eg. archived content that can still be read in) then this might be a great option.
I got the idea from this blog post.
HTH.
我已经处理过很多数据库，我们唯一一次使用文件组是当磁盘空间不足时，我们必须在另一个轴上创建一个新的文件组。 我确信有一些良好的性能原因导致这不理想，但这就是现实。
I've worked on a good range of DBs, and the only time we've used filegroups was when a disk was running short on space, and we had to create a new file group on another spindle. I'm sure there are good performance reasons why that's not ideal, but that was the reality.
除其他原因外，如果您想对表进行分区，附加文件组是有意义的。 如果有许多竞争性的读取具有不同的表的 where 条件，那么这是有道理的。 您可以配置每个分区以反映这样的位置条件并位于不同的磁盘上，从而将每次读取发送到另一个磁盘，从而实现并行读取并减少冲突。
among other reasons additional filegroups make sense if you want to partition a table. and that makes sense if there are many rivaling reads with dissimilar where-conditions of that table. you can configure each partition to reflect one such where-condition and to be located on a different disk, thereby sending each read to another disk, thus parallel reads and less conflict.