IndexOptimize — это наверное самая необходима процедура, так как позволят выполнять регламентное обслуживание баз данных с гибкими параметрами от команды Ola Hallengren. Я использую  ее на рабочем сервере в качестве небольшого оптимизатора — реорганизации и пересчета статистики в дневные час, когда нагрузка не высокая и можно подготовиться к вечернему спринту. Чем отличается rebuld от reindex можно прочитать на сайте Microsoft

Параметры

Databases

Выбор баз. В качестве аргументов, отвечающих за выбор объектов, можно передать SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES и AVAILABILITY_GROUP_DATABASES, если поддерживается. Знак (-) используется для исключения базы, а знак процента (%) выполняет роль символа подстановки. Все используемые параметры могут быть разделены занятой (,)

FragmentationLow

Параметры указывают операции с индексом, имеющим низкий уровень фрагментации.

Ребилд  или реорганизация индекса не всегда возможны. Поэтому вы можете указать несколько операций обслуживания индекса. Эти операции приоритетны слева направо: если первая операция поддерживается для индекса, то эта операция используется; если первая операция не поддерживается, то используется вторая операция (если поддерживается) и т. д. Если ни одна из указанных операций не поддерживается для индекса, то этот индекс не поддерживается.

Процедура IndexOptimize использует команду SQL Server ALTER INDEX: REBUILD WITH (ONLINE = ON) ребилд индекса online, REBUILD WITH (ONLINE = OFF) ребилд индекса offline, и REORGANIZE реорганизация индекса.

FragmentationMedium

Параметры указывают операции с индексом, имеющим средний уровень фрагментации. Данный параметр имеет схожие переменные с FragmentationLow

Ребилд  или реорганизация индекса не всегда возможны. Поэтому вы можете указать несколько операций обслуживания индекса. Эти операции приоритетны слева направо: если первая операция поддерживается для индекса, то эта операция используется; если первая операция не поддерживается, то используется вторая операция (если поддерживается) и т. д. Если ни одна из указанных операций не поддерживается для индекса, то этот индекс не поддерживается.

Процедура IndexOptimize использует команду SQL Server ALTER INDEX: REBUILD WITH (ONLINE = ON) перестроение индекса online, REBUILD WITH (ONLINE = OFF) перестроение индекса offline и REORGANIZE реорганизация индекса.

FragmentationHigh

Параметры указывают операции с индексом, имеющим высокий уровень фрагментации. Данный параметр имеет схожие переменные с FragmentationLow и FragmentationMedium

Ребилд  или реорганизация индекса не всегда возможны. Поэтому вы можете указать несколько операций обслуживания индекса. Эти операции приоритетны слева направо: если первая операция поддерживается для индекса, то эта операция используется; если первая операция не поддерживается, то используется вторая операция (если поддерживается) и т. д. Если ни одна из указанных операций не поддерживается для индекса, то этот индекс не поддерживается.

Процедура IndexOptimize использует команду SQL Server ALTER INDEX: REBUILD WITH (ONLINE = ON) перестроение индекса online, REBUILD WITH (ONLINE = OFF) перестроение индекса offline и REORGANIZE реорганизация индекса.

FragmentationLevel1

Установите нижний предел в процентах для средней фрагментации. Значение по умолчанию — 5 процентов. Это основано на рекомендации Microsoft в электронной документации — Books Online. IndexOptimize проверяет avg_fragmentation_in_percent в sys.dm_db_index_physical_stats, чтобы определить фрагментацию.

FragmentationLevel2

Установите нижний предел в процентах для высокой фрагментации. Значение по умолчанию — 30 процентов. Это основано на рекомендации Microsoft в электронной документации — Books Online. IndexOptimize проверяет avg_fragmentation_in_percent в sys.dm_db_index_physical_stats, чтобы определить фрагментацию.

PageCountLevel

Задайте размер, на страницах; индексы с меньшим количеством страниц исключаются из обслуживания. По умолчанию используется 1000 страниц. Это основано на рекомендации Microsoft. IndexOptimize проверяет page_count в sys.dm_db_index_physical_stats, чтобы определить размер индекса.

SortInTempdb

Использовать tempdb для операций сортировки при восстановлении индексов.

Переменная SortInTempdb в процедуре IndexOptimize использует команду SQL Server ALTER INDEX. Это очень интересный параметр, так как позволят задействовать быстрые дисковые массивы, выделенные под tempdb. Если файловые группы настроены верно для tempdb, то не стоит волноваться о их переполнении или замедлении работы сервера. Тут есть пару хитростей, о которых я постараюсь рассказать по завершении оформления статьи.

MaxDOP

Количество ядер процессора выделенных для проверки базы, файловой группы или таблицы. Если данный параметр не задан, будут использоваться максимальное значение степени параллелизма. Параметр MaxDOP использует команду SQL Server ALTER INDEX. Иногда просто необходимо ограничивать количество потоков, которое использует SQL Server. Такое бывает, если регламент выполняется в рабочее время и необходимо обеспечить незначительное снижение производительности.

FillFactor

Укажите в процентах, насколько полные страницы должны быть сделаны при восстановлении индексов. Если процент не указан, используется коэффициент заполнения в sys.indexes. Параметр FillFactor использует команду SQL Server ALTER INDEX. Данный параметр бывает полезен если необходимо снизить коэффициент авто расширения. Т.е. место в таблице будет резервироваться заранее.

PadIndex

Применить процент свободного места, который коэффициент заполнения указывает на страницы промежуточного уровня индекса.

Параметр FillFactor использует команду SQL Server ALTER INDEX.

LOBCompaction

Сжимать страницы, содержащие столбцы больших объектов (LOB) при реорганизации индексов.

Параметр LOBCompaction использует команду SQL Server ALTER INDEX.

UpdateStatistics

Обновление статистики. Важно помнить, что при реорганизации (rebuild), выполнять пересчет статистики нет необходимости! А вот при реиндексации это делать необходимо.

Процедура IndexOptimize использует команду SQL Server UPDATE STATISTICS.

OnlyModifiedStatistics

Обновлять статистику только в том случае, если какие-либо строки были изменены с момента последнего обновления статистики

IndexOptimize проверяет update_counter в sys.dm_db_stats_properties в SQL Server 2008 R2, начиная с Service Pack 2 и SQL Server 2012, начиная с Service Pack 1, чтобы определить, были ли изменены какие-либо строки с момента последнего обновления статистики. В более ранних версиях он проверяет rowmodctr в sys.sysindexes.

StatisticsSample

Укажите в процентах, сколько таблицы собрано при обновлении статистики. Значение 100 эквивалентно полному сканированию. Если значение не указано, SQL Server автоматически вычисляет требуемый образец.

Параметр StatisticsSample использует параметры SAMPLE и FULLSCAN команды SQL Server UPDATE STATISTICS.

StatisticsResample

Обновите статистику с помощью последнего образца.

Параметр StatisticsResample использует параметр RESAMPLE команды SQL Server UPDATE STATISTICS. Вы не можете комбинировать параметры StatisticsSample и StatisticsResample

PartitionLevel

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

MSShippedObjects

Ведение индексов и статистики по объектам, созданным внутренними компонентами SQL Server

IndexOptimize проверяет is_ms_shipped в sys.objects, чтобы определить, был ли объект создан внутренним компонентом SQL Server.

Indexes

Позволет выбрать определенные индексы. Если индексы не указаны, то выбираются все. Поддерживается маска поиска. Знак (-) используется для исключения базы, а знак процента (%) выполняет роль символа подстановки. Все используемые параметры могут быть разделены занятой (,)

TimeLimit

Установите время, в секундах, после которого команды не выполняются. По умолчанию время не ограничено.

Delay

Установите задержку в секундах между командами индекса. По умолчанию нет задержки.

WaitAtLowPriorityMaxDuration

Время, в минутах, для операции по перестроению индекса online, на блокировку с низким приоритетом.

Параметр WaitAtLowPriorityMaxDuration использует параметр SQL Server WAIT_AT_LOW_PRIORITY и MAX_DURATION команды ALTER INDEX.

WaitAtLowPriorityAbortAfterWait

Действие, которое будет выполняться после операции ожидания блокировки с низким приоритетом на перестроение индекса online

Параметр WaitAtLowPriorityAbortAfterWait использует  WAIT_AT_LOW_PRIORITY и ABORT_AFTER_WAIT команды ALTER INDEX.

AvailabilityGroups

Выбор групп доступности. Поддерживается маска поиска. Знак (-) используется для исключения базы, а знак процента (%) выполняет роль символа подстановки. Все используемые параметры могут быть разделены занятой (,)

LockTimeout

Устанавливет время в секундах, чтобы команда ожидала освобождения блокировки. По умолчанию время не ограничено.

Данная команда использует параметр SET LOCK_TIMEOUT установленный в SQL Server.

LogToTable

Вести лог в таблицу dbo.CommandLog.

Execute

Выполнить команды. По умолчанию команды выполняются нормально. Если этот параметр установлен в N, то команды только выводятся на экран.

Примеры

A. Перестроить или реорганизовать все индексы с фрагментацией во всех пользовательских базах данных

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

B. Перестроить или реорганизовать все индексы с фрагментацией и обновить измененную статистику по всем пользовательским базам данных

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

C. Обновить статистику во всех пользовательских базах

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’

D. Обновление измененной статистики по всем пользовательским базам данных

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

E. Перестроить или реорганизовать все индексы с фрагментацией во всех пользовательских базах данных, выполнить операции сортировки в tempdb и использовать все доступные процессоры

EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ‘Y’,
@MaxDOP = 0

F. Перестроить или реорганизовать все индексы с фрагментацией во всех пользовательских базах данных, используя опцию для поддержки секционированных индексов на уровне раздела

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PartitionLevel = ‘Y’

G. Перестроить или реорганизовать все индексы с фрагментацией во всех пользовательских базах данных с ограничением по времени, чтобы никакие команды не выполнялись после 3600 секунд

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@TimeLimit = 3600

H. Перестроить или реорганизовать все индексы с фрагментацией на таблицу Production.Product в базе данных AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘AdventureWorks’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘AdventureWorks.Production.Product’

I. Перестроить или реорганизовать все индексы с фрагментацией, за исключением индексов в таблице. Production.Product в базе данных AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘ALL_INDEXES, -AdventureWorks.Production.Product’

J. Перестройте или реорганизуйте все индексы с фрагментацией во всех пользовательских базах данных и запишите результаты в таблицу

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = ‘Y’

Выполнение

Выполните хранимые процедуры из заданий SQL Server Agent CmdExec, используя sqlcmd и -b. Передайте параметры хранимым процедурам по имени:

Похожие сообщения:

Выполнение операций над несколькими или всеми базами MS SQL (вариант) Скрипт, меняющей режим восстановления на SIMPLE в множестве баз MSSQL Определяем запрос, вызвавший ожидание на SQL сервере MSSQL и флаги трассировки