Scripts para desabilitar restrições e índices para carregamento de data warehouse

Para algumas coisas que você pode não ter considerado:

1. Se um índice começar como desativado, você acabará reconstruindo-o após a conclusão do script.
2. Se um índice começar com compactação de página, você o reconstruirá sem compactação.

Para desempenho:

1. Como nenhum outro processo está acessando a tabela, você pode reconstruir com a ONLINE = OFFopção de possivelmente um pequeno aumento de desempenho devido a menos bloqueio.
2. Dependendo do tamanho do tempdb e de seus índices, você pode usar a SORT_IN_TEMPDB = ONopção para aumentar o desempenho.
3. Você está atualizando as estatísticas em todas as tabelas, mesmo que essa tabela não tenha sido modificada ou tenha poucas linhas alteradas. Você pode tentar ser mais restritivo em suas atualizações e olhar apenas para as tabelas que foram modificadas desde a última atualização de estatísticas ou que foram modificadas.
4. Parece que você está executando todas essas consultas em uma única sessão. Você já pensou em dividir o trabalho entre várias sessões?

Desabilitar chaves estrangeiras antes de carregar os dados é um pouco preocupante no seu caso pois quando você está habilitando elas, seu scrip não tem nenhum erro de manipulação.. TRY .. CATCH blocks.

Depois que os dados são carregados, como você verifica a integridade referencial do seu banco de dados? A simples habilitação de chaves estrangeiras não garante a integridade referencial do seu banco de dados. Você deve pelo menos executar DBCC CHECKCONSTRAINTSe garantir que ele funcione limpo. Consulte: Chaves estrangeiras e seus estados

Outro ponto que vejo é o trabalho duplo - Qual é o sentido de atualizar as estatísticas depois de reconstruir os índices?

Lembre-se de que a reconstrução do índice atualizará as estatísticas nas colunas associadas a esse índice .

Há uma grande janela na qual posso fazer o carregamento para não precisar me preocupar com usuários acessando os dados durante o carregamento, mas não quero impactar dados não relacionados em outras tabelas do banco de dados.

Eu sugiro que você leia várias técnicas mencionadas no Guia de Desempenho de Carregamento de Dados e aqui na minha resposta .

Cuidado com índices hipotéticos. Se eles forem deixados por uma ferramenta de ajuste porque ela travou e não os removeu, seu script os tornará reais e agora você terá 30 ou 40 índices em uma única tabela e o desempenho nessa tabela será cheio de bloqueios e impasses .

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

Aqui está o roteiro final que tenho, depois de integrar o feedback de vários entrevistados (só queria reunir tudo para futuros leitores):

Desativar restrições e índices

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;

** Recriar índices, habilitar restrições e atualizar estatísticas **

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;