MSSQL: сравниваем data compression и backup compression

MSSQL поддерживает компрессию бэкапов на лету — легковесную и быструю. Также данные можно упаковать внутри базы с помощью DATA_COMPRESSION = PAGE или ROW. Как мы помним, упакованные данные плохо пакуются. Как упаковка данных повлияет на размер бэкапа?

Тестовая база

Создаем большую табличку test в чистой базе (не забываем перевести ее в SIMPLE):

create table seed (n int) GO set nocount on declare @n int=9999 while @n>=0 begin   insert into seed select @n   set @n=@n-1   end GO create table test (n int identity, a int, b int, str varchar(128)) GO create clustered index PK_n on test (n) GO insert into test (a,b,str)   select L.n,R.n,'seed '+convert(varchar,L.n)+' and '+convert(varchar,R.n)     from seed L, seed R GO create index IXa on test (a) GO create index IXb on test (b) GO create index IXstr on test (str) GO 

Запишем размеры индексов и бэкапов — упакованного и обычного. Затем применим ко всем индексам DATA_COMPRESSION = PAGE

alter index PK_n on test REBUILD with (data_compression=PAGE) alter index IXa on test REBUILD with (data_compression=PAGE) alter index IXb on test REBUILD with (data_compression=PAGE) alter index IXstr on test REBUILD with (data_compression=PAGE)

и снова все запишем.

Наконец, применим DATA_COMPRESSION = ROW

alter index PK_n on test REBUILD with (data_compression=ROW) alter index IXa on test REBUILD with (data_compression=ROW) alter index IXb on test REBUILD with (data_compression=ROW) alter index IXstr on test REBUILD with (data_compression=ROW)

Результаты

Все размеры приведены в Mb

Теперь рассмотрим размер full backups, тоже в мегабайтах:

Выводы

Америку я не открыл, результаты довольно ожидаемые, но хотелось убедиться еще раз

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

• Как и ожидалось, наиболее сильно бэкап уменьшается, если данные внутри базы не сжаты (до 5 раз). В реальности этот коэффициент обычно 3-4

• compression=PAGE очень сильно уменьшает объем данных и увеличивает cache hits ratio. Очевидно, ценой повышенного CPU

• compression=ROW работает хуже и иногда даже увеличивает место, занимаемое данными.

• И все же data compression может ненамного увеличить объем упакованного бэкапа