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

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

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

---

---

---

---

---

---

---

---

---

---

Вернуться в 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;
• настройка резервного копирования и возможность восстановить базу на любой момент времени.

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

Проблемы

Предположим, что в таблице за SQL Server 2017 или 2019 г. имеется индекс г. При запуске сканов с версиями (например, «Изоляция моментального снимка» (SI) или Чтение зафиксированной изоляции моментального снимка (RCSI)), а если в той же таблице также запущен процесс слияния с «многояровой многоязак» или «alter index reorganize», вы получаете неправильные результаты.

Статус

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

Решение

Эта проблема устранена в следующих накопительных обновлениях для SQL Server:

• Накопительный итог обновления 12 SQL Server 2019 г.

• Накопительный итог обновления 25 SQL Server 2017 г.

Ссылки

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