SQL в вопросах и ответах: Дефрагментация и аварии

Файлы журналов ошибок и временных баз данных могут в очень короткое время выйти из-под контроля. Описанные ниже рекомендации помогут решить проблему.

Пол С. Рэндал (Paul S. Randal)

Значение по умолчанию для дефрагментации

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

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

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

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

Так как кластеризованный индекс никогда не фрагментируется, разумно задать коэффициент заполнения равным 100. Этому индексу не потребуется дополнительное пространство. Могут быть другие причины фрагментации кластеризованного индекса (например, по причине обновлений столбцов переменной длины, в результате которых столбцы становятся длиннее), но в принципе это ничего не меняет.

Если коэффициент заполнения всего экземпляра будет отличным от 100%, потребуется задавать коэффициенты заполнения всех кластеризованных индексов, которые не фрагментируются. Если этого не сделать, к ним будет применяться коэффициент заполнения экземпляра, и при перестроении индексов они будут занимать лишнее пространство. Это приводит к неоправданному перерасходованию пространства в базе данных. Можно так делать, но в общем случае это не самый лучший вариант.

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

По волнам перезагрузки

Вопрос Некоторые серверы в нашей среде перезагружаются нечасто. Это хорошо в очень многих отношениях, но это также значит, что журнал ошибок SQL Server может становиться непомерно большим. Он заполняется десяткам тысяч сообщений о завершении резервного копирования, которые нам не нужны. Можно ли сделать журнал ошибок меньше и более управляемым?

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

Есть задокументированный флаг трассировки за номером 3226, который запрещает запись сообщений о завершении резервного копирования. Этот флаг надо внести в список загрузки трассировочных флагов (средствами SQL Server Configuration Manager). Чтобы сделать это без перезагрузки SQL Server, можно воспользоваться командой DBCC TRACEON (3226, -1). Значение «-1» означает, что флаг будет применяться глобально. Члены команды разработчиков SQL Server писали об этом флаге еще в 2007 году

Сконфигурировать управление журналом ошибок можно средствами SSMS (SQL Server Management Studio). Откройте Object Explorer и подключитесь к SQL Server. Разверните узел Management, щелкните правой кнопкой узел SQL Server Logs и выберите Configure. В окне Configure SQL Server Error Logs отметьте параметр Limit the number of error log files before they are recycled (Ограничить количество файлов журнала ошибок перед очисткой). Выберите 99 журналов ошибок вместо заданных по умолчанию шести журналов.

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

Укрощение Tempdb

Вопрос За последние два года наши объемы данных возросли многократно. На каком бы мы диске не расположили нашу базу данных tempdb, она всегда оказывается переполненной. Мы выполняем много сложных запросов, активно используя временные таблицы. Не могли бы вы посоветовать, как нам снизить использование tempdb?

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

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

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

Если временная таблица является наиболее эффективным способом разбиения вашего запроса, есть два приема, которые позволят сократить использование tempdb:

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

Плохие архивы

Вопрос На прошлой неделе вышло из строя наше хранилище SAN. Мы потеряли немного данных из нашей производственной БД. В хранилище SAN размещались самые свежие резервные копии и файлы базы данных — все это было уничтожено. Мы также обнаружили, что чуть более старые архивы также повреждены — иногда по одной и той же причине. Как нам избежать такой ситуации в будущем?

Ответ Это очень распространенная ситуация — нет хороших резервных копий и есть единственная точка отказа, что в конечном итоге ведет к потере данных.

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

• Реализуйте регулярную проверку согласованности производственной базы данных. Это означает, что надо выполнять команду DBCC CHECKDB по отношению к производственной базе данных или ее копии.
• Включите создание контрольных сумм в производственной базе данных (если это еще не реализовано). Во всех операциях резервного копирования используйте параметр WITH CHECKSUM. Это обеспечит проверку контрольных сумм страниц, считываемых для включения в архив, что позволяет предотвратить создание резервной копии с поврежденной базой данных.
• Реализуйте проверку правильности архивов после их создания. Это подразумевает восстановление архива на другом экземпляре SQL Server (опять с параметром WITH CHECKSUM) или как минимум выполнение восстановления в режиме RESTORE VERIFYONLY с параметром WITH CHECKSUM. Задача заключается в восстановлении базы данных и выполнении DBCC CHECKDB. Это также хороший способ избавить производственный сервер от необходимости выполнения проверки согласованности базы данных.
• Организуйте регулярный график тренингов по восстановлению производственной базы данных из имеющихся резервных копий.

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

Вторая проблема заключается в том, что резервные копии хранятся в той же подсистеме ввода/вывода, что и сама база данных. Это означает, что отсутствует защита от отказа подсистемы ввода/вывода.

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

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

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

Пол С. Рэндал (Paul S. Randal) носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу Twitter.com/PaulRandal.