SQL в вопросах и ответах: Сжатие, расширение и реструктуризация баз данных

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

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

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

Сжатие осуществляется за счет перемещения страниц файла данных для консолидации свободного пространства в конце файла, которое затем возвращается в файловую систему (что позволяет уменьшить размер файла данных). Чтобы переместить страницу файла данных, SQL Server должен получить монопольную блокировку этой страницы. А это означает, что никаких других блокировок или записи в эту страницу быть не должно. Если в базе данных параллельно выполняются операции, предполагающие получение блокировок, процесс сжатия приостанавливается в ожидании необходимых блокировок. Это увеличивает время сжатия по отношению к ситуации, когда отсутствуют какие-либо другие операции.

Еще одним фактором при сжатии за счет перемещения страницы файла данных, является наличие у какой-либо другой структуры базы данных физических указателей на данные в перемещаемой странице, которые необходимо обновить в соответствии с новым положением страницы. Это не представляет проблемы, если только таблица не является кучей (не имеет кластеризованного индекса) и/или у таблицы один или несколько столбцов с большими объектами (LOB), хранящимися вне строк (отдельно от записей в таблице данных).

Если таблица является кучей, все некластеризованные индексы в куче содержат ссылки на физические записи данных в таблице. При сжатии за счет перемещения страниц данных из таблицы нужно обновлять некластеризованные индексы. SQL Server выполняет операции обслуживания индексов путем обращения к обработчику запросов за обслуживанием некластеризованных индексов по 100 строк за раз.

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

Несмотря на то, что процесс сжатия может быть медленными, начиная с SQL Server 2005 в режиме динамического административного представления sys.dm_exec_requests в столбце percent_complete предоставляется информация о ходе выполнения сжатия. Ход сжатия можно отслеживать по счетчику Shrink Data Movement Bytes/sec объекта Databases в Системном мониторе.

Включение автоматического увеличения размера файлов

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

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

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

Вся сложность заключается в определении «правильной» настройки автоматического увеличения. В SQL Server 2005 и более поздних версиях по умолчанию устанавливаются автоматическое увеличение файлов журнала транзакций на 10%, а файлов данных – на 1Мб. Однако определение автоматического увеличения в относительных цифрах означает, что при увеличении файлов растет и уровень автоматического увеличения. Это также означает, что необходимое время также может увеличиться, если не разрешена мгновенная инициализация файла. Таким образом оба типа файлов должны иметь абсолютное значение автоматического увеличения, чтобы поведение этой функции было предсказуемо.

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

Для файлов данных 1 Мб автоматического увеличения до смешного мало, но правильное значение определить трудно. Все зависит от того, что требуется от функции автоматического увеличения – чрезвычайные временные меры или замена ручного управления размером файла данных. Решение также зависит от того, сколько нового пространства требуется ежедневно для добавляемых в базу данных. Сухой остаток таков: надо включить автоматическое увеличение размера файлов и задать ему разумное значение в абсолютных числах, не прибегая к процентам.

Схема хранения

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

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

Во-первых, всегда ли данные будут меньше 8000 байт? Если да, то попробуйте использовать тип данных (n)char или (n)varchar, но только не один из настоящих типов данных LOB таких, как XML, (n)varchar(max), varbinary(max), (n)text или image, если только это не абсолютно необходимо. Если настоящий тип LOB нужен из-за объема данных, не используйте (n)text или image, так как в SQL Server 2005 эти типы данных объявлены устаревшими. Они не настолько функциональны, как более новые типы данных LOB.

Во-вторых, если требуется истинный тип LOB, следует серьезно подумать, где хранить данные – в строках (в той же записи таблицы данных, что и другие столбцы таблицы) или вне строк (хранятся в отдельных страницах файла данных со ссылкой на них в таблицах данных). При частом использовании данных LOB лучше хранить их в строках, так как это позволит запросам извлекать информацию более эффективно. Если это не так, лучше хранить их вне строк. Нерегулярные запросы данных LOB потребуют больше ресурсов, но записи данных будут меньше, что обеспечивает более плотное хранение данных и общее улучшение производительности запросов. Имейте в виду, что в строках данные LOB можно хранить только размером до 8000 байт или до размера, возможного при наличии других столбцов записи данных, иначе они автоматически размещаются вне строк.

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

Есть также нюансы с данными фиксированной и переменной длины, иногда даже требуется быстрый потоковый доступ к данным. В этом случае следует подумать об использовании существующего в SQL Server 2008 типа данных FILESTREAM. Более глубокий анализ всех типов хранения данных LOB вы найдете в записи «Importance of choosing the right LOB storage technique».

Критические проверки и компромиссы

Вопрос: Я перерабатываю процедуры обслуживания баз данных в нашей компании и собираюсь начать применять проверки DBCC в особо важных базах данных. Как часто я должен запускать проверку в каждой базе данных?

Ответ: Упреждающие проверки целостности являются важной частью любого комплексного плана обслуживания пользовательских и системных баз данных. Также важно использовать метод проверки страниц. В базах данных SQL Server 2005 и более поздних версий включите проверку контрольных сумм страницы. С базах данных SQL Server 2000 используйте обнаружение разорванных страниц.

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

Вот всего лишь несколько из них.

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

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

Суть в том, что все зависит от вас и вашего ощущения комфортности. В августе 2009 года я провел опрос в своем блоге: 37% из 276 респондентов выполняют проверки целостности еженедельно, а 25% выполняют их ежедневно. Полные результаты моего опроса и много другой информации, полезной для определения частоты проверок, вы найдете в моем блоге www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx.

Материалы по теме

• Corruption Recovery Tips, Database Shrinkage Advice and More
• Unexpected Consistency Checks, Troubleshooting Memory Usage and More
• Removing Index Fragmentation, Synchronizing vs. Synchronized, and More