Реорганизация индекса sql 1с ошибка

Не работает реорганизация индексов, если есть отключенные.

Модератор: Дмитрий Юхтимовский

---

---

---

---

---

---

---

---

---

---

Вернуться в MS SQL Server для целей 1С:Предприятие

Кто сейчас на форуме

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1

1. Главная
2. Полезная информация
3. Как правильно настроить MS SQL Server для “1С”: планы обслуживания

12.03.2019

Часто неопытные системные администраторы подразумевают перевод базы “1С” в клиент-серверный режим работы как панацею, избавляющую от всех проблем и ускоряющую “1С” во много раз. Однако, без правильной настройки, мониторинга и регулярного обслуживания базы “1С” в клиент-серверном режиме начинают работать еще “медленнее”, чем в файловом. В рамках этой статьи рассмотрим оптимальную настройку своевременных регламентных операций на уровне самой СУБД MS MSQL. Крайне важно выполнять регламентное обслуживание в системах под значительной нагрузкой, где работают больше 10-ти пользователей, ведь в подобных системах обычных действий (выполняемых СУБД MS SQL) становится недостаточно для эффективной работы.

Планы обслуживания/Maintenance Plan в MS SQL Server

Итак, “Сервер 1С:Предприятие” и SQL Server установлены и настроены, базы перенесены, пользователи работают. Ускорение “1С” и комфорт в работе получен. Но, с течением времени документы начинают открываться медленнее, подбор номенклатуры “зависает”, а отчеты формируются “целую вечность”. Чтобы этого избежать, следует настроить и автоматизировать регламентные процедуры по обслуживанию базы в SQL Server.

Вообще, планы обслуживания нужно подстраивать под конкретное оборудование и базы данных. Оставим это на усмотрение профессионалов администрирования баз данных. В общем случае, для базы данных не более 200 Гб в MS SQL Server рекомендуется выполнять следующие регламентные операции:

• Проверка целостности базы данных
• Реорганизация индекса/Восстановить индекс
• Обновление статистики

Рекомендуется регулярно контролировать своевременность и правильность выполнения данных регламентных процедур.

Проверка целостности базы данных/DBCC CHECKDB

Периодичность: 1 раз в неделю.

Время запуска: в технологическом окне – во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio – “Управление” – “Планы обслуживания” – правой кнопкой мыши “Мастер планов обслуживания”.

Имя – можно заполнить на свое усмотрение, например “Проверка целостности базы данных” или “CheckDB”. Для настройки расписания запуска проверки – кнопка “Изменить”. Выполняется – еженедельно; повторять – каждое воскресенье. Однократное задание, например, в 01:00.

При выборе задач по обслуживанию устанавливаем флаг “Проверка целостности базы данных”.

Выбираем необходимые базы данных для обслуживания: либо какую-то определенную, либо несколько, либо все пользовательские.

Если настройка прошла без ошибок, выйдет сообщение об успешной проверке.

Реорганизация индекса/Восстановить индекс

Что такое индексы? Индексы – это структурированные данные, которые ускоряют процесс запроса, предоставляя быстрый доступ к строкам данных в таблице, аналогично предметному указателю или оглавлению в книгах. Индексы составляют больше половины объема большинства баз “1С”. Для каждого индекса обязательно хранится его статистика.

Подробнее об индексах в MS SQL Server

MS SQL Server самостоятельно создает и изменяет индексы при работе с базой. С течением времени данные в индексе становятся фрагментированными, т.е. разбросанными по базе данных. Существенно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления работы базы. Если фрагментация составляет от 5 до 30%, то рекомендуется ее устранить с помощью реорганизации, при фрагментации свыше 30% необходимо полное перестроение индексов.

Подробнее о реорганизации и перестроении индексов

В простейшем случае получить информацию по фрагментации индексов можно с помощью кода:

DECLARE @db_id SMALLINT;

SET @db_id = DB_ID(N'MyBaseSQL');

IF @db_id IS NULL
BEGIN;
    PRINT N'Неправильное имя базы';
END;

ELSE
BEGIN;
	SELECT
		object_id AS [ID объекта],
		index_id AS [ID индекса],
		index_type_desc AS [Тип индекса],
		avg_fragmentation_in_percent AS [Фрагментация в %]
		
	FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , 'LIMITED')
	 
	ORDER BY [avg_fragmentation_in_percent] DESC;
END;
GO

Для выполнения кода нажимаем “Создать запрос” – вставляем код. В строке “SET @db_id = DB_ID(N’MyBaseSQL’);” вместо “MyBaseSQL” нужно указать имя своей базы данных. Кнопка “Выполнить”.

Почему регулярно стоит использовать именно реорганизацию индекса?

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

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

Важно! При использовании модели восстановления “Полная” (правой кнопкой мыши по базе данных – “Свойства” – “Параметры”), чтобы файл журнала транзакций не вырастал до неприличных размеров, необходимо выполнять “Резервное копирование базы данных (полное)” после каждой процедуры реорганизации или перестроения индекса.

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

Периодичность: 1 раз в сутки.

Время запуска: в технологическом окне – во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio – “Управление” – “Планы обслуживания” – правой кнопкой мыши “Мастер планов обслуживания”.

Имя – можно заполнить на свое усмотрение, например “Реорганизация индекса” или “Index Reorganize”. Для настройки расписания запуска проверки – кнопка “Изменить”. Выполняется – ежедневно; повторять – каждый 1 день. Однократное задание, например, в 02:00.

При выборе задач по обслуживанию устанавливаем флаг “Реорганизация индекса”.

Выбираем необходимые базы данных для обслуживания: либо какую-то определенную, либо несколько, либо все пользовательские.

Если настройка прошла без ошибок, выйдет сообщение об спешной проверке.

Обновление статистики

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

Подробнее о статистике в MS SQL Server

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

Частоту обновления статистики нужно определять экспериментальным путем в зависимости от нагрузки, но общая рекомендация для баз “1С” – один раз в сутки.

Периодичность: 1 раз в сутки.

Время запуска: в технологическом окне – во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio – “Управление” – “Планы обслуживания” – правой кнопкой мыши “Мастер планов обслуживания”.

Имя – можно заполнить на свое усмотрение, например “Обновление статистик” или “Update Statistics”. Для настройки расписания запуска проверки – кнопка “Изменить”. Выполняется – ежедневно; повторять – каждый 1 день. Однократное задание, например, в 03:00.

При выборе задач по обслуживанию устанавливаем флаг “Обновление статистик”.

Выбираем необходимые базы данных для обслуживания: либо какую-то определенную, либо несколько, либо все пользовательские.

Если настройка прошла без ошибок, выйдет сообщение об спешной проверке.

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

Фирма “1С” в нескольких источниках (Регламентные операции на уровне СУБД для MS SQL Server) советует после обновления статистики дополнительно проводить очистку процедурного кэша. Это имело смысл для старых версий SQL Server, например версии 2005. С версии 2008 при включенной настройке “асинхронное обновление статистики” очищать процедурный кэш необязательно. При асинхронном обновлении статистики запросы компилируются с существующей статистикой, даже если она устарела. Если на момент компиляции запроса статистика оказывается устаревшей, оптимизатор запросов может выбрать неоптимальный план запроса. Запросы, которые компилируются после выполнения асинхронного обновления, будут усовершенствованы благодаря использованию обновленной статистики.

Контроль выполнения планов обслуживания

Просматривать результаты выполнения обслуживания можно нажав правой кнопкой мыши на “Управление” – “Планы обслуживания” – “Просмотр журнала”.

Если в журнале будут обнаружены ошибки, стоит изучить проблему и принять меры. Планы обслуживания должны отрабатывать успешно.

Почему не стоит использовать сжатие базы данных (шринк/shrink)?

Пожалуй, единственным случаем, когда есть смысл использовать сжатие базы данных – масштабные изменения в базе данных. Например: после восстановления из DT-дампа, свертки или реструктуризации информационной базы.

В остальных случаях:

• сжатие файла базы данных (MDF) приводит к увеличению индексов;
• сжатие файла журнала транзакций (LDF) не нужно при правильной настройке резервного копирования и обслуживании индексов. При использовании полной модели восстановления (Full Recovery Model) базы SQL важно делать регулярные резервные копии файла журнала транзакций и только перестроение индексов. Тогда, файл LDF будет соизмерим с размером файла базы данных и не будет бесконтрольно расти.

Ко всему прочему, практически сразу после выполнения операции “шринк/shrink” файлам приходится вновь расти. Что приводит к дополнительным операциям чтения и записи дисковой системы, которые в свою очередь замедляют работу пользователей.

Что дальше?

В будущих статьях мы рассмотрим несколько тем:

• тонкая настройка SQL Server;
• поиск узких мест в производительности связки сервер “1С:Предприятие”/MS SQL Server;
• настройка резервного копирования и возможность восстановить базу на любой момент времени.

Следите за обновлениями.

1. Главная
2. Полезная информация
3. Как правильно настроить MS SQL Server для «1С»: планы обслуживания

12.03.2019

Часто неопытные системные администраторы подразумевают перевод базы «1С» в клиент-серверный режим работы как панацею, избавляющую от всех проблем и ускоряющую «1С» во много раз. Однако, без правильной настройки, мониторинга и регулярного обслуживания базы «1С» в клиент-серверном режиме начинают работать еще «медленнее», чем в файловом. В рамках этой статьи рассмотрим оптимальную настройку своевременных регламентных операций на уровне самой СУБД MS MSQL. Крайне важно выполнять регламентное обслуживание в системах под значительной нагрузкой, где работают больше 10-ти пользователей, ведь в подобных системах обычных действий (выполняемых СУБД MS SQL) становится недостаточно для эффективной работы.

Планы обслуживания/Maintenance Plan в MS SQL Server

Итак, «Сервер 1С:Предприятие» и SQL Server установлены и настроены, базы перенесены, пользователи работают. Ускорение «1С» и комфорт в работе получен. Но, с течением времени документы начинают открываться медленнее, подбор номенклатуры «зависает», а отчеты формируются «целую вечность». Чтобы этого избежать, следует настроить и автоматизировать регламентные процедуры по обслуживанию базы в SQL Server.

Вообще, планы обслуживания нужно подстраивать под конкретное оборудование и базы данных. Оставим это на усмотрение профессионалов администрирования баз данных. В общем случае, для базы данных не более 200 Гб в MS SQL Server рекомендуется выполнять следующие регламентные операции:

• Проверка целостности базы данных
• Реорганизация индекса/Восстановить индекс
• Обновление статистики

Рекомендуется регулярно контролировать своевременность и правильность выполнения данных регламентных процедур.

Проверка целостности базы данных/DBCC CHECKDB

Периодичность: 1 раз в неделю.

Время запуска: в технологическом окне — во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio — «Управление» — «Планы обслуживания» — правой кнопкой мыши «Мастер планов обслуживания».

Имя — можно заполнить на свое усмотрение, например «Проверка целостности базы данных» или «CheckDB». Для настройки расписания запуска проверки — кнопка «Изменить». Выполняется — еженедельно; повторять — каждое воскресенье. Однократное задание, например, в 01:00.

При выборе задач по обслуживанию устанавливаем флаг «Проверка целостности базы данных».

Выбираем необходимые базы данных для обслуживания: либо какую-то определенную, либо несколько, либо все пользовательские.

Если настройка прошла без ошибок, выйдет сообщение об успешной проверке.

Реорганизация индекса/Восстановить индекс

Что такое индексы? Индексы — это структурированные данные, которые ускоряют процесс запроса, предоставляя быстрый доступ к строкам данных в таблице, аналогично предметному указателю или оглавлению в книгах. Индексы составляют больше половины объема большинства баз «1С». Для каждого индекса обязательно хранится его статистика.

Подробнее об индексах в MS SQL Server

MS SQL Server самостоятельно создает и изменяет индексы при работе с базой. С течением времени данные в индексе становятся фрагментированными, т.е. разбросанными по базе данных. Существенно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления работы базы. Если фрагментация составляет от 5 до 30%, то рекомендуется ее устранить с помощью реорганизации, при фрагментации свыше 30% необходимо полное перестроение индексов.

Подробнее о реорганизации и перестроении индексов

В простейшем случае получить информацию по фрагментации индексов можно с помощью кода:

DECLARE @db_id SMALLINT;

SET @db_id = DB_ID(N'MyBaseSQL');

IF @db_id IS NULL
BEGIN;
    PRINT N'Неправильное имя базы';
END;

ELSE
BEGIN;
	SELECT
		object_id AS [ID объекта],
		index_id AS [ID индекса],
		index_type_desc AS [Тип индекса],
		avg_fragmentation_in_percent AS [Фрагментация в %]
		
	FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , 'LIMITED')
	 
	ORDER BY [avg_fragmentation_in_percent] DESC;
END;
GO

Для выполнения кода нажимаем «Создать запрос» — вставляем код. В строке «SET @db_id = DB_ID(N’MyBaseSQL’);» вместо «MyBaseSQL» нужно указать имя своей базы данных. Кнопка «Выполнить».

Почему регулярно стоит использовать именно реорганизацию индекса?

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

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

Важно! При использовании модели восстановления «Полная» (правой кнопкой мыши по базе данных — «Свойства» — «Параметры»), чтобы файл журнала транзакций не вырастал до неприличных размеров, необходимо выполнять «Резервное копирование базы данных (полное)» после каждой процедуры реорганизации или перестроения индекса.

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

Периодичность: 1 раз в сутки.

Время запуска: в технологическом окне — во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio — «Управление» — «Планы обслуживания» — правой кнопкой мыши «Мастер планов обслуживания».

Имя — можно заполнить на свое усмотрение, например «Реорганизация индекса» или «Index Reorganize». Для настройки расписания запуска проверки — кнопка «Изменить». Выполняется — ежедневно; повторять — каждый 1 день. Однократное задание, например, в 02:00.

При выборе задач по обслуживанию устанавливаем флаг «Реорганизация индекса».

Выбираем необходимые базы данных для обслуживания: либо какую-то определенную, либо несколько, либо все пользовательские.

Если настройка прошла без ошибок, выйдет сообщение об спешной проверке.

Обновление статистики

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

Подробнее о статистике в MS SQL Server

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

Частоту обновления статистики нужно определять экспериментальным путем в зависимости от нагрузки, но общая рекомендация для баз «1С» — один раз в сутки.

Периодичность: 1 раз в сутки.

Время запуска: в технологическом окне — во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio — «Управление» — «Планы обслуживания» — правой кнопкой мыши «Мастер планов обслуживания».

Имя — можно заполнить на свое усмотрение, например «Обновление статистик» или «Update Statistics». Для настройки расписания запуска проверки — кнопка «Изменить». Выполняется — ежедневно; повторять — каждый 1 день. Однократное задание, например, в 03:00.

При выборе задач по обслуживанию устанавливаем флаг «Обновление статистик».

Выбираем необходимые базы данных для обслуживания: либо какую-то определенную, либо несколько, либо все пользовательские.

Если настройка прошла без ошибок, выйдет сообщение об спешной проверке.

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

Фирма «1С» в нескольких источниках (Регламентные операции на уровне СУБД для MS SQL Server) советует после обновления статистики дополнительно проводить очистку процедурного кэша. Это имело смысл для старых версий SQL Server, например версии 2005. С версии 2008 при включенной настройке «асинхронное обновление статистики» очищать процедурный кэш необязательно. При асинхронном обновлении статистики запросы компилируются с существующей статистикой, даже если она устарела. Если на момент компиляции запроса статистика оказывается устаревшей, оптимизатор запросов может выбрать неоптимальный план запроса. Запросы, которые компилируются после выполнения асинхронного обновления, будут усовершенствованы благодаря использованию обновленной статистики.

Контроль выполнения планов обслуживания

Просматривать результаты выполнения обслуживания можно нажав правой кнопкой мыши на «Управление» — «Планы обслуживания» — «Просмотр журнала».

Если в журнале будут обнаружены ошибки, стоит изучить проблему и принять меры. Планы обслуживания должны отрабатывать успешно.

Почему не стоит использовать сжатие базы данных (шринк/shrink)?

Пожалуй, единственным случаем, когда есть смысл использовать сжатие базы данных — масштабные изменения в базе данных. Например: после восстановления из DT-дампа, свертки или реструктуризации информационной базы.

В остальных случаях:

• сжатие файла базы данных (MDF) приводит к увеличению индексов;
• сжатие файла журнала транзакций (LDF) не нужно при правильной настройке резервного копирования и обслуживании индексов. При использовании полной модели восстановления (Full Recovery Model) базы SQL важно делать регулярные резервные копии файла журнала транзакций и только перестроение индексов. Тогда, файл LDF будет соизмерим с размером файла базы данных и не будет бесконтрольно расти.

Ко всему прочему, практически сразу после выполнения операции «шринк/shrink» файлам приходится вновь расти. Что приводит к дополнительным операциям чтения и записи дисковой системы, которые в свою очередь замедляют работу пользователей.

Что дальше?

В будущих статьях мы рассмотрим несколько тем:

• тонкая настройка SQL Server;
• поиск узких мест в производительности связки сервер «1С:Предприятие»/MS SQL Server;
• настройка резервного копирования и возможность восстановить базу на любой момент времени.

Следите за обновлениями.

Добрый день, коллеги.

В сегодняшней статье мы бы хотели рассмотреть достаточно востребованную и популярную тему, как настройка планов обслуживания MS SQL Server. В результате проведения аудитов мы достаточно часто (более чем в 60 % случаев) обнаруживаем некорректности в настройке СУБД MS SQL Server, используемой для работы с продуктами фирмы «1С». Практика показывает, что эта СУБД является наиболее распространенной, поэтому в данной статье рассмотрим основные нюансы работы именно с ней.

Итак, с чего начинается настройка плана обслуживания? Конечно же с бэкапа! Первое правило DBA гласит: «Ничего не начинай делать без бэкапа». Ну и мы не будем. Давайте рассмотрим два основных варианта создания бэкапов, а точнее две модели резервного копирования, или модели восстановления (https://msdn.microsoft.com/ru-ru/library/ms189275(v=sql.120).aspx)

Восстановление по модели simple

Ваша база данных находится в SIMPLE режиме восстановления. Что это означает? Это означает, что бэкапы бывают только полные, журналы транзакций бэкапировать не нужно, производительность в этом смысле максимальная, но восстановиться можно только на точку бэкапа. Восстановление базы «на указанный момент времени» невозможно.

Следовательно, еженочно (или чаще, в зависимости от потребности) мы должны снимать свеженькую копию нашей базы данных и складывать ее в надежное место, и обязательно не в то, в котором лежит наша основная база данных

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

Кроме того, затрону сразу вопрос работы с журналом транзакций. Так как нам журнал тразакций в этом режиме восстановления не очень‑то и помощник, нет необходимости в его резервном копировании — вся информация из журнала уже ушла в бэкап. Обычно журналы в такой модели восстановления почти не растут, но иногда полезно его усекать. Например, после массового изменения данных эта операция может быть крайне полезна с точки зрения места, занимаемого журналом транзакций. Если диск с журналом переполнится — проблемы функционирования базы данных вам обеспечены.

Усечение данных может быть проведено, как стандартным мастером настройки плана обслуживания, так и с помощью несложно скрипта на T‑SQL:

DBCC SHRINKFILE (DatabaseName, 1);

GO

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

Восстановление по модели full

Давайте рассмотрим основные принципы настройки резервного копирования и управления размером журнала лога транзакций с точки зрения самого массового варианта — полной модели восстановления БД.

Полная модель восстановления отличается от простой тем, что в течение всей работы базы данных мы можем (а еще точнее — ДОЛЖНЫ!) делать бэкапы лога транзакций, тем самым обеспечивая возможность восстановления БД между точками основных бэкапов или откаты на конкретные промежутки времени функционирования базы, а также обеспечивая освобождение места в файле журнала (усечение). Если этого не делать, он будет расти постоянно до тех пор, пока однажды не заполнит все доступное ему место (либо на диске, либо до ограничения, заданного в СУБД). Последствия кажутся очевидными, и не самыми приятными.

С точки зрения наличия полных бэкапов — безусловно, минимальная граница — это как правило те же одни сутки. Разностные бэкапы базы данных — это возможность сохранить только изменения, произошедшие с момента последнего бэкапа. Это позволяет достаточно быстро и оперативно проводить резервное копирование базы данных, при этом использовать достаточно быстрое восстановление БД до нужного состояния.

Резервные копии журнала транзакций могут выполняться с нужной вам периодичностью в течение дня, подробнее чем разностное копирование БД. Мы рекомендуем, обычно, выбирать степень подробности копий около ¼ от времени создания разностных копий БД.

Как уже было сказано выше, при выполнении резервной копии журнала транзакций базы данных в полной модели он усечется автоматически (только не путайте усечение со сжатием!).

Пересчет статистики и работа с индексами

Достаточно ошибочной является сложившаяся практика работы с индексами и статистикой у наших клиентов. Очень часто мы сталкиваемся вообще с полным отсутствием этих процедур в планах обслуживания баз данных. Часто они выполняются в неправильном порядке. Часто просто неоптимально (например, одновременно!).

Правильная последовательность действий выглядит так:

1. Определяем степень фрагментированности индекса

1. Если индекс маленький или мало фрагментирован, запускаем процедуру реорганизации индекса и пересчета статистики.

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

2. Пересчитываем всю остальную статистику, где это требуется.
   

Если рассмотреть мини‑скрипт для пересчета статистики и перестроения индексов (не претендуем на супер полноту и универсальность), то выглядеть он будет примерно так (с перебором индексов через курсор):

DECLARE @SQL NVARCHAR(MAX)

DECLARE @MIN_IND_SIZE integer = 128

DECLARE @MIN_FRAGMENTATION_LEVEL integer = 10

DECLARE @CRITICAL_FRAGMENTATION_LEVEL integer = 30

DECLARE currentIndex CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

   SELECT ‘ALTER INDEX [‘ + ind.name + N’] ON [‘ +

SCHEMA_NAME(obj.[schema_id]) + ‘].[‘ + obj.name + ‘] ‘ +

       CASE WHEN stat.avg_fragmentation_in_percent > @CRITICAL_FRAGMENTATION_LEVEL

           THEN ‘REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)’

           ELSE ‘REORGANIZE’

       END + ‘;’

   FROM (

       SELECT stat.[object_id], stat.index_id,

avg_fragmentation_in_percent = MAX(stat.avg_fragmentation_in_percent)

       FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) stat

       WHERE stat.page_count > @MIN_IND_SIZE AND stat.index_id > 0

AND stat.avg_fragmentation_in_percent > @MIN_FRAGMENTATION_LEVEL

       GROUP BY stat.[object_id], stat.index_id

   ) stat

   JOIN sys.indexes ind WITH(NOLOCK) ON stat.[object_id] = ind.[object_id]

AND stat.index_id = ind.index_id

   JOIN sys.objects obj WITH(NOLOCK) ON obj.[object_id] = stat.[object_id]

OPEN currentIndex

FETCH NEXT FROM currentIndex INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

print @sql

   EXEC sys.sp_executesql @SQL

   FETCH NEXT FROM cur INTO @SQL

END

CLOSE currentIndex

DEALLOCATE currentIndex

Обратите внимание на использование tempdb, а также на сохранение индекса доступным во время перестроения — в зависимости от редакции вашей СУБД последняя функция может быть недоступна.

Уведомления

Кроме всех технических аспектов, очень правильно настраивать такие планы обслуживания, которые при некорректном выполнении все‑таки уведомят вас о произошедшей беде. И это будет самый короткий абзац в моей статье.

Если вам все показалось слишком сложным, или вы не уверены в том, что можете сделать подобные настройки самостоятельно — не стесняйтесь, обращайтесь к нам— мы поможем!