用於禁用數據倉庫負載的約束和索引的腳本

我將數據載入到數據倉庫中。

目前，為了方便和速度，我有一個腳本用於在載入數據之前刪除外鍵約束和索引。有一個很大的視窗可以進行載入，所以我不需要擔心使用者在載入期間訪問數據，但我不想影響數據庫中其他表中不相關的數據。

我已經在這里和其他地方進行了一些研究以提出這個腳本，但我想知道是否有一些我可能會忽略的事情可能會導致性能次優或者我可能會遺漏一些重要的東西（我不知道. ..計算列或其他東西？）或者我做事的順序錯誤等等。

任何建議都可以使這個強大和高性能。

禁用約束和索引

編輯：我刪除了WHILE評論者幫助我意識到的循環是多餘的。

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

-- 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 nvarchar(128) = 'dbo';
Declare @sql nvarchar(max) = N'';

-- 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' + iif(idx.type = 6, ' WITH (MAXDOP = 1);', ' WITH (FILLFACTOR = 100);') + CHAR(13)
 From sys.indexes idx
 Join sys.objects obj ON obj.object_id = idx.object_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;

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;

對於一些你可能沒有考慮過的事情：

1. 如果索引以禁用狀態開始，您將在腳本完成後重新建構它。
2. 如果索引以頁面壓縮開始，您將在不壓縮的情況下重建它。

對於性能：

1. 由於沒有其他程序正在訪問該表，因此您可以使用該選項進行重建，ONLINE = OFF以減少鎖定，從而可能提高性能。
2. 根據 tempdb 的大小和您的索引，您可以使用該SORT_IN_TEMPDB = ON選項來提高性能。
3. 您正在更新所有表的統計資訊，即使該表尚未修改或更改的行數很少。您可以嘗試在更新中更加嚴格，並且只查看自上次更新或完全修改過的統計資訊以來修改過的表。
4. 看起來您正在一個會話中執行所有這些查詢。您是否考慮過在多個會話之間拆分工作？

引用自：https://dba.stackexchange.com/questions/154766