IndexOptimize -хранимая процедура оптимизации структуры (реиндексация, пересчет статистики и реорганизация)

Оглавление

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

Параметры

Databases

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

Переменная Описание
SYSTEM_DATABASES Все системные базы (master, msdb, and model)
USER_DATABASES Все пользовательские базы
ALL_DATABASES Все базы
AVAILABILITY_GROUP_DATABASES Все базы и группы доступности
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES Все пользовательские базы и исключая группы доступности
Db1 База данных Db1
Db1, Db2 Базы данных Db1 и Db2
USER_DATABASES, -Db1 Все пользовательские базы, за исключением Db1
%Db% Все базы, имеющие в названии Db
%Db%, -Db1 Все базы имеющие в названии Db, за исключением базы Db1
ALL_DATABASES, -%Db% Все базы, за исключением тех, которые имеют в названии Db

FragmentationLow

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

Переменная Описание
INDEX_REBUILD_ONLINE Перестроение (rebuild) индекса online
INDEX_REBUILD_OFFLINE Перестроение (rebuild) индекса offline
INDEX_REORGANIZE Реорганизация индекса
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE Перестроение индекса онлайн. Перестроить индекс в автономном режиме, если онлайн-перестройка не поддерживается индексом
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE Перестройте индекс онлайн. Реорганизовать индекс, если онлайн-перестройка не поддерживается индексом
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,

INDEX_REBUILD_OFFLINE

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

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

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

FragmentationMedium

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

Переменная Описание
INDEX_REBUILD_ONLINE Перестроение (rebuild) индекса online
INDEX_REBUILD_OFFLINE Перестроение (rebuild) индекса offline
INDEX_REORGANIZE Реорганизация индекса
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE Перестроение индекса онлайн. Перестроить индекс в автономном режиме, если онлайн-перестройка не поддерживается индексом
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE Перестройте индекс онлайн. Реорганизовать индекс, если онлайн-перестройка не поддерживается индексом
INDEX_REORGANIZE,

INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE

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

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

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

FragmentationHigh

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

Переменная Описание
INDEX_REBUILD_ONLINE Перестроение (rebuild) индекса online
INDEX_REBUILD_OFFLINE Перестроение (rebuild) индекса offline
INDEX_REORGANIZE Реорганизация индекса
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE Перестроение индекса онлайн. Перестроить индекс в автономном режиме, если онлайн-перестройка не поддерживается индексом
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE Перестройте индекс онлайн. Реорганизовать индекс, если онлайн-перестройка не поддерживается индексом
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,

INDEX_REBUILD_OFFLINE

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

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

Процедура 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 для операций сортировки при восстановлении индексов.

Переменная Описание
Y Использовать
N Не использовать. По умолчанию

Переменная 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

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

Переменная Описание
Y Применять
N Не применять. По умолчанию

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

LOBCompaction

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

Переменная Описание
Y Сжимать. По умолчанию
N Не сжимать

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

UpdateStatistics

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

Переменная Описание
ALL Обновлять статистику индексов и таблиц
INDEX Обновление статистики индексов
COLUMNS Обновление статистики таблиц
NULL Не обновлять статистику. По умолчанию

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

OnlyModifiedStatistics

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

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

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

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

Переменная Описание
Y Обновление статистики с помощью последней выборки.
N Пусть SQL Server автоматически вычислит требуемый образец. По умолчанию

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

PartitionLevel

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

Переменная Описание
Y Ведение секционированных индексов на уровне раздела. По умолчанию
N Ведение секционированных индексов на уровне индекса

MSShippedObjects

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

Переменная Описание
Y Ведение индексов и статистики по объектам, созданным внутренними компонентами SQL Server
N Не поддерживать индексы и статистику по объектам, созданным внутренними компонентами SQL Server. Это значение по умолчанию.

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

Indexes

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

Переменная Описание
ALL_INDEXES Все индексы
Db1.Schema1.Tbl1.Idx1 Индекс Idx1 на объекте Schema1.Tbl1 в базе данных Db1
Db1.Schema1.Tbl1.Idx1, Db2.Schema2.Tbl2.Idx2 Индекс Idx1 на объекте Schema1.Tbl1 в базе данных Db1 и индекс Idx2 на объекте Schema2.Tbl2 в базе данных Db2
Db1.Schema1.Tbl1 Все индексы объекта Schema1.Tbl1 в базе данных Db1
Db1.Schema1.Tbl1, Db2.Schema2.Tbl2 Все индексы на объекте Schema1.Tbl1 в базе данных Db1 и все индексы объекта Schema2.Tbl2 в базе данных Db2
Db1.Schema1.% Все индексы в схеме Schema1 в базе данных Db1
%.Schema1.% Все индексы в схеме Schema1 во всех базах данных
ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1 Все индексы, кроме индекса Idx1, на объекте Schema1.Tbl1 в базе данных Db1
ALL_INDEXES, -Db1.Schema1.Tbl1 Все индексы, кроме индексов объекта Schema1.Tbl1 в базе данных Db1

TimeLimit

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

Delay

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

WaitAtLowPriorityMaxDuration

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

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

WaitAtLowPriorityAbortAfterWait

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

Переменная Описание
NONE Продолжать ждать блокировок с нормальным приоритетом
SELF Прервать операцию по перестроению индекса online
BLOCKERS Убитье транзакции пользователя, которые блокируют операцию перестроение индекса online

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

AvailabilityGroups

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

Переменная Описание
ALL_AVAILABILITY_GROUPS Все группы доступности
AG1 Группа доступности AG1
AG1, AG2 Группы доступности AG1 и AG1
ALL_AVAILABILITY_GROUPS, -AG1 Все группы доступности, кроме AG1
%AG% Все группы доступности, содержащие в своем имени “AG”
%AG%, -AG1 Все группы доступности содержащие в своем имени “AG”, кроме AG1
ALL_AVAILABILITY_GROUPS, -%AG% Все группы доступности, за исключением групп, содержащих в имени AG

LockTimeout

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

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

LogToTable

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

Переменная Описание
Y Лог команд сохранять в dbo.CommandLog
N Не сохранять лог. По умолчанию

Execute

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

Переменная Описание
Y Выполнять команды. По умолчанию
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. Передайте параметры хранимым процедурам по имени:

0 0 голоса
Рейтинг статьи
Звёзд: 1Звёзд: 2Звёзд: 3Звёзд: 4Звёзд: 5 (Пока оценок нет)
Загрузка...
Подписаться
Уведомить о
guest
0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии