Отключите все некластерные индексы
Я выбираю много некластерных индексов из своей базы данных со следующим:
SELECT sys.objects.name tableName,
sys.indexes.name indexName
FROM sys.indexes
JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id
WHERE sys.indexes.type_desc = 'NONCLUSTERED'
AND sys.objects.type_desc = 'USER_TABLE'
Я хотел бы выполнить следующее по каждому из результатов:
ALTER INDEX indexName ON tableName DISABLE
Как я пошел бы о выполнении этого? Существует ли лучший путь?
Править
Я делаю, это в целях усечения таблиц, затем восстанавливая с "ИЗМЕНЯЕТ ИНДЕКС bla, таблица ON ВОССТАНАВЛИВАЮТ". Это должно быть автоматизировано, таким образом отбрасывание и восстанавливание были бы несколько более высоким техническим обслуживанием, которого я скорее избегу. Действительно ли это - плохой план? Мне нужно средство пустеющих таблиц с минимумом наверху.
3 ответа
Вы можете встроить запросы в оператор select, например:
DECLARE @sql AS VARCHAR(MAX)='';
SELECT @sql = @sql +
'ALTER INDEX ' + sys.indexes.name + ' ON ' + sys.objects.name + ' DISABLE;' +CHAR(13)+CHAR(10)
FROM
sys.indexes
JOIN
sys.objects
ON sys.indexes.object_id = sys.objects.object_id
WHERE sys.indexes.type_desc = 'NONCLUSTERED'
AND sys.objects.type_desc = 'USER_TABLE';
EXEC(@sql);
Символы 13 и 10 - это перевод строки / возврат каретки, поэтому вы можно проверить вывод, заменив EXEC на PRINT , и он будет более читабельным.
Создайте табличную переменную с индексами и именами таблиц. Используйте цикл, чтобы перебрать их и выполнить динамический оператор SQL для каждого из них.
declare @Indexes table
(
Num int identity(1,1) primary key clustered,
TableName nvarchar(255),
IndexName nvarchar(255)
)
INSERT INTO @Indexes
(
TableName,
IndexName
)
SELECT sys.objects.name tableName,
sys.indexes.name indexName
FROM sys.indexes
JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id
WHERE sys.indexes.type_desc = 'NONCLUSTERED'
AND sys.objects.type_desc = 'USER_TABLE'
DECLARE @Max INT
SET @Max = @@ROWCOUNT
SELECT @Max as 'max'
SELECT * FROM @Indexes
DECLARE @I INT
SET @I = 1
DECLARE @TblName NVARCHAR(255), @IdxName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)
WHILE @I <= @Max
BEGIN
SELECT @TblName = TableName, @IdxName = IndexName FROM @Indexes WHERE Num = @I
SELECT @SQL = N'ALTER INDEX ' + @IdxName + N' ON ' + @TblName + ' DISABLE;'
EXEC sp_sqlexec @SQL
SET @I = @I + 1
END
OTOH может быть лучше DROP, чем DISABLE (или это мелкая синтаксическая разница между Oracle и MS SQL? :-) Причина, о которой я упоминаю, в том, что я помню таблицы, которые были повторно заполнены и неожиданно денормализованы дважды в день, и мы DROP-ing все индексы, чтобы заставить DB перестроить как индексы, так и планы выполнения sproc после того, как мы загрузили новую дату и перестроили все индексы.
Конечно, для этого у нас был отдельный сценарий, поскольку после их удаления индексы больше не находятся в системных таблицах.