Типичные ошибки настройки планов обслуживания СУБД MS SQL Server для 1С

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

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

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

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

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

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

Типичные ошибки настройки планов обслуживания СУБД MS SQL Server для 1С

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

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

DBCC SHRINKFILE (DatabaseName, 1); GO

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

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

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

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

Типичные ошибки настройки планов обслуживания СУБД MS SQL Server для 1С

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

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

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

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

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

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

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

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

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, а также на сохранение индекса доступным во время перестроения — в зависимости от редакции вашей СУБД последняя функция может быть недоступна.

Уведомления

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

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