quitar - Deshabilite y vuelva a habilitar todos los índices en una base de datos de SQL Server

enable index sql server

Question

Estoy ejecutando un DTS para realizar tareas en mi base de datos, en el cual al principio necesito desactivar todos los índices en la base de datos y volver a habilitarlos cuando el DTS finalice su trabajo.

¿Hay alguna manera de que pueda deshabilitar todos los índices en toda la base de datos y luego volver a habilitarlos a todos?

Sé cómo deshabilitar / habilitar uno por uno, alguien puede ayudarme con la forma de deshabilitar / habilitar todo a la vez como un paso en el DTS.

Answer 1

Aquí hay una secuencia de comandos que generará declaraciones ALTER para todos los índices no agrupados en su base de datos. Puede modificar esto fácilmente para generar scripts REBUILD y scripts para índices agrupados

select ''ALTER INDEX '' + I.name + '' ON '' + T.name + '' DISABLE''
from sys.indexes I
inner join sys.tables T on I.object_id = T.object_id
where I.type_desc = ''NONCLUSTERED''
and I.name is not null

Answer 2

Deshabilitar índices es una buena idea cuando se trata de cargar grandes cantidades de datos, pero ... el gran problema son los índices agrupados. Si deshabilita un índice agrupado, ha deshabilitado toda la tabla.

Varias opciones se sugieren, y ninguna de ellas es simple.

1) Recorra las vistas del sistema (sys.indexes), extraiga la tabla y el nombre del índice, genere y ejecute SQL dinámico para deshabilitar el índice. Tenga una rutina de "deshacer" para volver a habilitarlos. (Tenga cuidado, ¿fue un índice único o una restricción única?) Esto, por desgracia, solo funciona si no utiliza índices agrupados. Buena suerte con eso.

2) En cuanto a 1, pero omita cualquier índice agrupado. Cuando cargue datos, asegúrese de que se carguen en orden secuencial (índice agrupado), de lo contrario tendrá tiempos de carga deficientes y tablas fragmentadas. (Si los proveedores de datos son como los míos, buena suerte con eso también).

3) Cree tablas en su base de datos que contengan definiciones de los índices en sus tablas de "carga". Cree una rutina que los recorra y descarte todos los índices (los índices agrupados duran). Esto será rápido si trunca las tablas primero. Cargue sus datos, luego realice un ciclo y vuelva a crear los índices desde cero (agrupados primero). Use la partición de tablas para hacer menos horrible en el resto del sistema (por ejemplo, haga todo lo anterior en las tablas de "carga", luego use el cambio de partición para mover los datos cargados a sus tablas "en vivo"). No me tomó poco tiempo construir un sistema así, pero puede y funcionará.

Answer 3

Esto funciona para SQL Server 2008 y versiones más recientes. Permite diferentes esquemas y también nombres que tienen espacios, guiones y otros caracteres especiales que deben ser citados. ¿Cuál es el uso de los corchetes [] en sentencias de sql?

Estos scripts generarán el código en la pestaña de resultados. Debes copiar / pegar en la pestaña de consulta y ejecutarlas.

Deshabilitar script

SELECT ''ALTER INDEX '' + QUOTENAME(I.name) + '' ON '' + QUOTENAME(SCHEMA_NAME(T.schema_id))+''.''+ QUOTENAME(T.name) + '' DISABLE''
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = ''NONCLUSTERED''
AND I.name IS NOT NULL
AND I.is_disabled = 0

Habilitar script (Reconstruir)

SELECT ''ALTER INDEX '' + QUOTENAME(I.name) + '' ON '' + QUOTENAME(SCHEMA_NAME(T.schema_id))+''.''+ QUOTENAME(T.name) + '' REBUILD''
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = ''NONCLUSTERED''
AND I.name IS NOT NULL
AND I.is_disabled = 1

Esto se basa en otra respuesta aquí.

Answer 4

Para habilitar un índice, debes reconstruirlo. Este script reconstruirá todos los índices deshabilitados.

DECLARE @my_sql2 NVARCHAR(200);
DECLARE cur_rebuild CURSOR FOR
SELECT ''ALTER INDEX '' + i.name + '' ON '' + t.name + '' REBUILD'' FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id WHERE i.is_disabled = 1 ORDER BY t.name, i.name;
OPEN cur_rebuild;
FETCH NEXT FROM cur_rebuild INTO @my_sql2;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @my_sql2;
FETCH NEXT FROM cur_rebuild INTO @my_sql2;
END;
CLOSE cur_rebuild;
DEALLOCATE cur_rebuild;
GO

Answer 5

Podemos usar el siguiente script para deshabilitar los índices

ALTER INDEX ALL ON [TableName]
DISABLE;

Haga su inserción masiva en la tabla y luego ejecute debajo del script.

ALTER INDEX ALL ON [TableName]
REBUILD;

Answer 6

Tendrá que ejecutar un script que seleccione el metadate para la tabla y el índice. Entonces puedes hacer un

ALTER INDEX indexname ON tablename DISABLE; 

Más tarde puede ejecutar un script similar para reconstruir:

ALTER INDEX indexname ON tablename REBUILD; 

Puede hacer esto de uno en uno, o recopilarlos en una variable NVARCHAR (MAX) y ejecutarlos como un solo lote. Puede ver el código de ejemplo en esta pregunta anterior:

Deshabilitar todos los índices no agrupados