Типичные ошибки настройки планов обслуживания СУБД 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 режиме восстановления. Что это означает? Это означает, что бэкапы бывают только полные, журналы транзакций бэкапировать не нужно, производительность в этом смысле максимальная, но восстановиться можно только на точку бэкапа. Восстановление базы «на указанный момент времени» невозможно.

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

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

DBCC SHRINKFILE (DatabaseName, 1); GO

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

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

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

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

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

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

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

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

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

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

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

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

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

Уведомления

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

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