DECLARE @SQL NVARCHAR(MAX)
DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT '
PRINT ''Перестройка [' + i.name + N'] начато '' + Cast(GETDATE() as varchar);
ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE WHEN s.avg_fragmentation_in_percent > 30
THEN 'REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)
PRINT ''Перестройка [' + i.name + N'] завершено '' + Cast(GETDATE() as varchar) '
ELSE 'REORGANIZE'
END + ';'
FROM (
SELECT
s.[object_id]
, s.index_id
, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID('ax_molniya'), OBJECT_ID('dbo.INVENTTRANS'), NULL, NULL, 'LIMITED') s
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 5
GROUP BY s.[object_id], s.index_id
) s
JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]
OPEN cur
FETCH NEXT FROM cur INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
print (@SQL);
FETCH NEXT FROM cur INTO @SQL
END
CLOSE cur
DEALLOCATE cur
Что значит строка (FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON) :
[stextbox id=”info” caption=”FILLFACTOR =”]Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0.[/stextbox]
[stextbox id=”info” caption=”PAD_INDEX “]Определяет разреженность индекса. Значение по умолчанию — OFF.
ON
Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан и одновременно PAD_INDEX установлен в состояние ON, то используется значение коэффициента заполнения, хранимое в таблице sys.indexes.
OFF или fillfactor не указан
Страницы промежуточного уровня заполняются почти полностью. При этом остается достаточно места по крайней мере для одной строки максимального размера, которого может достигать индекс, в зависимости от набора ключей в промежуточных страницах.
Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL). [/stextbox]
[stextbox id=”info” caption=”STATISTICS_NORECOMPUTE “]Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.
ON
Устаревшие статистики не пересчитываются автоматически.
OFF
Автоматическое обновление статистических данных включено.
Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE. [/stextbox]
[stextbox id=”info” caption=” ALLOW_ROW_LOCKS “]Когда присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, уровне страниц и уровне таблиц. Компонент Database Engine выберет соответствующую блокировку и может повысить уровень блокировки с уровня строки или таблицы до уровня страницы.
Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц. Дополнительные сведения о настройке гранулярности блокировки индекса см. в разделе Настройка блокировки индекса. [/stextbox]
[stextbox id=”info” caption=”ALLOW_PAGE_LOCKS”]Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.
ON
Блокировки страниц допустимы при доступе к индексу. Необходимость в блокировке страниц определяет компонент Database Engine.
OFF
Блокировки страниц не используются.
ПримечаниеПримечание
Индекс не может быть реорганизован, если ALLOW_PAGE_LOCKS установлен в состояние OFF.[/stextbox]
[stextbox id=”info” caption=”SORT_IN_TEMPDB”]Указывает, следует ли сохранять результаты сортировки в базе данных tempdb. Значение по умолчанию — OFF.
ON
Промежуточные результаты сортировки, которые используются при индексировании, хранятся в базе данных tempdb. Это может сократить время, требуемое для создания индекса, если база данных tempdb размещена на иных дисках, нежели пользовательская база данных. Однако это увеличивает использование места на диске, которое используется при индексировании.
OFF
Промежуточные результаты сортировки хранятся в той же базе данных, где и индекс.
Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается. [/stextbox]
[stextbox id=”info” caption=”ONLINE “]Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
Для XML-индекса или пространственного индекса поддерживается только значение ONLINE = OFF; при ONLINE = ON возникает ошибка.
ПримечаниеПримечание
Операции с индексами в сети доступны только в выпусках SQL Server Enterprise, Developer и Evaluation.
ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это позволяет продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то по завершении операции на короткое время создается блокировка типа S (совмещаемая) для источника. Блокировка типа SCH-M (изменения схемы) запрашивается, если кластеризованный индекс создается или удаляется в режиме в сети либо, происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.
OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Операция с индексами в режиме «вне сети», которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс либо перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (Sch-M) для этой таблицы. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT. [/stextbox]
Similar Posts:
- Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)
- Как сделать бэкап множества баз mssql на сетевое хранилище с созданием папок под бэкап. И после удалить эти базы.
- Как узнать фрагментацию индексов во всех таблицах в базе MSSQL
- Как обновить статистику и очистить кеш планов.
- Сжать лог у всех баз на сервере