Отключить и повторно включить все индексы в базе данных SQL Server

Я запускаю DTS для выполнения задач в моей базе данных, в которых сначала мне нужно отключить индексы all в базе данных и снова включить их, когда DTS завершит свою работу.

Есть ли способ отключить все индексы во всей базе данных, а затем снова включить их?

Я знаю, как отключить/включить по одному, может ли кто-нибудь помочь мне с возможностью отключить/включить все сразу как шаг в DTS.

Ответы

Ответ 1

Вот скрипт, который будет выводить операторы ALTER для всех некластеризованных индексов в вашей базе данных. Вы можете легко изменить это для вывода сценариев REBUILD и сценариев для кластерных индексов.

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

Ответ 2

Это работает для SQL Server 2008 и более новых версий. Он позволяет использовать разные схемы, а также имена, в которых есть пробелы, тире и другие специальные символы, которые должны быть указаны. Что такое квадратные скобки [] в операциях sql?

Эти скрипты выводят код на вкладку результатов. Вы должны скопировать/вставить вкладку запроса и выполнить их.

Отключить 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

Включить script (перестроить)

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

Это основано на другом ответе здесь.

Ответ 3

Мы можем использовать ниже скрипт для отключения индексов

ALTER INDEX ALL ON [TableName]
DISABLE;

Сделайте объемную вставку в таблицу, а не ниже script.

ALTER INDEX ALL ON [TableName]
REBUILD;

Ответ 4

Чтобы включить индекс, вы должны его перестроить. Этот script будет восстанавливать все отключенные индексы.

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

Ответ 5

Вам нужно будет запустить script, который будет выбирать метаданные для таблицы и индекса. Затем вы можете сделать следующее:

ALTER INDEX indexname ON tablename DISABLE;

Позже вы можете запустить аналогичный script для восстановления:

ALTER INDEX indexname ON tablename REBUILD;

Вы можете делать это за один раз или собирать их в переменную NVARCHAR (MAX) и выполнять их как одну партию. Вы можете увидеть пример кода в этом более раннем вопросе:

Отключить все некластеризованные индексы

Ответ 6

Отключение индексов - хорошая идея, когда дело доходит до загрузки большого количества данных, но... большая проблема - это кластеризованные индексы. Если вы отключите кластеризованный индекс, вы отключили всю таблицу.

Несколько вариантов предлагают сами, и ни один из них не прост.

1) Прокрутите системные представления (sys.indexes), извлеките имя таблицы и индекса, создайте и выполните динамический SQL, чтобы отключить индекс. У вас есть "отменить", чтобы повторно включить их. (Будьте осторожны - был ли он уникальным индексом или уникальным ограничением?) Это, увы, работает только в том случае, если вы не используете кластерные индексы. Удачи вам в этом.

2) Что касается 1, но пропустите любые кластерные индексы. Когда вы загружаете данные, убедитесь, что они загружаются в (кластерный индекс) последовательным порядком, иначе у вас будут плохие времена загрузки и фрагментированные таблицы. (Если ваши поставщики данных похожи на мои, удачи с этим тоже.)

3) Создайте таблицы в своей базе данных, содержащие определения индексов в таблицах загрузки. Создайте подпрограмму, проходящую через них, и отбрасывает все индексы (последние кластеризованные индексы). Это будет быстро, если вы сначала обрезаете таблицы. Загрузите свои данные, затем выполните цикл и заново создайте индексы с нуля (сначала кластеризованные). Используйте разбиение на таблицы, чтобы сделать менее страшным в остальной части системы (например, выполните все вышеприведенные действия в таблицах "загрузка", затем используйте переключение разделов, чтобы переместить загруженные данные в ваши "живые" таблицы). Мне потребовалось мало времени для создания такой системы, но она может и будет работать.

Ответ 7

Перед отключением индекса мы не должны быть ниже:

  • Отключение некластеризованного индекса запрещает пользователю доступ к индексу.
  • Отключение кластеризованных индексов в таблице не удаляет данные, но предотвращает доступ пользователей к базовым табличным данным.
  • Отключение некластеризованного или кластерного индекса в представлении физически удаляет данные индекса.
  • Включение индекса означает, что вам нужно его перестроить. Нет ключевого слова, называемого разрешающим.

---- Отключить все индексы ALTER INDEX ALL ON [Таблица_Name] DISABLE ИДТИ ---- Включить весь индекс ALTER INDEX ALL ON [Table_Name] REBUILD GO

Подробнее см. "Как отключить - Включить индексы в SQL Server"

Ответ 8

Используйте этот скрипт, чтобы отключить все индексы

-- Disable All Indices
DECLARE @Script NVARCHAR(MAX)
DECLARE curIndices CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT 'ALTER INDEX ' + QUOTENAME(indices.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(tableNames.schema_id))+'.'+ QUOTENAME(tableNames.name) + ' DISABLE'
    FROM
        sys.indexes indices  INNER JOIN 
        sys.tables tableNames ON indices.object_id = tableNames.object_id
    WHERE 
        indices.type_desc = 'NONCLUSTERED' AND 
        indices.name IS NOT NULL AND
        indices.is_disabled = 0;
OPEN curIndices
FETCH NEXT FROM curIndices INTO @Script
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Script
    EXECUTE sp_executesql @Script 

    FETCH NEXT FROM curIndices INTO @Script
END
CLOSE curIndices
DEALLOCATE curIndices

Используйте этот скрипт для перестройки (включения) всех индексов

-- Rebuild All Indices
DECLARE @Script NVARCHAR(MAX)
DECLARE curIndices CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT 'ALTER INDEX ' + QUOTENAME(indices.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(tableNames.schema_id))+'.'+ QUOTENAME(tableNames.name) + ' REBUILD'
    FROM
        sys.indexes indices  INNER JOIN 
        sys.tables tableNames ON indices.object_id = tableNames.object_id
    WHERE 
        indices.type_desc = 'NONCLUSTERED' AND 
        indices.name IS NOT NULL AND
        indices.is_disabled = 1;
OPEN curIndices
FETCH NEXT FROM curIndices INTO @Script
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Script
    EXECUTE sp_executesql @Script 

    FETCH NEXT FROM curIndices INTO @Script
END
CLOSE curIndices
DEALLOCATE curIndices