SQL-Ex blog

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

• Избыточные операции ввода/вывода, связанные с сжатием.


• Фрагментация индексов (с наибольшей вероятностью для всех ваших индексов).


• Избыточные операции ввода/вывода из-за фрагментации индексов.


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

Рассмотрим это на примере магазина игрушек. Магазин, которым вы управляете, начинает с площади в 100000 квадратных футов. Это идеальное пространство для использования перед праздниками. Теперь представим, что после праздников вам требуется площадь только в 10000 квадратных футов. Поэтому вы перестраиваете свой магазин игрушек (с большими расходами) на меньшую площадь. Затем, когда наступает сезон следующих праздников, вам снова нужно перестраивать свой магазин (обратно на 100000 квадратных футов), чтобы увеличить площадь для закупки игрушек. Видите, насколько дорогой может стать постоянная перестройка магазина. Тогда, если постоянно меняются требования к размеру площади вашего магазина, может быть имеет смысл сделать перестройку один раз, а не делать её регулярно, чтобы подстраиваться под текущие требования.

Базы данных SQL Server очень похожи на пример с магазином игрушек в том, что нет смысла ужимать размер только потому, что пространство не используется сегодня. Опасность может представлять опция автосжатия, которую вы можете включить для вашей базы данных и которая будет регулярно сжимать размер вашей базы данных без вашего участия. Другой опасной альтернативой является наличие DBCC SHRINK DATABASE или SHRINKFILE в задании, выполняемом по расписанию.

Удаление устаревших данных <> сжатию базы данных

Давайте не будем путать удаление устаревших данных с сжатием базы данных. Думайте о размере базы данных как о контейнере, и пространство в этом контейнере занимают данные, индексы и другие структуры.

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

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

Вот почему я предлагаю не сжимать базу данных. Команда сжатия базы данных мало что может вам дать, помимо освобождения места на диске. Размер резервных копий определяется не размером файла данных, а числом используемых 8-килобайтных страниц в этом файле. Удаление данных и перестройка индексов поможет ускорить создание бэкапов, время checkdb и работу статистики. При наличии файлов данных, на 10% - 20% превышающих необходимый на текущий момент размер, является хорошей практикой. Этот избыточный размер будет использован при добавлении новых данных, и использование существующего свободного пространства в файле данных оказывается много быстрей, чем расширение файла данных.

Свободное пространство в файле данных обычно игнорируется в процессе CheckDB, создании резервных копий и в процессе сбора статистики и перестройки индексов, и более всего игнорируется при выполнении обычных запросов.

Что если из моей базы была удалена большая часть данных?

Давайте предположим, что мой файл данных однажды имел размер 1Тб (1000Гб). Я удалил устаревшие данные, и таблицы с индексами теперь занимают около 100Гб, или грубо говоря 10% от размера всего файла данных. Должен ли я теперь сжать базу данных?

Может иметь смысл сжатие базы данных, если справедливо большинство из следующих пунктов:

• Моя база данных не вырастет снова до 1Тб в течение ближайших года или двух.


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


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


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


• Если после сжатия базы данных я собираюсь несколько расширить её (на 10%-20%) в расчете на предполагаемый рост.


• Я понимаю, что производительность упадет во время сжатия базы данных и после сжатия, пока индексы не будут перестроены.

А что с сжатием файлов журнала?

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

Выводы

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

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

И не позволяйте своим друзьям сжимать базы данных.