用于禁用数据仓库负载的约束和索引的脚本

对于一些你可能没有考虑过的事情：

1. 如果索引以禁用状态开始，您将在脚本完成后重新构建它。
2. 如果索引以页面压缩开始，您将在不压缩的情况下重建它。

对于性能：

1. 由于没有其他进程正在访问该表，因此您可以使用该选项进行重建，ONLINE = OFF以减少锁定，从而可能提高性能。
2. 根据 tempdb 的大小和您的索引，您可以使用该SORT_IN_TEMPDB = ON选项来提高性能。
3. 您正在更新所有表的统计信息，即使该表尚未修改或更改的行数很少。您可以尝试在更新中更加严格，并且只查看自上次更新或完全修改过的统计信息以来修改过的表。
4. 看起来您正在一个会话中运行所有这些查询。您是否考虑过在多个会话之间拆分工作？

在您的情况下，在加载数据之前禁用外键有点令人担忧，因为当您启用它们时，您的脚本没有任何错误处理TRY .. CATCH blocks......

加载数据后，您如何检查数据库的引用完整性？简单地启用外键并不能保证数据库的引用完整性。您至少应该运行DBCC CHECKCONSTRAINTS并确保它运行干净。参考：外键及其状态

我看到的另一点是双重工作 - 重建索引后更新统计数据有什么意义？

请记住，重建索引将更新与该索引关联的列的统计信息。

有一个很大的窗口可以进行加载，所以我不需要担心用户在加载期间访问数据，但我不想影响数据库中其他表中不相关的数据。

我强烈建议您阅读The Data Loading Performance Guide和我的回答中提到的各种技术。

注意假设的指数。如果调优工具因为崩溃并且没有删除它们而留下它们，那么您的脚本将使它们成为真实的，现在您将在单个表上拥有 30 或 40 个索引，并且该表上的性能将充满阻塞和死锁.

SELECT dbid = DB_ID(),
       objectid = object_id,
       indid = index_id
FROM sys.indexes
WHERE is_hypothetical = 1 ;

这是我在整合了来自不同受访者的反馈之后的最终脚本（只是想将它们收集在一起以供将来的读者使用）：

禁用约束和索引

Declare @schema varchar(128) = 'dbo';
Declare @sql nvarchar(max) = '';

-- 1. Indices
-- Select a list of indexes in the schema and generate statements to disable them.
Select @sql = @sql + 'ALTER INDEX ' + QuoteName(idx.name) + ' ON ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' DISABLE;' + CHAR(13)
  From sys.indexes As idx
  Join sys.objects As obj On idx.object_id = obj.object_id
  Where ((obj.type = 'U' And idx.type in (2,6)) -- Non-clustered index/columnstore on a table
    Or obj.type = 'V') -- All indexes on indexed views
    And obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name, idx.name;

Execute sp_executesql @sql;


-- 2. Foreign-key constraints
-- Build a list of foreign keys constraints in the schema and generate statements to disable the constraint checking.
Select @sql = @sql + 'ALTER TABLE ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' NOCHECK CONSTRAINT ' + QuoteName(fk.name) + ';' + CHAR(13)
  From sys.foreign_keys As fk
  Join sys.objects As obj On fk.parent_object_id = obj.object_id  
  Where obj.schema_id = (Select schema_id From sys.schemas Where name = @schema);

Execute sp_executesql @sql;

** 重建索引、启用约束和更新统计信息**

Declare @schema varchar(128) = 'dbo';
Declare @sql nvarchar(max) = '';

-- 1. Indices
-- Build a list of tables in the schema and generate statements to enable the indices on them.
Select @sql = @sql + 'ALTER INDEX ' + QuoteName(idx.name) + ' ON ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + 
    ' REBUILD WITH (' + iif(idx.type = 6, 
                            'MAXDOP = 1', 
                            'FILLFACTOR = 100, DATA_COMPRESSION = ' + IsNull(p.data_compression_desc Collate Latin1_General_CI_AS, 'None') + ', ONLINE = OFF, SORT_IN_TEMPDB = ON') 
                    + ');' + CHAR(13)
  From sys.indexes idx
  Join sys.objects obj ON obj.object_id = idx.object_id
  Left Join sys.partitions As p On idx.object_id = p.object_id And idx.index_id = p.index_id
  Where ((obj.type = 'U' And idx.type in (2,6)) -- Non-clustered index on a table
    Or obj.type = 'V') -- All indexes on indexed views
    And obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
    And idx.is_disabled = 1 -- Don't rebuild indexes that are already online
    And idx.is_hypothetical = 0 -- Don't rebuild hypothetical indexes!
  Order By iif(idx.type = 6, 1, 2), obj.name, idx.name;

Execute sp_executesql @sql;


-- 2. Foreign-key constraints
-- Build a list of foreign keys constraints in the schema and generate statements to enable them with checking.
Select @sql = @sql + 'ALTER TABLE ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' WITH CHECK CHECK CONSTRAINT ' + QuoteName(fk.name) + ';' + CHAR(13)
  From sys.foreign_keys fk
  Join sys.objects obj ON obj.object_id = fk.parent_object_id
  Where obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name, fk.name;

--DBCC CheckConstraints('fForeignKey_Table'); -- Does a soft check of the constraints and returns any values which violate them.
Execute sp_executesql @sql;


-- 3. Statistics
-- Build a list of tables in the schema and generate statements to update the statistics on them.
Select @sql = @sql + 'UPDATE STATISTICS ' + QuoteName(@schema) + '.' + QuoteName(obj.name) + ' WITH COLUMNS;' + CHAR(13)
  From sys.objects obj
  Where obj.type = 'U' -- User defined
    AND obj.schema_id = (Select schema_id From sys.schemas Where name = @schema)
  Order By obj.name;

Execute sp_executesql @sql;