将SQL Server中的所有非聚集索引移动到另一个文件组

Question

在 SQL Server 2008 中，我想将一个数据库中的 所有 非聚集索引移动到一个次要的文件组中。最简单的方法是什么？

- IamIC

---

我可以问一下你为什么认为你需要这个吗？关于这个有很多谣言。 - gbn

我想将非聚集索引移动到单独的磁盘驱动器上以获得更好的性能。我知道这样做确实会带来好处。我曾与一位顶级的Caché开发人员合作，他向我介绍了这个概念。 - IamIC

@IanC：完成后，很想听听你的反馈，看看是否能够注意到任何性能提升！我自己也一直在考虑这个问题。 - marc_s

@IanC：是的，同意 - 理论上，理论和实践没有区别；然而，在实践中... - marc_s

@IanC: 一些注释：你是否牺牲了大型RAID 10阵列来增加额外的磁盘？使用相同的磁盘控制器（现在跨越2个磁盘共享相同的缓存和带宽）？数据库可以适应内存（远少于磁盘IO）？写入速度由日志文件磁盘等决定。你必须拥有极端的大小和/或容量：我们不会因为我刚才提到的原因而费心每天新增1000万行。。 - gbn

Answer 1

运行这个更新的脚本，以创建名为MoveIndexToFileGroup的存储过程。该过程将某个表上的所有非聚集索引移动到指定的文件组中，并且支持一些其他脚本不支持的INCLUDE列。此外，它不会重建或移动已经位于目标文件组上的索引。创建过程后，像这样调用：

EXEC MoveIndexToFileGroup @DBName = '<your database name>',
                          @SchemaName = '<schema name that defaults to dbo>',
                          @ObjectNameList = '<a table or list of tables>',
                          @IndexName = '<an index or NULL for all of them>',
                          @FileGroupName = '<the target file group>';

要创建一个能够为数据库中每个表运行此操作的脚本，请将查询输出切换为文本，然后运行以下内容：

SELECT 'EXEC MoveIndexToFileGroup '''
    +TABLE_CATALOG+''','''
    +TABLE_SCHEMA+''','''
    +TABLE_NAME+''',NULL,''the target file group'';'
    +char(13)+char(10)
    +'GO'+char(13)+char(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

请参考原始的博客获取更多细节。虽然我并没有编写这个程序，但我已根据博客的回复进行了更新，并确认它在SQL Server 2005和2008上都可以正常工作。

更新内容

1. @psteffek 修改了脚本，以便在SQL Server 2012上运行。我合并了他的更改。
2. 如果你的表使用了IGNORE_DUP_KEY选项，则该过程将失败。目前还没有修复此问题的方法。
3. @srutzky指出该过程不能保证索引的顺序，并提出了如何修复的建议。我已相应地更新了该过程。
4. ojiNY注意到该过程忽略了索引过滤器（为了与SQL 2005兼容）。根据他的建议，我将它们添加了回来。

- dalenewman

Answer 2

脚本它们，更改ON从句，删除它们，重新运行新脚本。实际上没有其他选择。

幸运的是，互联网上有这样的脚本，例如这个，可以为您处理脚本。

- gbn

---

你也可以在新的CREATE INDEX语句中使用WITH DROP_EXISTING子句 - 这样可以省去显式的DROP INDEX步骤。 - marc_s

Answer 3

更新：如果您使用的是MS SQL Server管理器2008R2或更早版本，则手动执行步骤2将需要很长时间。我使用的是SQL Server Manager 2014，所以它可以正常工作（因为它导出删除和创建索引的方式很容易修改）。 我尝试在SQL Server 2014中运行脚本并遇到了一些问题，我懒得检测这些问题，所以我想出了另一种不依赖于您正在运行的SQL Server版本的解决方案。

1. 导出您的索引（包括删除和创建）

2.更新您的脚本，删除与创建表有关的所有内容，保留属于索引的内容。将原始索引替换为新索引（在我的情况下，我用ON [SECONDARY]替换了ON [PRIMARY]）。 []5 3.运行脚本！等待直到完成。 （您可能希望保存脚本以在其他环境中运行）。

- Chinh Phan