Как сжать/снизить размеры базы данных в MS SQL?
При использовании MS SQL появляется проблема, когда размеры расположенных баз данных на физическом носителе увеличиваются до огромных объемов.
Одно из решений — это покупка нового жесткого диска с большим объемом памяти. Но тот же самый MS SQL Server предлагает более экономичное решение (бесплатное) — свои собственные функции (как сжатие). Ниже представлены четыре основных метода по решению данной проблемы.
Метод 1: Использование SQL Server Management Studio
Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)
Шаг 2: Нажимаем на «ОК»
Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).
Метод 2: Использование Transact SQL Command
Метод 1: Использование SQL Server Management Studio
Шаг 1: Открываем наш SQL Server Management Studio
Шаг 2: Подключаемся к необходимой Базе данных
Шаг 3: Нажимаем на «Создать запрос» (New Query)
Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)
Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.
Метод 3: Сжатие на уровне строк
Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:
Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,
Не применяет сохранение данных, если значения являются 0 и NULL
Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.
Осуществим сжатие по строкам.
Метод 4: Сжатие на уровне страниц
Алгоритмы действия данного сжатия заключается в том, что система проходит по всей таблице. Если видит повторяющиеся значения, то вместо копирования этих данных, система создает ссылки на них. Аналогично осуществляется с общими префиксами.
Данное сжатие позволяет максимизировать кол-во строк, которые хранятся на странице,
Повторы данных заменяются ссылками, если происходит сжатие по префиксу.
Пример: используем ту же самую таблицу на 14 500 строк.
Осуществим сжатие по страницам.
Результат: занимаемое пространство данными уменьшилось до 2МВ.
Различия между сжатием на уровне страниц и строк
Если кратко резюмировать выше описанные способы, то главное различие между 3 и 4 способом – это данные которые используются в самой базе данных.
Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.
Негативные факторы при использовании сжатия:
Частое сжатие Базы Данных не рекомендуется, т.к. сжатие приводит к фрагментации таблиц.
Размер базы данных никаким образом нельзя сделать меньше, чем минимальный размер этой БД. Пример: если базу данных создали с размером 5 МВ и она увеличилась до 50 МВ, то ее можно сжать только до изначального созданного размера в 5МВ (даже с пустыми столбцами и строками).
Чтобы достичь наибольшего эффекта от сжатия, ее нужно применять после операций, которые после своего применения создают большое количество неиспользуемого пространства в БД (удаление таблиц).
- Происходит увеличение загрузки процессора.
Вывод
Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).