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) данных может иметь большущее воздействие на производительность запросов, потому очень принципиально избрать верный подход. Детализированный анализ всех вариантов выходит за рамки этой колонки, но вот несколько советов:

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

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

В-3-х, если таблица содержит столбец 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 используйте обнаружение разорванных страничек.

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

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

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

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

Сущность в том, что все находится в зависимости от вас и вашего чувства комфортности. В августе 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

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.